Dynamic Login & User Creation

Hello. I'm trying to create a new login and username inside a trigger using variables.

This code works:

create login testUserName with password = 'testPassword'

The problem comes when I try to use variables for the username and password like this.

create login @.username with password = @.password

Does anybody know how to get around this problem?

BTW, the error message it gives is this, but I really doubt that semicolons have anything to do with it. If I literally type my data in the create login call it works fine, but when I use variables it doesn't.

Msg 102, Level 15, State 1, Line 14

Incorrect syntax near '@.username'.

Msg 319, Level 15, State 1, Line 14

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

SQL Server doesn't allow you to specify variables in most DDL statements. So the only way is to form the CREATE LOGIN statement as a string and execute it using dynamic SQL. See the EXECUTE topic in Books Online for more details on how to execute SQL statements dynamically.|||

This doesn′t work unless you wrap it in dynamic SQL:

DECLARE @.SQLString VARCHAR(400)
SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + WITH PASSWORD ' + @.PASSWORD
EXEC(@.SQLSTRING)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks a lot! Just in case anybody else has this problem, here's some code that does what I wanted.

declare @.USERNAME varchar(50);

declare @.PASSWORD varchar(50);

set @.USERNAME = 'testUserName2';

set @.PASSWORD = 'testPassword';

DECLARE @.SQLString VARCHAR(400)

SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + ' WITH PASSWORD = ' + CHAR(39) + @.PASSWORD + CHAR(39)

EXEC(@.SQLSTRING)

|||

You need to protect the dynamic SQL against SQL injection attacks. So you need to quote the login name which is an identifier otherwise potentially someone could provide a malicious login name which can be used to do attack the database. The password part is hard to protect since it is just a string. So you will have to validate it for certain characters in the front-end.

declare @.USERNAME varchar(50);

declare @.PASSWORD varchar(50);

set @.USERNAME = quotename('testUserName2'); -- Use quotename to form the identifier

set @.PASSWORD = 'testPassword';

DECLARE @.SQLString VARCHAR(400)

SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + ' WITH PASSWORD = ' + CHAR(39) + @.PASSWORD + CHAR(39)

EXEC(@.SQLSTRING)

|||Thanks for the tip. We're currently protecting against it on the front end but I recently heard there are steps I can take in the database for protection, so I'll have to look into that. Thanks.

Dynamic dimenssion security

I am trying to pass the username to a vb function in the allowed members set of a dimenssion. For some reason it is not getting to the function. All of the other parameters are being received as I am logging them in the event log.

Any ideas on why it looks like the username is not being picked up at all would be great.

Thanks in advance.

Chris

Chris,

I'm assuming you are using the UserName() function? Can you post the MDX expression you have in the allowed members set definition?

You might try creating a test calculated member using some or all of the MDX expression you are using for that parameter just to make sure it is resolving in the manner you think it should...

Dave F.

|||

Thanks for the reply Dave. In the allowed members set I am doing a call to a vb strored procedure which takes in as parameter a username and level in the hierarchy it is providing security for. The stored procedure then executes a sql query on a view which returns an mdx string providing a set of members for a specific level.

If you execute the stored procedure from a query in management studio for example

SecurtiySP.GetOrgMembers(USERNAME, 7)

This then does return correctly. But when reporting services executes a query, it doesnt seem to pass the users username at all. I believe it may be to do with the account RS is running as and that it isnt part of AD and cant impersonate other accounts, so I will have a look next tuesday and see what I can find.

|||

Yes, the problem could be with having Reporting Services in the mix (or any other mid-tier application). If Reporting Services is running on a separate server, you would have to set up Kerberos in order for Reporting Services to impersonate the authenticated users in relation to Analysis Services. Also the server running Reporting Services will have to be trusted for delegation. More on this here:

http://sqljunkies.com/WebLog/mosha/archive/2005/01/25/6905.aspx

|||

Hi Michael,

Thanks for the reply. I have since found the problem. It seems accoring to this article http://support.microsoft.com/kb/913667 that we needed to run sp1 and the hotfixes. It is now passing the correct username :)

Chris