Adding users from cursor

Hi. Perhaps I am missing something obvious, but I am confused. I can
add a SQL Server 2005 user with this command:
CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
However, I have 100 users to add, and I want to use TRANSACT-SQL to
create the users automatically and pull their initial password info
from a table. I use a cursor to do this, replacing the 'AUser' and
'hello' with variables, for example:
CREATE LOGIN @.usr WITH PASSWORD = @.pwd
I get an Incorrect syntax near '@.usr' error. Somehow this is not the
correct datatype, I would imagine. I used this method with sp_addlogin
in SQL 2000 without a problem.
I've tried declaring @.usr as a varchar or nvarchar without success.
Please offer any suggestions.
Thank you.Seems CREATE LOGIN doesn't accept a variable for one of both those parameter
s. You can build the
CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
EXEC(@.sql)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message
news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
> However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> CREATE LOGIN @.usr WITH PASSWORD = @.pwd
> I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with sp_addlogin
> in SQL 2000 without a problem.
> I've tried declaring @.usr as a varchar or nvarchar without success.
> Please offer any suggestions.
> Thank you.
>|||Excellent idea. I was hung up on trying to get it to accept the
variable.
Tibor Karaszi wrote:
> Seems CREATE LOGIN doesn't accept a variable for one of both those paramet
ers. You can build the
> CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
> EXEC(@.sql)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||You need to use sp_executesql (Dynamic SQL) to accomplish this task. Somethi
ng like this:
DECLARE
@.User nvarchar(20),
@.pwd nvarchar(20),
@.Sql nvarchar(200)
SELECT
@.User = 'Mary',
@.pwd = 'test'
SET @.Sql = 'CREATE LOGIN ''' + @.User + ''' WITH PASSWORD = ''' + @.pwd + ''''
EXECUTE sp_executesql @.Sql
See Erland's excellent article about using Dynamic SQL.
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message news:1166460727.281101
.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
>
> CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
>
> However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
>
> CREATE LOGIN @.usr WITH PASSWORD = @.pwd
>
> I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with sp_addlogin
> in SQL 2000 without a problem.
>
> I've tried declaring @.usr as a varchar or nvarchar without success.
>
> Please offer any suggestions.
>
> Thank you.
>sql