How to use Create Loing into Stored Procedure

• golash69

  Newbie

  Points: 7

  More actions

  July 17, 2010 at 5:25 am

  #226961

  Hi,

  I Try to use Create Loing in my SP:

  set ANSI_NULLS ON

  set QUOTED_IDENTIFIER ON

  GO

  ALTER PROCEDURE [dbo].[sp_AddNewUser]

  -- Add the parameters for the stored procedure here

  @NewUserName varchar(20),

  @Password varchar(20)

  AS

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  SET NOCOUNT ON;

  CREATE LOGIN @NewUserName WITH PASSWORD=@Password, DEFAULT_DATABASE=[ShowMeTheMoney], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

  CREATE USER @NewUserName FOR LOGIN @NewUserName

  EXEC sp_addrolemember N'db_datareader', @NewUserName

  END

  and i get the folling error:

  Msg 102, Level 15, State 1, Procedure sp_AddNewUser, Line 15

  Incorrect syntax near '@NewUserName'.

  Msg 319, Level 15, State 1, Procedure sp_AddNewUser, Line 15

  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.

  When is use sp_AddLogin and sp_AddUser instead Create Login and Create User it's work.

  Microsoft set the sp_AddLogin and sp_AddUser as Deprecate SP.

  Tnx 🙂

• DataDog

  Hall of Fame

  Points: 3305

  More actions

  July 17, 2010 at 3:59 pm

  #1194590

  I think the username must be an identifier

  that means not a variable or string literal

  CREATE LOGIN superman -- OK

  CREATE LOGIN 'superman' -- NOT OK

  CREATE LOGIN @superman -- NOT OK

  one of the joys of T-SQL is never quite knowing when you can use a variable ... BOL usually gives clues based on the syntax

  workaround could be:

  exec('create login ' + quotename(@username) + ' blah blah blah')

  I think PASSWORD requires a string literal

  'with password = ' + quotename(@password, '''') + ' blah blah'