Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Automate New Logins Creation

By Leo Peysakhovich,

Recently, I was sitting on my working place when I hear another DBA’s laugh and talk. They were mentioning my name with smiling faces. Few minutes later I get a user’s service request to enter about 60 new logins to the SQL Server for the third party application and assign them to the specific role in a database. Policy of the company dictates that a strong password must be assigned to SQL Server users. E.g. password must be minimum 8 character long, use upper and lower case characters, digit(s), and a special character(s). For such purposes company has a password generator. DBA should assign a password and send it to each individual user. I am not discussing if the policy is right or wrong with DBA assigning the password and sending it to the user or why application is using SQL Server authentication but would like to show the way to automate the process and audit created logins.

There is a good article written by Randy Dyess about login auditing (http://www.sqlservercentral.com/columnists/RDyess/newenvironmentparti.asp). This article shows how to automate audits of login's password.

In my company user’s request, I get via email, kept all the necessary logins through the comma like this

user1, user2, user3…

Every time when DBA group has a new login request it becomes assigned to the one of DBA’s and he/she start a manual work. And as I was told this is very unusual request to add 60 people at the same time. So, the bet was that will it take at least 4 hours to fulfill the request. From my site, I don’t like a lot of manual labor. So, I decided to spend a time to automate the job. As a first step I created a plan of my automation like this

  1. load logins into permanent temporary login table
  2. load table with generated passwords.
  3. generate and run a script to add logins to the server
  4. generate and run a script to assign logins to the specific database role
  5. audit logins to make sure that they are properly assigned to the role and each login has the password the one that is specified in the login table.
  6. drop temporary login table

As I mentioned, passwords are generated by a special corporate application and output result is the comma delimited string as well. So, step 1 and 2 can be combined together. Another way to load user ID’s and passwords is to load it into Excel or comma delimited text file and load file using DTS package. Here is example of the source code to load 2 comma delimited strings (IDs and passwords)

In reality, this task can be done by many other different ways.

Step 1 and 2

BEGIN 

Declare @logins varchar(8000), @passwords varchar(8000), @defaultdb varchar(20)
        , @dbrole varchar(30) 

Set @logins = (copy and paste comma delimited string of logins)
Set @passwords =  (copy and paste comma delimited string of passwords)
Set @defaultdb = 'master'
Set @dbrole = 'userdbrole' 

create table temp_logins(logins varchar(20)
    , passwords char(8)
	 , defaultdb varchar(20)
	 , dbrole varchar(20) )

-- load logins into a table.
WHILE ( CHARINDEX(',', @ logins, 0) > 0 )
 BEGIN
   insert into temp_logins (logins,passwords, defaultdb, dbrole)
     select ltrim(rtrim(LEFT(@logins, CHARINDEX(',',@logins, 0) - 1))),
            ltrim(rtrim(LEFT(@passwords, CHARINDEX(',',@passwords, 0) - 1))),
				@defaultdb, @dbrole
                       
SET @logins = ltrim(rtrim(RIGHT(@logins, LEN(@logins) - 
              CHARINDEX(',', @logins, 0))))
                       
SET @passwords = ltrim(rtrim(RIGHT(@passwords, LEN(@passwords) - 
                CHARINDEX(',', @passwords, 0))))
  END
END
 

-- Step 3 - generating script to add logins to a server

select ' exec sp_addlogin @loginame = ''' + logins + ''',
      @defdb=  ''' + defaultdb + ''' ,
      @passwd = ''' + passwords + ''' '
   from temp_logins

-- Step 4 - generating a script to add user to the special role in database

select 'EXEC sp_adduser ''' + loginnm + ''', ''' + logins +
       ''', ''' + dbrole + ''' '   from temp_logins 

To add logins and users run the output of the select statements in Query Analyzer.

-- Step 5

- Let’s check if users are added and assigned to the proper database role.

BEGIN

create table  #userdbtbl (dbnm varchar(50) null
                        , usernm varchar(50)
								, groupnm varchar(50)
								, loginnm varchar(128)
								, defdbnm varchar(50)
								, userid int
								, sid varbinary(100)
								, tid int identity(1,1) ) 

insert into #userdbtbl (usernm , groupnm ,loginnm , defdbnm ,userid , sid)     
 exec sp_helpuser
            
select distinct usernm , groupnm ,loginnm , defdbnm ,userid  
  from #userdbtbl t1
    inner join temp_logins t2
	    on t2.logins = t1.loginnm

drop table  #userdbtbl

END

Now we need to check if users will be able to connect to the database with their login and password.

declare @cmd varchar(1000), @SERVERNAME varchar(20)
      , @DATABASENAME varchar(20), @TABLENAME varchar(50)
 set @SERVERNAME = 'myserver'
 set @DATABASENAME = 'mydatabase'
 select '  SELECT a.* FROM OPENROWSET(''SQLOLEDB'','''
     + @SERVERNAME+ ''';''' + logins + ''';''' + passwords + ''',
    ''SELECT * FROM ' + @DATABASENAME + '. INFORMATION_SCHEMA.TABLES '') 
	 AS a  '
from temp_logins

Run the output of the query in Query Analyzer and check if any login and password combination is failed to return the query result

Step 6

After all drop table temp_logins. Take an educated guess how long it took me to add all 60 logins? Right, I spent about an hour to prepare the statements. And another 20 minute to load and verify all 60 logins. Plus, this logic and prepared scripts I am using when more than 5 logins should be added to a server or a database.

Basically saying, the main idea of the exercise is to load logins and passwords into a table by any available method and generate a script. This methodology can be used for many various tasks. For example, I am using this technique when I would like to generate statement for the number of rows in each table.

select ' set NOCOUNT ON

select count(*) as ' + table_name + ' from pubs.dbo.' ' + table_name from INFORMATION_SCHEMA.TABLES

If the output result of the query is displayed as “Results in Text” in Query Analyzer, the output can be copied, run in another Query Analyzer window and display how many rows in each table in database.

Conclusion

Almost any DBA task can be automated. And if you do so, after a while set of handy scripts and stored procedures will start making your DBA live much easier at the same time allows you spend much less time for the routine tasks.

Total article views: 8809 | Views in the last 30 days: 2
 
Related Articles
SCRIPT

Login Password Policy

This script lists all of the SQL logins with their password policy

FORUM

Reset Password for linked server login

Reset Password for linked server login

FORUM

Moving Sql server 2000 user login and passwords to Sql server 2005

Moving user logins and passwords

SCRIPT

SQL_Login_Password_Expiry_Notification

This store procedure will send a proactively mail about the SQL login name & there expiry date for w...

FORUM

Server login assigned to "dbo" user within database

3rd party software, not sure how login got assigned to "dbo"

Tags
miscellaneous    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones