SQL Server & BI Blog by Andreas Wolter - Latest Comments on CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats https://www.insidesql.org/blogs/andreaswolter/?disp=comments en-EU http://backend.userland.com/rss 60 andreaswolter [Member] in response to: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats Mon, 15 Oct 2018 08:30:50 +0000 [Member] c8394@https://www.insidesql.org/blogs/ Thank you, S.E. Btw: this Blog is actually "closed". I have moved to http://andreas-wolter.com/en/blog/ Andreas https://www.insidesql.org/blogs/andreaswolter/2013/08/control-server-vs-sysadmin-sa-permissions-privilege-escalation-caveats#c8394 S.E. [Visitor] in response to: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats Mon, 15 Oct 2018 07:37:11 +0000 S.E. [Visitor] c8393@https://www.insidesql.org/blogs/ The SQL Agent problem has been resolved with SQL Server 2016. When creating/modifying a job with/to a different owner, you receive the following messages, respectively: <p> Only a member of the sysadmin server role can <em><strong>add</strong></em> a job for a different owner with @owner_login_name. (Microsoft SQL Server, Error: 14515) </p> <p> Only a system administrator can <em><strong>reassign</strong></em> ownership of a job. (Microsoft SQL Server, Error: 14242) </p> I tested this with the GUI but as far as I checked the code, the check is present inside the called SPs. Only a member of the sysadmin server role can add a job for a different owner with @owner_login_name. (Microsoft SQL Server, Error: 14515)

Only a system administrator can reassign ownership of a job. (Microsoft SQL Server, Error: 14242)

