Can't get sp_add_login, sp_add_user to work

Question

Can't get sp_add_login, sp_add_user to work

Charlottecb

Hall of Fame

Points: 3932
More actions
October 24, 2003 at 2:27 am

#59027

Can anyone tell me why the following won't compile under Enterprise manager. (it says I must declare @strlogin.
TIA
CREATE PROCEDURE [dbo].[prc995CreateUserLogin]
(@strLogin nvarchar(30),
@strPwd nvarchar(30))
AS
EXEC sp_addlogin @strLogin, @strPwd, 'MyDatabase', 'english'
GO
EXEC sp_adduser @strLogin, 'myRole'
GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Changing to



CREATE PROCEDURE [dbo].[prc995CreateUserLogin] 

(@strLogin nvarchar(30),

@strPwd nvarchar(30))

AS



EXEC sp_addlogin @strLogin, @strPwd, 'MyDatabase', 'english'



EXEC sp_adduser @strLogin, 'myRole'

GO

should work

GO ends a batch. And in the 'next' one your variable isn't declared.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Charlottecb Hall of Fame Points: 3932 More actions · Answer 2

Perfect.

Thanks.

If I call this stored proc from my app, can any user run this stored proc provided they have been given access to it, or is it only the db owner who can run this.

Forgive my ignorance as I am a newbie!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

ok, from BOL

quote:
System stored procedures are the exception because EXECUTE permissions are already granted to the public role, which allows everyone to execute them. However, after EXECUTE has been issued, the system stored procedures check the user's role membership. If the user is not a member of the appropriate fixed server or database role necessary to run the stored procedure, the stored procedure will not continue.
...
Only members of the sysadmin and securityadmin fixed server roles can execute sp_addlogin
...
Only the dbo and members of the sysadmin fixed server role can execute sp_adduser

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

cneuhold Hall of Fame Points: 3375 More actions · Answer 4

cneuhold

Hall of Fame

Points: 3375

October 27, 2003 at 7:15 am

#479660

check "CREATE PROCEDURE" statement in BOL