problem with stored proc

[Sagaert Johan]

hi

i have a sp with this in:

CREATE USER [@newuser] FOR LOGIN [@newuser]

why does it create a user @newuser instead of the contents of this var ?

Johan

[Russell Fields]

Johan,

You probably based this on seeing such examples as:

CREATE USER [Domain\Login] FOR LOGIN [Domain\Login]

Because the [ ] is an escape that tells the parser not to examine the
contents of the brackets more closely. It allows the use of unsupported
characters in names. But it means that the code does not even see the
variable name. If you remove [ ], then this should work.

SET @newuser = 'Domain\Login'

CREATE USER @newuser FOR LOGIN @newuser

A variable already safely contains the value with special characters, so
does not need to be bracketed.

RLF

hi
Johan

[Sagaert Johan]

Hi
If i remove the square brackets i get a syntax error

My sp is declared as :

ALTER PROCEDURE dbo.AddAdminUser

(

@newuser nvarchar(256),

@newpw varchar

)

AS

CREATE USER @newuser FOR LOGIN @newuser;

RETURN

Post by Russell Fields
Johan,
CREATE USER [Domain\Login] FOR LOGIN [Domain\Login]
Because the [ ] is an escape that tells the parser not to examine the
contents of the brackets more closely. It allows the use of unsupported
characters in names. But it means that the code does not even see the
variable name. If you remove [ ], then this should work.
A variable already safely contains the value with special characters, so
does not need to be bracketed.
RLF

hi
Johan

[Lawrence Garvin]

Post by Sagaert Johan
Hi
If i remove the square brackets i get a syntax error
ALTER PROCEDURE dbo.AddAdminUser
(
@newuser nvarchar(256),
@newpw varchar
)
AS
RETURN

The syntax error is caused because CREATE USER cannot take a variable as an
argument.

You can work around this by executing the proc using dynamic SQL:

CREATE PROCEDURE dbo.AddAdminUser (
@newuser nvarchar(256),
@newpw varchar
)
AS

DECLARE @sql varchar(max)
SET @sql = ('CREATE USER ' + @newuser + ' FOR LOGIN ' + @newuser)
EXEC @sql
RETURN