Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Create Login Script Expand / Collapse
Author
Message
Posted Friday, April 13, 2012 2:17 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 23, 2014 2:21 AM
Points: 1,205, Visits: 921
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;



Brian, now I realise why I could not get your script working I for got the brackets in "EXECUTE(@SQL);".


Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #1283445
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse