Impersonating another user to get their security right.
I'm not a DBA. There. I said it. Please don't point and laugh. I develop stored procedures that only specific users or AD groups are allowed to execute. Once I'm done, I wander down the corridor to my friendly junior DBA and say "Hey Buzza... maaaate! I need you to set up Joey Bloggsevski (or some AD group) so that he can run stored procedure X on Database Y on Server Z... and this time make sure he can't do anything else." Buzza will peer out from a pile of old 486 chassis, crumpled energy drink cans and monitors and say "Yep, no worries buddy, leave it with me." Empty soup tins too. What is it with you DBAs and empty soup tins? Ten minutes later I'll get an e-mail from him saying "Done. Give it a run and let me know how you went." This is where it gets awkward. I have to contact Joey Bloggsevski and get him to execute the stored procedure through whatever client application. Invariably... INVARIABLY Joey will call me saying "I'm getting [insert connection error message here]". So I have to go back to Buzza and explain. He'll fix that problem and I'll contact Joey again and [loop until all connection problems are resolved]. Frankly it's embarrassing and we look unprofessional. In fairness we ARE unprofessional, but I'd rather we didn't look that way to the outside world. Is there a way I can make my login behave as if it were Joey's, so that we can work through all our incompetence in-house? That way I can be sure that when I go to Joey, everything just works and he recommends me for a massive bonus?
Overall issue you have is your are not communicating the requirements fully to the DBA. Just telling them user X needs permissions to execute stored procedure Z, does not fill the Y in the middle of what your stored procedure does. As you stated you want to "make sure he can't do anything else", so the DBA only provides you with execute permission for that stored procedure. When in all reality you actually do need that user to do more than just execute. Now whether it is that DBA's responsibility to read your mind, I can't say but we take things literally when it comes to security of our data...yes we treat it as our data. If they can read your mind, then I'd give them the massive bonus. A better way of communicating to that DBA is to tell them you need user X configured where they can execute procedure Z. Procedure Z performs the following Y on these objects in the database. Once that is done you could always have the DBA create a test login for you, to just verify before telling your users it is ready. You can use something like this, but it has a caveat: EXECUTE AS USER = 'MyDomain\MyUser' --run your test code REVERT; The caveat is the user/login you pass to that command has to explicitly exist on the instance. So as you stated in your question the DBA could be using an AD Group, the login that is a member of that group would have to physically exist on the instance. You can [read BOL on this command and scenario]. In your case what I would do is have an AD login created like `MyDomain\TestAccess` or something like that (to indicate it is just a test account). Then when you need to verify or test the permissions you need assigned to any specific user have the DBA also provide the access to this login. Test the access prior to giving the all clear to your user(s). You would need to make note that once your testing is done just have the DBA drop the login, to clear the access it had so you can start fresh next time. :
I don't think there is. That's kind of what security is all about. What you really want to do is identify the roles that users are in and administer security on that basis. Ideally you'd have an Active Directory (AD) group for each role and people would be added to the appropriate group. Then you set up roles in the database which collect together a set of permissions that go together. Grant those permissions to those roles. Then you add the AD groups to the database role(s) that give them the permissions they should have. The benefits are that when new people join or when people move around they only have to have their AD group membership adjusted. When you add new database stuff you just need to decide which database roles it belongs to. The rest takes care of itself. This does require some buy-in from whoever has authority over AD but it's worth the effort because it can also be used to control folder access, Reporting Services security and so on.