|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DSN-Less ConnectionsTools: SQL SERVER 2000
Visual Basic 6.0 I made a DSN-less conection to the server above. It doesn't use Windows NT Trusted connection, it uses the Server authentication and own user. I have granted full control on all the objects. The situation is the following: - It's possible to use SELECT statements - It can't execute store procedures. Where could the problem be? Thanks. -- Rick Did you have a DSN connection based on the same security type that worked?
You have verified you have permission on the SP's you want to execute? -- Show quoteHide quoteChris Hanscom - Microsoft MVP (VB) Veign's Resource Center http://www.veign.com/vrc_main.asp Veign's Blog http://www.veign.com/blog -- "Rick" <R***@discussions.microsoft.com> wrote in message news:C72D279A-C53F-4182-8603-F5214476D64D@microsoft.com... > > Tools: SQL SERVER 2000 > Visual Basic 6.0 > > I made a DSN-less conection to the server above. It doesn't use Windows NT > Trusted connection, it uses the Server authentication and own user. I have > granted full control on all the objects. The situation is the following: > > - It's possible to use SELECT statements > - It can't execute store procedures. > > Where could the problem be? > > Thanks. > > > -- > Rick The user is a datareader/datawriter and it's been given permission on ther
store procedure. The problem persists. -- Show quoteHide quoteRick "Veign" wrote: > Did you have a DSN connection based on the same security type that worked? > You have verified you have permission on the SP's you want to execute? > > -- > Chris Hanscom - Microsoft MVP (VB) > Veign's Resource Center > http://www.veign.com/vrc_main.asp > Veign's Blog > http://www.veign.com/blog > -- > > > "Rick" <R***@discussions.microsoft.com> wrote in message > news:C72D279A-C53F-4182-8603-F5214476D64D@microsoft.com... > > > > Tools: SQL SERVER 2000 > > Visual Basic 6.0 > > > > I made a DSN-less conection to the server above. It doesn't use Windows NT > > Trusted connection, it uses the Server authentication and own user. I have > > granted full control on all the objects. The situation is the following: > > > > - It's possible to use SELECT statements > > - It can't execute store procedures. > > > > Where could the problem be? > > > > Thanks. > > > > > > -- > > Rick > > > Datareader / Datawriter. Is this a .Net thing?
If so: Almost everybody in this newsgroup is using VB6 or lower. While you may get a stray answer to VB.NET / VB2003 / VB2005 questions here, you should ask them in newsgroups devoted exclusively to .NET programming. Look for newsgroups with either the word "dotnet" or "vsnet" in their name. For the microsoft news server, try these newsgroups... microsoft.public.dotnet.general microsoft.public.dotnet.languages.vb microsoft.public.vsnet.general -- Show quoteHide quoteChris Hanscom - Microsoft MVP (VB) Veign's Resource Center http://www.veign.com/vrc_main.asp Veign's Blog http://www.veign.com/blog -- "Rick" <R***@discussions.microsoft.com> wrote in message news:ECDCAE6B-76E8-4D7D-B1FE-FEC8F6097F21@microsoft.com... > The user is a datareader/datawriter and it's been given permission on ther > store procedure. The problem persists. > > > -- > Rick > > > "Veign" wrote: > > > Did you have a DSN connection based on the same security type that worked? > > You have verified you have permission on the SP's you want to execute? > > > > -- > > Chris Hanscom - Microsoft MVP (VB) > > Veign's Resource Center > > http://www.veign.com/vrc_main.asp > > Veign's Blog > > http://www.veign.com/blog > > -- > > > > > > "Rick" <R***@discussions.microsoft.com> wrote in message > > news:C72D279A-C53F-4182-8603-F5214476D64D@microsoft.com... > > > > > > Tools: SQL SERVER 2000 > > > Visual Basic 6.0 > > > > > > I made a DSN-less conection to the server above. It doesn't use Windows NT > > > Trusted connection, it uses the Server authentication and own user. I have > > > granted full control on all the objects. The situation is the following: > > > > > > - It's possible to use SELECT statements > > > - It can't execute store procedures. > > > > > > Where could the problem be? > > > > > > Thanks. > > > > > > > > > -- > > > Rick > > > > > > This a SQL Server permission.
-- Show quoteHide quoteRick "Veign" wrote: > Datareader / Datawriter. Is this a .Net thing? > > If so: > > Almost everybody in this newsgroup is using VB6 or lower. While you may get > a stray answer to VB.NET / VB2003 / VB2005 questions here, you should ask > them in newsgroups > devoted exclusively to .NET programming. Look for newsgroups with either the > word "dotnet" or "vsnet" in their name. > > For the microsoft news server, try these newsgroups... > > microsoft.public.dotnet.general > microsoft.public.dotnet.languages.vb > microsoft.public.vsnet.general > > -- > Chris Hanscom - Microsoft MVP (VB) > Veign's Resource Center > http://www.veign.com/vrc_main.asp > Veign's Blog > http://www.veign.com/blog > -- > > > "Rick" <R***@discussions.microsoft.com> wrote in message > news:ECDCAE6B-76E8-4D7D-B1FE-FEC8F6097F21@microsoft.com... > > The user is a datareader/datawriter and it's been given permission on ther > > store procedure. The problem persists. > > > > > > -- > > Rick > > > > > > "Veign" wrote: > > > > > Did you have a DSN connection based on the same security type that > worked? > > > You have verified you have permission on the SP's you want to execute? > > > > > > -- > > > Chris Hanscom - Microsoft MVP (VB) > > > Veign's Resource Center > > > http://www.veign.com/vrc_main.asp > > > Veign's Blog > > > http://www.veign.com/blog > > > -- > > > > > > > > > "Rick" <R***@discussions.microsoft.com> wrote in message > > > news:C72D279A-C53F-4182-8603-F5214476D64D@microsoft.com... > > > > > > > > Tools: SQL SERVER 2000 > > > > Visual Basic 6.0 > > > > > > > > I made a DSN-less conection to the server above. It doesn't use > Windows NT > > > > Trusted connection, it uses the Server authentication and own user. I > have > > > > granted full control on all the objects. The situation is the > following: > > > > > > > > - It's possible to use SELECT statements > > > > - It can't execute store procedures. > > > > > > > > Where could the problem be? > > > > > > > > Thanks. > > > > > > > > > > > > -- > > > > Rick > > > > > > > > > > > > Actually I can execute the store procedure on the Query Analyzer, with the
same connection string. But it doesn't execute for recordsets nor ADO Controls in VB6. The system works fine as for Queries are concerned, but not for this S.P. The S.P. creates temp table and inserts data into it. the output is a direct query of that temp tabla. -- Show quoteHide quoteRick "Veign" wrote: > Did you have a DSN connection based on the same security type that worked? > You have verified you have permission on the SP's you want to execute? > > -- > Chris Hanscom - Microsoft MVP (VB) > Veign's Resource Center > http://www.veign.com/vrc_main.asp > Veign's Blog > http://www.veign.com/blog > -- > > > "Rick" <R***@discussions.microsoft.com> wrote in message > news:C72D279A-C53F-4182-8603-F5214476D64D@microsoft.com... > > > > Tools: SQL SERVER 2000 > > Visual Basic 6.0 > > > > I made a DSN-less conection to the server above. It doesn't use Windows NT > > Trusted connection, it uses the Server authentication and own user. I have > > granted full control on all the objects. The situation is the following: > > > > - It's possible to use SELECT statements > > - It can't execute store procedures. > > > > Where could the problem be? > > > > Thanks. > > > > > > -- > > Rick > > >
Show quote
Hide quote
"Rick" <R***@discussions.microsoft.com> wrote in message Minimize the SP to the lowest elements that still demostrates the problem,news:6A8727C8-1855-41B3-9120-8E848D679380@microsoft.com... > Actually I can execute the store procedure on the Query Analyzer, with the > same connection string. > > But it doesn't execute for recordsets nor ADO Controls in VB6. > > The system works fine as for Queries are concerned, but not for this S.P. > > The S.P. creates temp table and inserts data into it. the output is a direct > query of that temp tabla. > > > -- > Rick > > > "Veign" wrote: > > > Did you have a DSN connection based on the same security type that worked? > > You have verified you have permission on the SP's you want to execute? > > > > -- > > Chris Hanscom - Microsoft MVP (VB) > > Veign's Resource Center > > http://www.veign.com/vrc_main.asp > > Veign's Blog > > http://www.veign.com/blog > > -- > > > > > > "Rick" <R***@discussions.microsoft.com> wrote in message > > news:C72D279A-C53F-4182-8603-F5214476D64D@microsoft.com... > > > > > > Tools: SQL SERVER 2000 > > > Visual Basic 6.0 > > > > > > I made a DSN-less conection to the server above. It doesn't use Windows NT > > > Trusted connection, it uses the Server authentication and own user. I have > > > granted full control on all the objects. The situation is the following: > > > > > > - It's possible to use SELECT statements > > > - It can't execute store procedures. > > > > > > Where could the problem be? > > > > > > Thanks. > > > > > > > > > -- > > > Rick > > and then post it and the VB6/ADO code using it. -ralph The store procedure does this:
-it creates a temp table(#Table). -it inserts data into the temp table -it returns a SELECT from the temp table The funniest thing is that if I use a Trusted Connection with a DSN it works. -I've given all the permissions the the user to handle data. -The store procedure is used with an ADO Control and a datagrid. -- Show quoteHide quoteRick "Ralph" wrote: > > "Rick" <R***@discussions.microsoft.com> wrote in message > news:6A8727C8-1855-41B3-9120-8E848D679380@microsoft.com... > > Actually I can execute the store procedure on the Query Analyzer, with the > > same connection string. > > > > But it doesn't execute for recordsets nor ADO Controls in VB6. > > > > The system works fine as for Queries are concerned, but not for this S.P. > > > > The S.P. creates temp table and inserts data into it. the output is a > direct > > query of that temp tabla. > > > > > > -- > > Rick > > > > > > "Veign" wrote: > > > > > Did you have a DSN connection based on the same security type that > worked? > > > You have verified you have permission on the SP's you want to execute? > > > > > > -- > > > Chris Hanscom - Microsoft MVP (VB) > > > Veign's Resource Center > > > http://www.veign.com/vrc_main.asp > > > Veign's Blog > > > http://www.veign.com/blog > > > -- > > > > > > > > > "Rick" <R***@discussions.microsoft.com> wrote in message > > > news:C72D279A-C53F-4182-8603-F5214476D64D@microsoft.com... > > > > > > > > Tools: SQL SERVER 2000 > > > > Visual Basic 6.0 > > > > > > > > I made a DSN-less conection to the server above. It doesn't use > Windows NT > > > > Trusted connection, it uses the Server authentication and own user. I > have > > > > granted full control on all the objects. The situation is the > following: > > > > > > > > - It's possible to use SELECT statements > > > > - It can't execute store procedures. > > > > > > > > Where could the problem be? > > > > > > > > Thanks. > > > > > > > > > > > > -- > > > > Rick > > > > > Minimize the SP to the lowest elements that still demostrates the problem, > and then post it and the VB6/ADO code using it. > > -ralph > > >
Show quote
Hide quote
"Rick" <R***@discussions.microsoft.com> wrote in message Are you sure the SQL Server login has Execute permission for the storednews:6A1FF9D4-58EB-47CA-AB0F-03FD3059EE42@microsoft.com... > > The store procedure does this: > > -it creates a temp table(#Table). > -it inserts data into the temp table > -it returns a SELECT from the temp table > > > The funniest thing is that if I use a Trusted Connection with a DSN it > works. > -I've given all the permissions the the user to handle data. > > > -The store procedure is used with an ADO Control and a datagrid. > proc? Normally, if using stored procs (as opposed to writing inline SQL SELECT, UPDATE, DELETE, etc. statements in your VB app), you don't give any kind of permissions at all to tables or views. You merely need to give the login name Execute permission for the stored proc. Normally, I do this in the stored proc itself using a GRANT statement. For example: GRANT EXECUTE ON [dbo].[STORED_PROC_NAME] TO [ROLE_NAME] GO It might help if you post the actual SQL code of your stored proc rather then simply stating the 3 things it does. Previously, you mentioned the login was given the db_datareader and db_datawriter fixed database roles. To my knowledge (maybe I'm wrong about this), those roles do not include execute permission for stored procs. Regardless, it appears to be a permissions issue. Compare the permissions for the trusted connection login against the SQL Server Authentication login. I'd be willing to bet you'll find differences. Finally, do you have a DBA that you can consult with? This seems to me to be more an issue for a DBA than a programmer. With that in mind, you might want to post to a SQL Server newsgroup as it doesn't appear to really have anything to do with VB. -- Mike Microsoft MVP Visual Basic 
Other interesting topics
|
|||||||||||||||||||||||
