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.