login script

• joshphillips7145

  Right there with Babe

  Points: 720

  More actions

  August 16, 2012 at 6:29 pm

  #260211

  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')

• Lokesh Vij

  SSChampion

  Points: 10836

  More actions

  August 16, 2012 at 10:01 pm

  #1526899

  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[/url]
  Guidelines for answers on Performance questions

  Link to my Blog Post --> www.SQLPathy.com[/url]

  Follow me @Twitter