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.
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:exec 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
- 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.)
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.
|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.
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.