question

Mark avatar image
Mark asked

SQL Login syntax in Dynamic T-SQL

I keep trying various combinations, but I am not getting the password right in my dynamic SQL: SET @sql = 'CREATE Login ' + @UserName + ' WITH PASSWORD = ' + CHAR(39) + @UserName + CHAR(39) + ', DEFAULT_DATABASE = MyDefaultDB' Of course, the password needs to be surrounded by single quotes. Thanks for your help!
sql-server-2005logindynamic-sql
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark avatar image Mark commented ·
On the 2005 production database and on my test machine (which is 2008 R2), I can log in fine after I manually change the password. So something odd is going on with the password.
0 Likes 0 ·

1 Answer

·
JohnM avatar image
JohnM answered
Forgive me, but your code seems to work for me. You can also use additional sets of single quotes to escape the character. DECLARE @sql VARCHAR(500) DECLARE @userName VARCHAR(100) SET @username = 'jdoe' SET @sql = 'CREATE LOGIN ' + @UserName + ' WITH PASSWORD = ''' + @UserName + ''', DEFAULT_DATABASE = TempDB' PRINT @sql SET @sql = 'CREATE LOGIN ' + @UserName + ' WITH PASSWORD = ' + CHAR(39) + @UserName + CHAR(39) + ', DEFAULT_DATABASE = TempDB' PRINT @sql GO Returned (respectively): CREATE LOGIN Jdoe WITH PASSWORD = 'Jdoe', DEFAULT_DATABASE = TempDB CREATE LOGIN Jdoe WITH PASSWORD = 'Jdoe', DEFAULT_DATABASE = TempDB Both were parsed and executed on a 2005 instance without any issues. Am I missing something?
9 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image Pavel Pawlowski commented ·
Check the SQL Server Log for details about the Error 18456. Based on the State information showed in the SQL Server Log it is possible to identify the right reason for the Login Failed.
2 Likes 2 ·
Mark avatar image Mark commented ·
It will eecute fine, then I can't log in as the new person even though I can see that they have been added. When I change their password manually, it's fine though. (I'm also adding them to a database by the way - that part seems to work fine.)
0 Likes 0 ·
JohnM avatar image JohnM commented ·
Hmm...I was able to log into my 2005 instance via SSMS using the credentials I used in my script, jdoe/jdoe. Are you using SSMS to log the user in or some other method? Just trying to think outside the box a bit . .
0 Likes 0 ·
Mark avatar image Mark commented ·
Thanks John. Yes, using SSMS. By the way, I'm mainly a developer, trying to stick my toe in more of a DBA role here. So I'm not used to trying code like this.
0 Likes 0 ·
JohnM avatar image JohnM commented ·
No worries. ;-) What error is thrown when you attempt to log in?
0 Likes 0 ·
Mark avatar image Mark commented ·
The error is "Login failed for user 'myUser' Error 18456.
0 Likes 0 ·
Mark avatar image Mark commented ·
Actually that error number is from a 2008 R2 test machine I'm using (a develper's edition on my PC actually)
0 Likes 0 ·
JohnM avatar image JohnM commented ·
@Pavel Pawlowski beat me to it. ;-) You can check here to see what he is referring to. This should give you a beter idea as to what is causing the login failure. http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx
0 Likes 0 ·
Mark avatar image Mark commented ·
Excellent. It's not working on my test machine since the server is not configured for SQL Server auth. And, I can't see the logs on the Production server - I'll have to ask the DBA to do that. :(
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.