2

I have scoured the internet looking for something even close to what I'm wanting to do, and all I can find is how to get around security controls to be able to grant sysadmin to a login. (!!!) Here's the situation; I'm a veteran DBA at a new company that has never had a DBA before, so everyone and their brother's cousin has sysadmin. That's fine, I have a plan to clean it up and reduce the number of logins that have sysadmin permission. However, now that I've started taking that permission set away from some logins, "someone" (and that's the trouble, I don't know who) is granting sysadmin to other logins. I need a way to either report or (ideally) prevent logins that have sysadmin permission from granting sysadmin to other logins. Of course, this is not going to be easy, because a DDL trigger for "ALL SERVER" is not going to work, because from time to time, I will need to elevate certain logins to have sysadmin (upgrade/config change/new app/etc), so my login still has to have this ability. But, all other logins, with either sysadmin or securityadmin need to be blocked from granting sysadmin (and I suppose securityadmin) to any other login...newly created or existing.

Does anyone have any idea how to go about doing this? PBM may be asking too much, because I've never been able to do anything useful with it, outside of a training class. Perhaps using a DDL trigger(s) would get the job done (???).

Thanks in advance for any suggestions and/or advice.

P.S. I need to be able to log/prevent this for both sp_addsrvrolemember and ALTER SERVER ROLE, as we have 2008 R2 instances along with 2012+ instances.

Improve this question
5
  • Oh, and, I need to use sp_addsrvrolemember because we still have numerous 2008 R2 instances, that won't recongnize ALTER SERVER ROLE.
    – SQL_Hacker
    Feb 24, 2017 at 16:05
  • 1
    You should edit your question to add that.
    – Erik Darling
    Feb 24, 2017 at 16:07
  • 1
    Is blocking strictly required, or would logging that it was done, and who the grantor and grantee were, be adequate? (Not that I have an answer off that, just suggesting that another option might work as well)
    – RDFozz
    Feb 24, 2017 at 16:15
  • If a user is granted the sysadmin role, that user should be able to do anything on the SQL Server including deleting of audits that would have revealed the grant. Of course, if the permission is still being held then that should be visible.
    – RLF
    Feb 24, 2017 at 16:22
  • Well, it would suffice to log the operation so someone's hand can be slapped. But, I would really like the ability (granted, it's in the short-term) to prevent such an action. I would even be willing to add the "prevent" to each login that presently has sysadmin (except mine, of course). But...you are right, since it is a temporary situation, logging it and reporting it to the "powers that be" would work for now...
    – SQL_Hacker
    Feb 24, 2017 at 16:37

3 Answers 3

Reset to default
2

For whodunnit, there's a profiler trace event "Audit Add Login to Server Role" under security. Presumably it catches ALTER and not just the SPs specified, but that would need to be tested.

I'd suspect the guilty once caught would start assigning specific permissions instead so would trace that too.

Improve this answer
4
  • One hopes that the result of being caught would be the loss of the sysadmin privileges. Also, it's not clear how sophisticated the users in question are; I'd assume we're talking people too lazy to go through whatever mechanism they should be using to get this done, not industrial sabotage.
    – RDFozz
    Feb 24, 2017 at 17:39
  • You could also profile/extended event for the Alter & proc commands themselves just in case
    – Gareth Lyons
    Feb 24, 2017 at 18:01
  • I would really like to avoid Profiler. Isn't there a DMV/Exented Events equivalent that can be queried? What about the default trace? I read a post on SQLServerCentral that suggests the default trace captures any login changes. I tested it on a 2014 instance, and it does provide a boat-load of data, but it doesn't work on 2008 R2 instances (perhaps the default trace is disabled...haven't gotten that far yet).
    – SQL_Hacker
    Feb 24, 2017 at 18:12
  • select * from sys.configurations where name = 'default trace enabled' to check if it's enabled. The 'Audit Add Login to Server Role Event' does seem to be included in the default trace. Just tested and both ALTER SERVER and sp_addsrvrolemember will trigger that event. There's fields in the trace for target login & the login that executed the command.
    – Gareth Lyons
    Feb 24, 2017 at 21:05
0

Okay, so after answers posted and continued digging through the internet, I have found the solution...at least a temporary one. The answer is at: http://www.sqlservercentral.com/articles/Permissions/130850/. The author details how to specifically find changes to logins using the default trace on the instance. This is great...sort of. The first problem is finding out how "busy" the instance is, which will affect the rate of rollover files created by the default trace. For example, I have one instance where the oldest rollover file is from 2/15. On another instance, the oldest rollover file is from 2 AM today. (!!!) So, to successfully gather any changes to logins, using this method, it has to be determined how often to execute the query...and dump the results into a table for later review.

Thank you all for your suggestions and help in nailing down a workable solution.

Improve this answer
2
  • Or run your own extended event trace if rollovers are a concern...
    – Gareth Lyons
    Feb 24, 2017 at 21:07
  • I've never used extended events. I'm sure it would be a better way, but starting from zero would not help in this situation. I'll just have to deal with the rollover issue of the default trace until I can get all the sysadmin logins removed from the instances. Thank you for your suggestion...I'm sure it's a better way to go.
    – SQL_Hacker
    Feb 25, 2017 at 22:10
0

A sysadmin is an overriding authority on a SQL Server. You can try auditing to let you know what has happened, but a sysadmin can override that as well.

Server-Level Roles

In addition to the Fixed Server Roles, such as sysadmin, there are more granular permission settings, if they prove valuable to you.

See the graphic at Server Level Roles and Permissions. You may be able to satisfy some needs with 26 roles that may have lower permissions.

enter image description here

Improve this answer
2
  • Thank you for your response, but I need a way to at least log who (which login that presently has sysadmin) is granting sysadmin to other logins. Ideally, I would like to prevent such an action, but logging it would work for now. Perhaps I need to change the question to just logging "whodunnit" for now...
    – SQL_Hacker
    Feb 24, 2017 at 17:18
  • @SQL_Hacker - My comment on Server Level Roles and Permissions may allow you to grant certain rights that are available to a sysadmin but in a more granular way, reducing how many rights you actually grant.
    – RLF
    Feb 24, 2017 at 21:57

Your Answer

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

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