Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mallorz4
Starting Member
27 Posts |
 Posted - 2014-03-03 : 13:51:52
|
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
38200 Posts |
Posted - 2014-03-03 : 14:09:11
|
| 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 @sqlexec(@sql)If you need to get fancier, you'll need to use sp_executesql.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
mallorz4
Starting Member
27 Posts |
Posted - 2014-03-03 : 16:46:15
|
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 @sqlexec(@sql)If you need to get fancier, you'll need to use sp_executesql.Tara KizerSQL Server MVP since 2007http://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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-03 : 18:00:09
|
| You're welcome, glad to help.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
Dynamic SQL - Looking for an example for LOGIN