You’d be amazed (or maybe you wouldn’t) how often I hear this:
“I used to be able to do xyz but now I can’t. Can you fix it?”
Aside from the difficulties of proving they actually had the permissions they say they did (security is all about paranoia right?) the biggest question is why did those permissions dissapear? If we don’t know why, then we can’t stop it from happening again, right? In general I see this as one of several categories.
- Something unusual. For example did you know that if you change the owner of a schema any permissions for the objects in the schema disappear?
- Something procedural. For example we do periodic permissions reviews and people are frequently removed from AD (Active Directory) groups that they shouldn’t be a part of anymore. This, of course, causes them to lose permissions. This one can be a royal pain to track down. It’s not in SQL Server at all and you need to look at whatever processes you have in place to track changes in AD
- Something avoidable. Did you know that if you drop an object all of the permissions for it disappear with it? Fairly logical right? And by extension if the process that moves your code drops and re-creates objects then the permissions for those objects will disappear. Get used to using the command CREATE OR ALTER. It’s available for most objects (not tables I’m afraid). Here is the BOL for CREATE PROCEDURE. You can see the option right at the top of the syntax section.
- Something global. The database was restored over for whatever reason and the permissions weren’t correctly re-applied afterwards. Hard to prove but easy to disprove by looking at the restore history. Hopefully you scripted out the permissions ahead of time or at least took a backup before doing your restore.
- Something else. Your permissions were actually removed. This is the least common in my experience but it does happen. Sometimes it’s by mistake, sometimes we’ve gotten a request to remove certain permissions. I’ve even seen developers get in and do this deliberately for various reasons. Which is almost always a good sign that that developer has way too many permissions. Regardless, I have a DDL trigger that I’ve written to help with this one. I’ll be posting it soon.