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 [at] brugernavn as char(50)
DECLARE [at] password as char(50)

set [at] brugernavn = 'testuser'
set [at] password = N'testpassword'

select [at] brugernavn
select [at] password

CREATE LOGIN [at] brugernavn WITH PASSWORD= [at] password,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=ON, CHECK_POLICY=ON

USE [testbase]

CREATE USER [at] brugernavn FOR LOGIN [at] 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 ' [at] 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 ' [at] brugernavn'.

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

Best regards,

Brian

> 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 [at] SqlStatement nvarchar(MAX);

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

EXECUTE ( [at] SqlStatement);

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Brian Sprogų" <brs [at] mailme.invalid> wrote in message
news:479086ad$0$15886$edfadb0f [at] 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 [at] brugernavn as char(50)
> DECLARE [at] password as char(50)
>
> set [at] brugernavn = 'testuser'
> set [at] password = N'testpassword'
>
> select [at] brugernavn
> select [at] password
>
> CREATE LOGIN [at] brugernavn WITH PASSWORD= [at] password,
> DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
> CHECK_EXPIRATION=ON, CHECK_POLICY=ON
>
> USE [testbase]
>
> CREATE USER [at] brugernavn FOR LOGIN [at] 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 ' [at] 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 ' [at] brugernavn'.
>
> How do I get the script to do what I want it to ?
>
> Best regards,
>
> Brian

Hi Dan,

I modified the script to look like this, and it worked like a charm :-)

USE [master]
GO
DECLARE [at] brugernavn as char(50)
DECLARE [at] password as char(50)
DECLARE [at] SqlStatement nvarchar(MAX);

set [at] brugernavn = 'testbruger'
set [at] password = N'testpassword'

SET [at] SqlStatement =
N'CREATE LOGIN ' +
QUOTENAME( [at] brugernavn) +
N' WITH PASSWORD = ' +
QUOTENAME( [at] password, '''') +
N', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=ON, CHECK_POLICY=ON ';

EXECUTE ( [at] SqlStatement);

use [testbase]

SET [at] SqlStatement =
N'CREATE USER ' +
QUOTENAME( [at] brugernavn) +
N' FOR LOGIN ' +
QUOTENAME( [at] brugernavn)

EXECUTE ( [at] SqlStatement);
GO

Thanks a lot for your help.

Best regards,

Brian

Dan Guzman skrev:
>> 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 [at] SqlStatement nvarchar(MAX);
>
> SET [at] SqlStatement =
> N'CREATE LOGIN ' +
> QUOTENAME( [at] brugernavn) +
> N' WITH PASSWORD = ' +
> QUOTENAME( [at] password, '''');
>
> EXECUTE ( [at] SqlStatement);
>