Re: Please Help

From: Sue Hoegemeier (Sue_H@nomail.please)
Date: 01/14/03 

From: Sue Hoegemeier <Sue_H@nomail.please>
Date: Mon, 13 Jan 2003 20:55:17 -0700

I think if you use USE in a stored procedure, you need to
execute the entire statement dynamically with an EXEC - e.g.
EXEC('USE <SomeDatabase> EXEC sp_grantdbaccess...')
But....If I create a stored procedure in Northwinds that
executes:
exec pubs.dbo.sp_grantdbaccess @SomeUser
it works fine and creates the user in pubs when executed
from Northwinds. Could you post the specific error you get
when you try the same thing?

-Sue

On Mon, 13 Jan 2003 16:07:25 -0600, "David Lucus"
<davidl@studentlife.tamu.edu> wrote:

>It is now giving me another error. It is telling me that I cannot use the
>"USE" command in a procedure or trigger. This is in a stored procedure. if
>you have any other ideas I would be much appreciative.
>
>thx,
>David
>
>"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
>news:OyzSx10uCHA.1848@TK2MSFTNGP09...
>> Try issuing a USE command before executing, and not specifying the
>database
>> name in the EXEC call, like so:
>>
>> USE MASTER
>> EXEC sp_addlogin @newname
>>
>> USE DBName
>> EXEC sp_grantdbaccess @newname
>> EXEC sp_addrolemember @sturolename, @newname
>>
>> --
>> Aaron Bertrand, SQL Server MVP
>> http://www.aspfaq.com/
>>
>> Please reply in the newsgroups, but if you absolutely
>> must reply via e-mail, please take out the TRASH.
>>
>>
>> "David Lucus" <davidl@studentlife.tamu.edu> wrote in message
>> news:etA5uu0uCHA.2512@TK2MSFTNGP11...
>> > I have a stored procedure that adds new sql server accounts from the
>web.
>> > there are 2 databases that need teh login. The database where the
>stored
>> > procedure resides has no problems granting roles and db access
>> permissions,
>> > however when i try to give it permissions to the other database nothing
>is
>> > happening. It is not adding a user to the roles or throwing an error.
>> here
>> > is the code i am using to add the user to the second db
>> >
>> > EXECUTE DBName.dbo.sp_grantdbaccess @newname
>> >
>> > EXECUTE DBName.dbo.sp_addrolemember @sturolename, @newname
>> >
>> > where @newname is the variable with the name, and @sturolename is the
>role
>> > name.
>> >
>> > if anyone has any ideas please help.
>> >
>> > thx,
>> > David
>> >
>> >
>>
>>
>

Relevant Pages

  • Re: regex to find an stored proc name
    ... exec this a, b, c ... execute ... The one I gave you will do this, and will put the Stored Procedure names ... Hard work is a medication for which ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: EXECUTE tsqlstring with parameters
    ... I've tried using exec by itself, exec sp_executesql with an embedded exec ... string and sp_executesql with parameter substitution. ... until I need to execute a stored procedure that uses an OUTPUT parameter. ...
    (microsoft.public.sqlserver)
  • Re: Creating a view that uses a sp to retrive data
    ... Use Northwind ... And I still should qualify the database name. ... > db other than the stored procedure. ... exec tempdb..usp')x ...
    (microsoft.public.sqlserver.programming)
  • Re: Looping through databases in stored proc
    ... Yuu could try declaring and executing a string within your stored procedure ... select @str AS TheStringToExecute ... > dynamically run sp_helpfile against each database on the server. ... > exec sp_helpfile ...
    (microsoft.public.sqlserver.programming)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... EXEC sp_fulltext_catalog 'adsfull', 'stop' ... > 1) check noise words inside stored procedure ... > can be solved by changing the language specific file with noise words ... ...
    (microsoft.public.sqlserver.fulltext)