1

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.

7
  • but cannot modify the database except when I explicitly impersonate that other user. - this doesn't make much sense to me - if you're protecting yourself from mistakes, you're going to make them whether you wrote some DDL or whether you wrote some DDL after impersonating the user that lets you run your DDL. Aug 11, 2015 at 19:59
  • @AaronBertrand Obviously the DDL has to be right. I am not talking about validating correctness. For the same reason you shouldn't have root admin access on an operating system by default, except only when/as needed, there is no reason for me to have DDL write permissions at all times except only when/as needed.
    – AaronLS
    Aug 11, 2015 at 20:23
  • Then the elegant answer is the simplest: don't log in as your Windows account, who is a sysadmin; reserve that for when you actually need to run sysadmin tasks (just like you run certain programs as administrator or use sudo at the command line when you need to). And I didn't mean the DDL is "wrong" - incorrect DDL shouldn't run regardless of who you're logged in. I'm saying if you run DDL by accident, that too can happen even when you previously elevated your own privileges. Aug 11, 2015 at 20:26
  • @AaronBertrand "that too can happen even when you previously elevated your own privileges" If properly applied you shouldn't be leaving yourself in an elevated state after you've performed the necessary task. Same reason switches in mission critical systems have additional cover plates that have to be toggled before they are actice. Yes mistakes can still happen while elevated, but it greatly reduces the chance of that happening by limiting the timeframes in which they can occur. It's a pretty well established best practice. The discussion is how to apply it to SQL server.
    – AaronLS
    Aug 11, 2015 at 20:44
  • It's nice to be able to open and review DDL scripts and not worry about a stray F5 in the wrong window will have catastrophic effects. If the error rate of a human is a mistake every 5 days, but you only have elevated permissions in order for that mistake to actually have an impact 10 minutes out of that time frame, you've decreased the likely hood of an actual error occurring to being the probability that the mistake lines up with that timeframe.
    – AaronLS
    Aug 11, 2015 at 20:48

1 Answer 1

4

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):

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.