SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dynamic SQL - Looking for an example for LOGIN
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mallorz4
Starting Member

27 Posts

Posted - 03/03/2014 :  13:51:52  Show Profile  Reply with Quote
I created a cursor that moves through a table to retrieve a user's name.

When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.

I'm getting an 'incorrect syntax' error at the variable. For example ..

CREATE LOGIN @NAME WITH PASSWORD 'password'


I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.

My dilemma is this - I thought I was using dynamic SQL. Could I get an example or further explanation?

tkizer
Almighty SQL Goddess

USA
36613 Posts

Posted - 03/03/2014 :  14:09:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
A stored procedure doesn't help unless it uses dynamic SQL.

Dynamic SQL is like this:

declare @sql varchar(1000)
set @sql = 'create login ' + @name + ' with password ''password'''
print @sql
exec(@sql)

If you need to get fancier, you'll need to use sp_executesql.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

mallorz4
Starting Member

27 Posts

Posted - 03/03/2014 :  16:46:15  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

A stored procedure doesn't help unless it uses dynamic SQL.

Dynamic SQL is like this:

declare @sql varchar(1000)
set @sql = 'create login ' + @name + ' with password ''password'''
print @sql
exec(@sql)

If you need to get fancier, you'll need to use sp_executesql.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Thank you! I seen that syntax on another post but didn't quite understand; it seemed to click with yours.

I appreciate the help.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36613 Posts

Posted - 03/03/2014 :  18:00:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're welcome, glad to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000