\[Editor's Note: Subscribe to the SQL Server UPDATE newsletter (http://www.sqlmag.com/ resources/email/update/main/cfm) to solve next month's SQL Server problem. Winners receive $100 or a $50 gift certificate to Fatbrain.com, an online high-tech bookstore.\]
And the Winners are . . .
Congratulations to Cindy Gross of Texas Health Systems and Andrew Llewellyn of Bournemouth, UK. Cindy won first prize of $100 for the best solution to the Reader Challenge. Andrew won second prize of a $50 gift certificate to Fatbrain.com.
Problem
Peter is the database administrator for his company's four SQL Server machines. The company is planning an upgrade to SQL Server 7.0, and Peter is involved in testing compatibility. As a result, he needs assistance with day-to-day operations. He has delegated much of the work to two junior DBAs; however, he's still not comfortable with letting them operate with sa permissions.
Peter needs to delegate the task of adding logins and handling passwords without giving the sa password to the junior DBAs. He knows that he can restrict control in SQL Server 7.0, but the upgrade is still a few months away. How can Peter let the other DBAs manage logins on the SQL Server 6.5 servers (adding and dropping users and changing passwords) without giving them sa control?
Solution
Peter can add new stored procedures (preferably with new names) to replace sp_addlogin, sp_password, sp_droplogin, and any other items the junior DBAs need to manage. He can generate the script for each of these stored procedures and remove the check that ensures the current user is an sa. Then, he can either replace it with their suids (and his) hardcoded or have the script read from a table, which he can populate with the junior DBAs' suids and even an expiration date or other limitations, if needed.
He can make similar changes in the Master database for sp_adduser, sp_addalias, sp_dropuser, sp_dropalias, etc., if he doesn't want to give them DBO permissions in the user databases. He can even add parameters to check for further limits (e.g., a user can't access both DB1 and DB2 on the same server).
Remember to turn on allow updates using sp_configure before creating these procedures, and (more important) turn the setting off after creation. Be very careful when you modify the system tables, and make sure to test the code!
