It Just (De)Bugs Me, That's All

About six months ago I had a bomb dropped in my lap with regards to stored procedure debugging in SS2K5. A developer happened to make an attempt to debug a CLR stored proc and forwarded me some links describing that he needed to be a member of sys admin in order to debug. “Well, that makes sense for CLR objects” I thought, and then went about trying to get him the access he needed without leaving him as a sys admin while he continued to do his development work. After all, we all know that it is a best practice to do development without sys admin rights, right?

As I dived more into debugging, I came to realize that the sys admin privileges are needed for regular, plain-old, standard T-SQL stored procedures. Huh? Surely this is a joke, right? You would have to be crazy to take such functionality and require developers to be sys admins, especially since we know they should not be developing as sys admins. Exactly how I am supposed to pull off this duality? Am I expected to turn the lights on and off every time a developers wants to debug a standard stored procedure?

And I get a kick out of how they have hidden the debug tool. See, most folks around here think that SSMS is the natural progression from Enterprise Manager and/or Query Analyzer. So they will go about asking for SSMS to be installed and then I get a phone call the next day asking such things as “Where do I build SSIS packages?”, and “Where is the debugger?” Well, they are located in the tools that Microsoft has decided you should be using for development now, mainly they are in specific flavors of Visual Studio.

So, we go through the process of getting the developers those tools, and then I am hit with the inevitable request to be a sys admin in order to debug a proc. Wonderful. Can I get you some keys to the server room while I am at it? This just seems so, well…crazy. The story I read about this is that Microsoft found that it was possible for a non-sys admin to manipulate the debugger in order to give themselves elevated rights, even sys admin rights. So, as a security measure they wanted it to be very clear that since a debugger could hack their way to be a sys admin, you should know in advance and be required to give it to them anyway. I suppose that makes sense to someone, but I would have found it better to remove the debugger from this release until they can figure out a better way to get the functionality there that satisfies both parties.

Now, I am not one to back away from a challenge. And this certainly felt like one to me. I set about trying to figure out a way around this sys admin requirement. I dived into the debug process in an attempt to figure out a way to get it to work without giving away the keys to the kingdom, so to speak. It is not as if I do not trust developers. But the fact is many of our boxes are shared environments, and not dedicated to just one group or person. Thus, small changes to the server settings can affect other work. Heck, small changes could affect our ability to monitor the environment effectively. I don’t think I need to tell you the dangers of allowing a large number of people the ability to have sys admin rights. It makes an administrators job much more challenging.

The first thing I did was try to grant execute rights to just the debug proc, sp_sdidebug. No luck there, the VS interface would still not allow me to step into a stored procedure. At that point, I also tried to be smart and think of a way to get the job done by using groups within Active Directory. So, I came up with an idea. I would have a group created within AD, and tag it appropriately (for an example I will use INSTANCENAME-DEBUG as the AD group name). The idea would be that developers could get themselves added to this group as necessary and I would not have to handle those requests. So far, so good.

Next up, I add that group as a member of the sys admin fixed server role to the instance. Guess what? Debugging works. Of course it does, right? So, how to save people from hurting themselves? I came up with the idea that I could simply issue a series of DENY statements to the AD group login. So, I could deny creating databases, backing up or restoring databases, creating linked servers, shutting down the services, etc. You know, all the important items that you would want to control as an administrator. I ran the following statement:

SELECT * FROM fn_my_permissions (‘[INSTANCENAME-DEBUG]’, ‘DATABASE’);

to list out my current permissions, then ran:

DENY CREATE DATABASE TO [INSTANCENAME-DEBUG]

Then, I ran the original select, and found that my permissions did not change. I still had the CREATE DATABASE permission and sure enough, I was still able to create a database.

What? How is it possible? Well, I guess when you are a sys admin, no one can issue you a deny statement. It just does not take affect. Drat.

So, I called Microsoft to complain. I know, you can hardly believe that I would be the type of person to call and complain about anything. I explained what I was trying to do, and how I felt that they really screwed up with the debugger. The tech on the other end of the line agreed with me completely. She also decided to take a different route to accomplish the same goal. She issued a series of DDL triggers on a sys admin login. For example, she issued a trigger to rollback a create database statement. And this worked! However, since the login is a sys admin, that login can simply disable the trigger. So, it was a small victory.

At this point I do not know what to do. I need to provide functionality to my developers and at the same time i need to provide them a stable environment. The debugger in SS2K5 does not allow for me to wear both hats. I need to find a better way. Perhaps I need to issue some triggers, at least it would be better than nothing. And I could tie that to Notification Services to generate some alerts and reports on non-approved activities. Perhaps a trigger on the trigger to tell me if someone has disabled any triggers? A silent alarm, if you will?

How about Microsoft spending the time to get this done right? Should I hold my breathe until SP3?