I tested this with the GUI but as far as I checked the code, the check is present inside the called SPs.]]> https://www.insidesql.org/blogs/andreaswolter/2013/08/control-server-vs-sysadmin-sa-permissions-privilege-escalation-caveats#c8393 cody_konior [Member] in response to: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats Thu, 05 Feb 2015 05:16:26 +0000 [Member] c7991@https://www.insidesql.org/blogs/ Thanks for writing this, it's the most comprehensive overview of sysadmin role vs CONTROL SERVER I've seen; if only it wasn't all so much bad news! This is the kind of thing Microsoft themselves should have documented more fully. It's really astounding that the problems with system procedures haven't been fixed even in SQL Server 2014. https://www.insidesql.org/blogs/andreaswolter/2013/08/control-server-vs-sysadmin-sa-permissions-privilege-escalation-caveats#c7991 andreaswolter [Member] in response to: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats Thu, 24 Oct 2013 22:58:22 +0000 [Member] c3004@https://www.insidesql.org/blogs/ And let's exclude the possibility of injecting a SQL Agent Job-step that is running under sa or other code injecting methods for now ;-) That's more like a general problem. It is a point though of course: the job is not done by just denying. Rather should the server be secured from ground-up. Mistakes can be exploited easily, if you know what you are looking for. :) https://www.insidesql.org/blogs/andreaswolter/2013/08/control-server-vs-sysadmin-sa-permissions-privilege-escalation-caveats#c3004 andreaswolter [Member] in response to: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats Thu, 24 Oct 2013 22:33:52 +0000 [Member] c3003@https://www.insidesql.org/blogs/ Hi David, Thank you for your feedback. I can completely understand the "bad feeling" that many have with working based on denies. But when the permission hierarchy is clear, it actually does make sense once in a while, to sort of trim down permissions. Whether this is valid in this case.. let’s see. How exactly do you want the undo the deny? He can’t do it onto himself, and trying to do grant it to the role does not help. Your point is very important though: I should maybe stress more, that I did <strong>DENY the Login</strong> and <strong>not just the Role</strong> on purpose. That’s actually the point. Denying the role can easily be undone. Totally true. So thank you very much for making me think about this point again. I am very open to hear any of the other 99 ideas :-) Andreas DENY the Login and not just the Role on purpose. That’s actually the point. Denying the role can easily be undone. Totally true. So thank you very much for making me think about this point again. I am very open to hear any of the other 99 ideas :-) Andreas]]> https://www.insidesql.org/blogs/andreaswolter/2013/08/control-server-vs-sysadmin-sa-permissions-privilege-escalation-caveats#c3003 David Browne [Visitor] in response to: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats Thu, 24 Oct 2013 21:57:38 +0000 David Browne [Visitor] c3002@https://www.insidesql.org/blogs/ There are probably 100 ways for a login with CONTROL SERVER to become a sysadmin. You shouldn't try to block them, because you'll never get an exhaustive list and the security is illusory. For instance a login with CONTROL SERVER can undo the DENY that block impersonation. grant impersonate on login::sa to role_dba https://www.insidesql.org/blogs/andreaswolter/2013/08/control-server-vs-sysadmin-sa-permissions-privilege-escalation-caveats#c3002 andreaswolter [Member] in response to: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats Tue, 15 Oct 2013 20:21:40 +0000 [Member] c2547@https://www.insidesql.org/blogs/ Hi Alvaro Thanks for adding this important point. I would not consider this a “hole” (It is quite logically and just follows the permission hierarchy) but it is important to keep in mind: A Login != a User And Control Server => Control “Any” Database Using “hand-crafted” permissions is in fact the most secure option. But be aware: Even if you specifically “DENY IMPERSONATE On USER::xyz”, depending on the schema the data is in, the CONTROL-SERVER-Login can still get the data easily if he is able to exploit an owner-ship chain (!). And this is as easy as it could be… So to really make sure he cannot get certain data, I highly recommend to “DENY CONNECT ON DATABASE::[DB_ContainingTheData] To ControlServerLogin” Thanks for your elaborate comment and scenario, Andreas Control “Any” Database Using “hand-crafted” permissions is in fact the most secure option. But be aware: Even if you specifically “DENY IMPERSONATE On USER::xyz”, depending on the schema the data is in, the CONTROL-SERVER-Login can still get the data easily if he is able to exploit an owner-ship chain (!). And this is as easy as it could be… So to really make sure he cannot get certain data, I highly recommend to “DENY CONNECT ON DATABASE::[DB_ContainingTheData] To ControlServerLogin” Thanks for your elaborate comment and scenario, Andreas]]> https://www.insidesql.org/blogs/andreaswolter/2013/08/control-server-vs-sysadmin-sa-permissions-privilege-escalation-caveats#c2547 Alvaro Fdez [Visitor] in response to: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats Mon, 14 Oct 2013 16:08:02 +0000 Alvaro Fdez [Visitor] c2513@https://www.insidesql.org/blogs/ Hi Andreas, Adding to the scenarios in your article. Let's say the DBA team add a CONTROL SERVER's account as a user in a user database - for the sake of later forbid him access to user data in that database (ie, DENY CONTROL ON SCHEMA, or DENY CONTROL on a certain table, etc.). The team also DENY ALTER ANY LOGIN and ALTER USER for that CONTROL_SERVER login/user as well. In that database, let's say there is a "schemauser" user (and server login). So we DENY IMPERSONATE ON LOGIN::schemauser to the CONTROL_SERVER login and, in effect, given the set of privileges denied above, and existing a mapping between the CONTROL SERVER login and a user for that login in that database, the CONTROL_SERVER account won't be able to access user data/schemas. But unless the DBA team also explicitly DENY IMPERSONATE ON USER::schemauser TO CONTROL_SERVER_user , he will be able to do a EXECUTE AS USER=schemauser to access the data as the schemauser. So in my case, creating a CONTROL SERVER login and at the same time, trying to prevent him access to user data, will force the DBA team to: 1- Deny any kind of ALTER LOGIN or ALTER USER (or APPLICATION ROLE) privileges at a minumum. 2-Always pay attention to newly created logins/users in that database, because the CONTROL SERVER account will just keep track of a new user and impersonate him as described -- unless the DBA team, on each login/user creation, automatically repeats the DENY IMPERSONATE ON LOGIN/USER::the_newly_created_login_or_user ... For a project involving separation of duties (but that required to forbid access to user data) I frankly ended not creating a such "marvellous" CONTROL SERVER account, but instead granting individual privileges out of the hundreds of them (project was over a SQL2008 R2 unfortunately,couldn't use SQL2012 server roles) Regards, Alvaro Fdez https://www.insidesql.org/blogs/andreaswolter/2013/08/control-server-vs-sysadmin-sa-permissions-privilege-escalation-caveats#c2513