Temporary permissions for DDL

Question

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.

Answer 1

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:

CREATE USER blat WITHOUT LOGIN;
CREATE USER floob WITHOUT LOGIN;
GRANT IMPERSONATE ON USER::blat TO floob;

EXECUTE AS USER = 'floob';
GO
PRINT USER_NAME();
GO
EXECUTE AS USER = 'blat';
GO
PRINT USER_NAME();
GO
REVERT;
GO
PRINT USER_NAME();
GO
REVERT;

Results:

floob
blat
floob

(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 the ddl_admin role, and log in as floob most of the time. When you needed to, you could simply impersonate blat, 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.

CREATE LOGIN peon WITH PASSWORD = 'peon', CHECK_POLICY = OFF;
GO

CREATE DATABASE playground;
GO

USE playground;
GO

CREATE USER peon FROM LOGIN peon;
GO
ALTER ROLE db_datareader ADD MEMBER peon;
-- of course grant all the permissions you want here
GO

CREATE USER ddldude WITHOUT LOGIN;
GO
ALTER ROLE db_owner ADD MEMBER ddldude;
-- doesn't have to be db_owner, just a valid example
GO

GRANT IMPERSONATE ON USER::ddldude TO peon;
GO

Now, create a new session, logging in directly using peon/peon, and run this code:

USE playground;
GO
CREATE TABLE dbo.foo(id INT);
GO

Breaks, right? Yes, of course:

Msg 262, Level 14, State 1
CREATE TABLE permission denied in database 'playground'.

Now, you can impersonate another user, without being a sysadmin, so you should file a bug against whatever documentation told you that:

EXECUTE AS USER = N'ddldude';
GO
CREATE TABLE dbo.foo(id INT); -- works
GO
REVERT;

Don't forget to clean up:

USE master;
GO
ALTER DATABASE playground SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE playground;
GO
DROP LOGIN peon;
GO