Why CREATE LOGIN failed ?

• onlo

  SSCertifiable

  Points: 5080

  More actions

  October 28, 2011 at 2:58 am

  #246957

  I used dynamic SQL to make a stored procedure to create login :

  USE [db901_DEV]

  GO

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER PROCEDURE [dbo].[sp_SSSA_login]

  @input_login VARCHAR(50), @input_pass VARCHAR(50)

  AS

  BEGIN

  DECLARE @cmd nvarchar(2000)

  SET @cmd = 'CREATE LOGIN ' + @input_login +' WITH PASSWORD = ' + QUOTENAME(@input_pass,'''')

  + ', DEFAULT_DATABASE= ' + QUOTENAME('db901_DEV','')

  + ', DEFAULT_LANGUAGE= ' + QUOTENAME('us_english','')

  + ', CHECK_EXPIRATION=OFF '

  + ', CHECK_POLICY=OFF '

  Exec sp_executesql @cmd

  END

  If I execute it :

  sp_SSSA_login 'tw.test_901', '7A6b5c'

  It failed.

  If I execute it :

  sp_SSSA_login 'tw_test_901', '7A6b5c'

  It works.

  Could you tell me why ?

• sql_dba123

  SSC Eights!

  Points: 853

  More actions

  October 28, 2011 at 3:08 am

  #1400533

  Replace the CREATE LOGIN statement with the below statement

  SET @cmd = 'CREATE LOGIN [' + @input_login +'] WITH PASSWORD = ' + QUOTENAME(@input_pass,'''')

• onlo

  SSCertifiable

  Points: 5080

  More actions

  October 28, 2011 at 3:39 am

  #1400538

  That's great !

  It works. Thanks a lot.