A Good General Delegates His Work

Or more appropriately titled “How to get someone else to do your job”. If you are anything like me, you
hate changing passwords for SQL Server users. One of the most annoying questions I get starts with
“I
forgot my password. Can you reset it for me?”
(you need a good nasaly voice to get the effect).
This is only
slightly more annoying than having the HR person give me a form requesting a new user be added to
the server (immediately, of course).

So what can you do? Well, when I was a network admin and we got our first SQL Server, the corporate
DBAs must have felt like I do because they gave me the sa password and explained how to add a user with
the Security Manager application and warned me not to do anything else on the server. Of course I
promptly logged in with isql and proceeded to begin learning SQL and how the server works. However, I
learned from their mistake and never give the sa password to anyone who does not need it. Including my
boss!!! (More on how to do this in another article)

With the introduction of v7.0, security was more modular and you could assign people to roles that would
give them limited authority and would somewhat alleviate security concerns over delegating user
management to a non-DBA. However, I still do not like to give specific rights on the server to an HR person
(they would probably forget their password) and I definitely do not like putting Enterprise Manager on
a non-technical person’s PC. I solved the problem prior to SQL 7 and still see this technique as a useful
application even with SQL 2000. At the end of the article, I will give a few reasons and uses for this
application with SQL 7 or 2000.

The Application

Here is the code for the objects and a brief description with a detailed explanation to follow.

  • DBA_Assist – The primary table that holds the user information.
  • dbspUsrMgmt – A stored procedure that will allow a non-sa to add a user, drop a user, or change a
    password.
  • dbspRunCmd – The stored procedure that acts as the proxy for the sa and actually performs the work of
    user management.

Installing this application

To install this, choose the database where you will be delegating user management and compile these three
scripts in that database. Then schedule a task to run as often as you need it with the following execution
code:

&nbsp&nbsp&nbspexec dbspRunCmd

As you can see, I am not one for long installation scripts or complicated instructions.

If you choose to create a front end for this application (other than ISQL or Query Analyzer) it needs to get
the following information from the user:

  • New or existing username
  • password
  • group (if applicable and it always should be)
  • Action (Insert, change password, or delete).

This needs to be provided to the stored procedure dbspUsrMgmt.

I am working on an ASP application to go with this and will post it shortly. (Actually it’s done, but the
code is a mess and I need to clean it up.)

The Details

If you are still reading then I guess I need to explain further how this works. OK, here goes and please let
me know if this makes no sense.

A normal user cannot add additional users to SQL Server. There is no getting around this. But what they
can do is log the information about what they want to accomplish to a table and have the DBA read it later
and process their request. This is the same process that we use a piece of paper for when a new employee is
hired. So, I decided to create a table (DBA_Assist) that accepts instructions.

In order to keep this generic and simple, I decided the table would hold the SQL code to perform the user
management. So the contents of DBA_Assist will look like this to add “Bob” with a password of “Marley”
to the “Music” database and to the “Reggae” group in this database.

DBA_AssistID TSQLCode InsDt ComplDt
1 exec sp_addlogin ‘Bob’, ‘Marley’, ‘Music’ 09/01/2000 01/01/1900
2 exec sp_Adduser ‘Bob’, ‘Bob’, ‘Reggae’ 09/01/2000 01/01/1900

This is the table after the initial insert is run. The code for this initial insert is as follows:

exec dbspUsrMgmt '', '', '', ''

Now I do not expect my users to be able to run stored procedures much less write T-SQL code, so I how
does this code get into the table? I have a front end that accepts parameters from the user. It is basically a
web page that gets the username, password, and action from a user. I have different pages for different
databases and with my web databases, there is usually only one group, so I do not have a choice on the pages for these.

Once the parameters are gathered, then I pass them to the stored procedure dbspUsrMgmt which generates
and inserts the proper T-SQL commands into the table. I hate doing repetitive work and try to get the
computer to generate as much code as possible for me. If you look through the code for dbspUsrMgmt, it is
fairly simple. I use a series of string concatenations to piece together a T-SQL command and then insert it
into DBA_Assist. There are a series of logical tests that generate different code depending on the action
passed in. This is a very extensible procedure and new actions (like changing groups) can easily be added
to the code.

Now I have the code in the DBA_Assist table. I can just check this table ever couple hours, select the code
out and run it, right? Well, I could, but as I said, I hate user management. So I take advantage of two SQL
Server constructs to get the server to do the work for me. One is the EXEC() function and the other is the
SQL Agent (v7.x and above) or SQL Executive (v6.5 and below).

The EXEC() function will take a single string parameter and run it as a batch. Just as if you typed a line of
code in Query Analyzer or ISQL and hit the little green arrow. So guess what code I pass into EXEC()? You
got it, the T-SQL column from DBA_Assist. So how do I decide what code to pass in? I use the ComplDt
column as a flag. For new (unexecuted) code, I set this to ’01/01/1900′. These are the rows that I return in
my result set and loop through using a cursor. I ensure they are executed in the proper order by using the
InsDt column as an ordering column in my cursor query. This way I ensure I do not add a user to a
database before adding him as a login to the server.

The scheduling facilities of SQL Server are extremely powerful if you setup this service to run as a domain
account. I always do and any command that I run executes as this user. I always ensure this user is an sa on
the SQL Server and carefully guard the XP_ procedures that it can run. Since this account is an sa, it can
add users. So I have a task setup that will run every five minutes from 4:00am to 8:00pm and execute the
dbspRunCmd stored procedure.

Conclusion

I’m done, well I have to deliver an ASP front end, but that’s coming. I give someone a URL, setup their
login to execute the stored procedure dbspUsrMgmt and I no longer have to manage the SQL Server users
in my company. Now I can concentrate on the important work of tuning developer’s queries. And there are
other ways that I can use this application, even with SQL 2000.

For starters, if I am using SQL security for an extranet application ,not that I would, but for the sake of
arguement, let’s assume I am. I could have someone in another company manage the users for that business
partner. I could easily extend dbspUsrMgmt to add another line of code for each action that would send an
email confirming the changes. Or I could have the system schedule reports by inserting a DTSRUN
command or another stored procedure name. Then I could have a line of T-SQL that would send a
notification that the report was complete along with the URL of an HTML file that contained the report.

I am sure I am barely scratching the surface for the possible uses of this application and I hope people out
these will send me an email with their ideas. I am always looking to learn and understand more about how
people use SQL Server and the various programming techniques available. So, let me know and good luck.

Steve Jones
)2000 dkranch

Latest Articles