Posted by Dan Guzman on 01/18/08 13:51

> How do I get the script to do what I want it to ?

Not all DCL commands accept variables (see BOL syntax) so you'll need to use
dynamic SQL. For example:

DECLARE @SqlStatement nvarchar(MAX);

SET @SqlStatement =
N'CREATE LOGIN ' +
QUOTENAME(@brugernavn) +
N' WITH PASSWORD = ' +
QUOTENAME(@password, '''');

EXECUTE (@SqlStatement);

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Brian Sprogø" <brs@mailme.invalid> wrote in message
news:479086ad$0$15886$edfadb0f@dtext01.news.tele.dk...
> Hi,
>
> I seem to have missed something here...
>
> Made a script for creating users on a SQL2005 - and it works just fine.
>
> To make things easier I would like to add a couple of variables to get
> around typing e.g. the username 3 times.
>
> My script looks like this:
>
>
> USE [master]
> GO
> DECLARE @brugernavn as char(50)
> DECLARE @password as char(50)
>
> set @brugernavn = 'testuser'
> set @password = N'testpassword'
>
> select @brugernavn
> select @password
>
> CREATE LOGIN @brugernavn WITH PASSWORD= @password,
> DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
> CHECK_EXPIRATION=ON, CHECK_POLICY=ON
>
> USE [testbase]
>
> CREATE USER @brugernavn FOR LOGIN @brugernavn
> GO
>
> The 2 select-statemens is for verifying the contents of the variables.
>
> The script fails at the line 'CREATE LOGIN...' with the following
> messages:
>
> Msg 102, Level 15, State 1, Line 10
> Incorrect syntax near '@brugernavn'.
> Msg 319, Level 15, State 1, Line 10
> 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.
> Msg 102, Level 15, State 1, Line 14
> Incorrect syntax near '@brugernavn'.
>
> How do I get the script to do what I want it to ?
>
> Best regards,
>
> Brian

[Back to original message]