K. Brian Kelley (5/10/2008)

---

The problem is that CREATE LOGIN and ALTER LOGIN won't take the variable. The way around this is to use Dynamic SQL. For instance:

CREATE PROCEDURE dbo.Create_Login

@username sysname

AS

BEGIN

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(4000);

SET @SQL = 'CREATE LOGIN ' + @username + ' WITH PASSWORD = ''12345'', DEFAULT_DATABASE=[dbname], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON';

EXECUTE(@SQL);

EXEC sys.sp_addsrvrolemember @loginame = @username, @rolename = N'sysadmin';

SET @SQL = 'ALTER LOGIN ' + @username + ' DISABLE';

EXECUTE(@SQL);

END;