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

login script Expand / Collapse
Author
Message
Posted Thursday, August 16, 2012 6:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 25, 2012 5:50 PM
Points: 40, Visits: 66
I need to write a script that creates will create login ID's based on the contents of a table. For the purpose here I will call the table newLogins, and I need to perform 3 actions for each row in this table using dynamic SQL. First I need to create a login with a temporary password based on the first four letters of the login name followed by 9999, secondly set the default database (for this I will use AP) and three I need to create a user for the login with the same name as the login and assign the users to the 'PaymentEntry' role.

I could really use some help here I am new to sql and absolutely brand new to Dyanmic SQL!

Create Table NewLogins
(LoginName varchar(128))

Insert NewLogins
Values ('BBrown'), ('CChaplin'), ('DDyer'), ('EEbbers')
Post #1346307
Posted Thursday, August 16, 2012 10:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Hope you are looking for something like this:

DECLARE @LoginName VARCHAR(128);
DECLARE @dynsql VARCHAR(max);
DECLARE @default_db VARCHAR(200) = 'AP';
DECLARE @role_name VARCHAR(100) = 'PaymentEntry';
DECLARE c_login CURSOR FOR
SELECT LoginName
FROM NewLogins;

OPEN c_login;

FETCH NEXT FROM c_login INTO @LoginName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @dynsql = 'CREATE LOGIN ' + @LoginName + ' WITH PASSWORD = ''' + SUBSTRING(@LoginName, 1, 4)+'9999' + ''', DEFAULT_DATABASE=' + @default_db;

EXEC (@dynsql);

EXEC sys.SP_ADDSRVROLEMEMBER
@loginame = @LoginName,
@rolename = @role_name

FETCH NEXT FROM c_login INTO @LoginName
END

CLOSE c_login;

DEALLOCATE c_login;



~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1346327
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse