Create Login?!

Question

Post reply

Create Login?!

estuart

Grasshopper

Points: 13
More actions
April 27, 2011 at 5:49 pm

#239229

manually created a user on the server and in the db - ts_admin.
server role - sysadmin
db roles - db_owner, db_accessadmin, db_securityadmin
Goal: to create a new user from within a stored proc
script:
begin tran srvr;
set @sql = create login ' + @ip_newuser + ' with password = ''password'' ' ;
exec (@sql);
Error: Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.
what am i missing???!!!??
oh - i've never worked with MSSQL before - so apologize now when the issue is obvious. :crazy:

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

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

KevinGurney SSCommitted Points: 1550 More actions · Answer 1

Early thoughts.

Is the command running as the account you think it is?

If you genuinely have sysadmin then CREATE LOGIN shouldn't give permission erros.

---------------------------------------------
If data can screw you, it will; never assume the data are correct.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Two comments unrelated to the security aspect...

begin tran srvr;

set @sql = create login ' + @ip_newuser + ' with password = ''password'' ' ;

exec (@sql);

There's no use in naming transactions unless it's for documentation purposes.

You never commit or roll that transaction back.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

toddasd SSCertifiable Points: 6298 More actions · Answer 3

Your quotes are off, I don't see how that runs at all.

This works for me:

declare @ip_newuser varchar(100);

declare @sql varchar(max);

set @ip_newuser = 'new_user';

--begin tran srvr;

set @sql =' create login ' + @ip_newuser + ' with password = ''password1!'' ' ;

exec (@sql);

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

estuart Grasshopper Points: 13 More actions · Answer 4

My bad! it was late... quotes are all there:

set @sql = 'create login ' + @ip_newuser + ' with password = ''password'' ' ;

exec (@sql);

for testing purposes Studio Express I've added

with execute as 'ts_admin'

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 5

Do this:

set @sql = create login ' + @ip_newuser + ' with password = ''password'' ' ;

select @sql;

Take the output and look at it. Then try to run it manually as the user running the stored procedure. It will tell you what works and what might be wrong.