I was hoping to use user impersonation to setup a user that has permissions to modify DDL, and only when I need to deploy DDL changes would I impersonate that user. This way I can use my windows integrated account to query data as needed, but cannot modify the database except when I explicitly impersonate that other user.
According to the MSDN article on impersonation, you can only execute impersonation if you are a sysadmin.
So the challenge is, if my default Login, we'll call it LoginAaron, is a sysadmin of the database, how do deny myself DDL write permissions, so that I cannot execute DDL without explicitly impersonating another user with ddl_admin role(we'll call user UserDdlDeploy)?
Or is there another elegant way to accomplish this?
This is not so much a security procedure, as it is more of a safety/precautionary procedure.
Best Answer
A
sysadmin
can't demote themselves temporarily / most of the time / etc. What you want to do is create a SQL auth login that you use most of the time, and it only has the privileges to do the limited things you want to do. Then when you want to perform DDL operations, log in as your Windows login.There are probably ways to do this if your primary login is not a
sysadmin
, but I think they will be self-defeating. You'd have to log in as someone else any time you want to do anything (including things you want your lower-privileged login to be able to do, but didn't foresee when you set up the permissions). As a quick example, here are two database principals, one of which can impersonate the other:Results:
(Now, impersonation is not perfect - there are issues with using this when you need to access resources across database boundaries, for example.)
So, imagining those are real users hooked to real logins, you could add only
blat
to theddl_admin
role, and log in asfloob
most of the time. When you needed to, you could simply impersonateblat
, do your DDL things, then revert. My suggestion is to just stay away from trying to diminish the privileges of your Windows account /sysadmin
, and just get used to logging in a different way so that you can be a peon most of the time, and only elevate when you need to. I'll repeat, though, that this won't eliminate mistakes you would make in any case anyway - it only prevents you from accidentally running DDL, or running the wrong DDL, which could happen when you've knowingly impersonated, too.And, because the notion of elevating your own privileges only when you need to, in order to protect yourself because you think you'll only make mistakes when you're not elevated, seems weird to me, and because I can't help it (credit xkcd, of course):
Here is a more concrete example, that uses a real SQL authentication login.
Now, create a new session, logging in directly using
peon
/peon
, and run this code:Breaks, right? Yes, of course:
Now, you can impersonate another user, without being a
sysadmin
, so you should file a bug against whatever documentation told you that:Don't forget to clean up: