set password equal to variable

fizzerchris · April 26th, 2007, 04:10 PM

I'm scripting creating logins and want to set them all with the same default password. My thought is to declare a variable, then create the login with password equal to the variable. I get "incorrect syntax near '@PW'" using the following:
DECLARE @PW varchar(50)
SET @PW = 'password'

CREATE LOGIN loginname WITH PASSWORD = @PW,
    CHECK_POLICY=OFF,
    CHECK_EXPIRATION=OFF,
    DEFAULT_DATABASE=defaultdb

happygv · April 27th, 2007, 01:29 AM

The code looks good, BTW which line does it err out? Can you try a simple CREATE LOGIN statement without the variable and see if that fails.

_________________________
- Vijay G
Strive for Perfection

fizzerchris · April 27th, 2007, 11:32 AM

It returns error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '@PW'.

This works fine:
CREATE LOGIN loginname WITH PASSWORD = 'password',
    CHECK_POLICY=OFF,
    CHECK_EXPIRATION=OFF,
    DEFAULT_DATABASE=defaultdb

happygv · April 30th, 2007, 01:45 AM

Fine... I got the reason for it. DDL doesn't support parameterization. So you got to use dynamic SQL to overcome that.

Code:

Exec('CREATE LOGIN loginname WITH PASSWORD = ''' + @PW + ''',
 CHECK_POLICY=OFF, CHECK_EXPIRATION=OFF, DEFAULT_DATABASE=defaultdb')

Cheers.

_________________________
- Vijay G
Strive for Perfection