How : write a stored procedure to include CREATE LOGIN ?

Question

Post reply

How : write a stored procedure to include CREATE LOGIN ?

onlo

SSCertifiable

Points: 5080
More actions
February 4, 2009 at 2:00 am

#127714

Dear all,
I need to write a batch job to non-DBA or even non-IT people to create SQL login, user, password & access rights.
But unluckily, the following statement fail :
CREATE PROCEDURE test_user
@input_login CHAR(8), @input_pass CHAR(10)
AS
BEGIN
SET NOCOUNT ON;
CREATE LOGIN @input_login WITH PASSWORD = @input_pass
END
Is it possible to include CREATE LOGIN, CREATE USER, GRANT, ... commands into a stored procedure and pass input parameters for them to execute ?
If no, I can only use sp_addlogin but Microsoft says it will be removed in a future version.

Viewing 7 posts - 1 through 6 (of 6 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

I think you have to use dynamic SQL to achieve this.

CREATE PROCEDURE test_user

@input_login CHAR(8), @input_pass CHAR(10)

WITH EXECUTE AS 'AdminUser'

AS

BEGIN

DECLARE @cmd nvarchar(2000)

SET @cmd = 'CREATE LOGIN ' + @input_login +' WITH PASSWORD = ' + @input_pass

Exec sp_executesql @cmd

END

I also added a EXECUTE AS clause, because as you said the procedure will be executed by users which are not a member of the sysadmin or securityadmin role.

[font="Verdana"]Markus Bohse[/font]

onlo SSCertifiable Points: 5080 More actions · Answer 2

Thanks.

But I don't know why the execution fail :

EXEC dbo.test_user 'tuser001', 'tuser0011'

Error message :

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'tuser0011'.

Any ideal ?

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

That's what happens when you just write some quick code without testing it. THe procedure fails because the password parameter adds the string without quotes. Change it to this:

ALTER PROCEDURE test_user

@input_login CHAR(8), @input_pass CHAR(10)

WITH EXECUTE AS 'AdminUser'

AS

BEGIN

DECLARE @cmd nvarchar(2000)

SET @cmd = 'CREATE LOGIN ' + @input_login +' WITH PASSWORD = ' + QUOTENAME(@input_pass,'''')

Exec sp_executesql @cmd

END

[font="Verdana"]Markus Bohse[/font]

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 4

One more comment. I think you should change the datatype of your input paramters to varchar. Using CHAR(10) for the password will result in trailing blanks.

[font="Verdana"]Markus Bohse[/font]

onlo SSCertifiable Points: 5080 More actions · Answer 5

onlo

SSCertifiable

Points: 5080

February 5, 2009 at 2:10 am

#938654

Thanks

the__t Hall of Fame Points: 3260 More actions · Answer 6

I'm just curious, is this to alleviate the number of requests that a DBA has to handle for a lower environment like Development? Maybe I'm just missing something (which is entirely possible), but allowing users to create their own login (by impersonating an Admin account) and grant their own permissions seems like a very risky proposition.

"Got no time for the jibba jabba!"
-B.A. Baracus