Using "USE" in Sproc

[JoseC]

Components:
VB6
SQL7.0

I am trying to add new users to for my application, the SPROC that I calling contains:

------------------------------------------------------
use master
exec sp_addlogin
@login,
@password,
MyAppDB

use MyAppDB
exec sp_grantdbaccess
@login,
@login

exec sp_addrolemember
[MyAppDB users],
@login
------------------------------------------------------

I get the following error:
Error 154: a USE database statement is not allowed in a procedure or trigger.

So, how can I add my new users?

 

[tlbroadbent]

One of the little known behaviors of system stored procedures is that they can be executed within the context of any database though they are stored in master. Simply add the database to the SP name. In addition, you don't need to Use master to execute sp_addlogin.

exec sp_addlogin
@login,
@password,
MyAppDB

--Note: MyAppDB.dbo.sp_grantdbaccess is also valid
exec MyAppDB..sp_grantdbaccess
@login,
@login Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[JoseC]

Thank you so much!

 

[JoseC]

tlbroadbent,
I tried the following and although it is not coming back with an error, it's not creating the user.

------------------------------------------------------------
CREATE PROCEDURE user_create (@username as varchar(40)) AS

declare @password varchar(25)

set @password = 'password' -- ENTER NEW USER PASSWORD HERE --

If @username <> Null
If not exists (SELECT [name] FROM syslogins WHERE [name] = @username)
BEGIN
exec sp_addlogin
@username,
@password,
MyAppDB

exec MyAppDB.dbo.sp_grantdbaccess
@username,
@username

exec MyAppDB.dbo.sp_addrolemember
[mous users],
@username
END
------------------------------------------------------------

What am I doing wrong?
Thanks!

 

[tlbroadbent]

Use &quot;IS NOT NULL&quot; in the If statement rather than &quot;<> NULL.&quot; Add master.dbo. to the syslogins table reference.

CREATE PROCEDURE user_create
(@username as varchar(40)) AS

Declare @password varchar(25)

-- ENTER NEW USER PASSWORD HERE --
set @password = 'password'

If @username Is Not Null
Begin
If not exists
(SELECT [name]
FROM master.dbo.syslogins
WHERE [name] = @username)
BEGIN

exec sp_addlogin
@username,
@password,
UtilityDB

exec UtilityDB.dbo.sp_grantdbaccess
@username,
@username

exec UtilityDB.dbo.sp_addrolemember
db_datareader,
@username
END
END
go Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[JoseC]

tlbroadbent,
Doh!! Okay, I made the changes, tried it, and it works!

Thanks again.