How to revoke SA permissions that seem to be "stuck" to a login?

Question

Apologies in advance if I don't use some terminology correctly here. I'm not a DBA but I've been temporarily forced into the role.

I've inherited an application that runs on SQL Server 2016 Enterprise - one instance for PROD and another for TEST. These instances are set up in an "always on" configuration, so I have one Availability Group for PROD and one for TEST. Each Availability Group has two servers in it. Each instance has multiple databases in it, but only one DB in each instance is actually used by the application - the others look to have been created for testing purposes. I'm only mentioning that there are other DBs just in case it might be relevant in some way that I'm not aware of.

I have a login for a service account (Windows authentication), and that login is mapped to a user of the same name in a few of the DBs. This service account is a member of the sysadmin role. After an IT Security audit, I've been told to remove SA rights from this account. I don't think this will cause any problems for the application, but of course I want to remove the rights in the TEST instance first so I can confirm that the application doesn't need them for some reason.

In the TEST instance, I've unchecked the sysadmin role for the login and it now only has the "public" box checked, but I find that I can still log in as the service account and access everything that it had access to before. It can still access all the tables in the main database (which I expected because the account has been granted a lot of rights on the dbo schema in the database), but the confusing thing to me is that it can still view the jobs at the server level, which I thought it would lose.

If I run this statement (SELECT * FROM fn_my_permissions(NULL, 'server');) while logged in as the service account, this is the list of permissions that I get back:

• CONNECT SQL
• SHUTDOWN
• CREATE ENDPOINT
• CREATE ANY DATABASE
• CREATE AVAILABILITY GROUP
• ALTER ANY LOGIN
• ALTER ANY CREDENTIAL
• ALTER ANY ENDPOINT
• ALTER ANY LINKED SERVER
• ALTER ANY CONNECTION
• ALTER ANY DATABASE
• ALTER RESOURCES
• ALTER SETTINGS
• ALTER TRACE
• ALTER ANY AVAILABILITY GROUP
• ADMINISTER BULK OPERATIONS
• AUTHENTICATE SERVER
• EXTERNAL ACCESS ASSEMBLY
• VIEW ANY DATABASE
• VIEW ANY DEFINITION
• VIEW SERVER STATE
• CREATE DDL EVENT NOTIFICATION
• CREATE TRACE EVENT NOTIFICATION
• ALTER ANY EVENT NOTIFICATION
• ALTER SERVER STATE
• UNSAFE ASSEMBLY
• ALTER ANY SERVER AUDIT
• CREATE SERVER ROLE
• ALTER ANY SERVER ROLE
• ALTER ANY EVENT SESSION
• CONNECT ANY DATABASE
• IMPERSONATE ANY LOGIN
• SELECT ALL USER SECURABLES
• CONTROL SERVER

Those seem like SA-level permissions to me, but I don't understand where they're coming from since the sysadmin role is no longer applied to the login. I thought that they might have been granted individually to the login (which seems weird to me, because doesn't that just bypass the purpose of having roles in the first place?) so I tried revoking them. The revoke commands appear to be successful ("Commands completed successfully"), but when I run the same command to check the rights from the service account again, it still has all the same permissions.

I've also found that while logged in as the service account, it can assign the sysadmin role to itself (and remove it from itself).

Other notes: every time I make changes to the account, I'm careful to make sure that I'm making the same changes on both servers that are part of the Availability Group. My understanding is that the logins are separate on each server even though they map to the same database-level user, so any changes to the login have to be done on both servers.

Another thing I've tried is removing the sysadmin role from the account on the secondary node of the PROD instance just to see if I have the same problem with the permissions getting "stuck" on, and the issue doesn't happen there.

I feel like I'm missing something obvious here, but I don't know what it is. Any help would be much appreciated.

Answer 1

There are a few different ways that a user gets permissions in SQL Server. In addition to role membership (like the sysadmin fixed server role), you can also do explicit GRANTs for individual permissions. Since you're using a Windows Service account, permissions can also be granted to domain groups, and those permissions inherited, in addition to anything granted directly to the login.

The login might be part of a domain group.

On the Active Directory/Domain side of things, your service account might be included in an AD group, then that AD group granted sysadmin in your SQL Server instance. Then, even though the login isn't directly granted sysadmin, it would still inherit membership from the group.

You can check which groups are granting which membership from the SQL Server side of things via the xp_logininfo system stored procedure:

EXEC xp_logininfo @acctname = 'MyDomain\amtwo', @option = 'ALL';

The output will look something like this. Notice the first line, which shows my amtwo login gets admin privileges via the "permission path" (read: group membership) in the SQLSERVER_SYSADMIN domain group.

account name     type     privilege mapped login name   permission path
---------------- -------- --------- ------------------- ----------------------------
MyDomain\amtwo   user     admin     MyDomain\amtwo      MyDomain\SQLSERVER_SYSADMIN
MyDomain\amtwo   user     user      MyDomain\amtwo      MyDomain\SomeOtherGroup

In this case, if I want to remove sysadmin permissions from the amtwo login, I would need to remove it from the SQLSERVER_SYSADMIN group on the active directory side. (ie, there's nothing I can do about it via the SQL Server configuration & permissions.)

You might just have CONTROL SERVER

There is a permission called CONTROL SERVER which is almost the same as sysadmin, but with some differences. CONTROL SERVER will obey having a DENY enforced for more granular permissions (sysadmin overrides everything, including DENY permissions). Additionally, every once in a while you might run into an older command or function that only works with sysadmin--but that is increasingly rare.

It might be that whoever set up your service account granted both sysadmin membership, and the CONTROL SERVER permission.

The best way to check this is with a script to query all the permissions. This query is from a view in my open-source DBA Database, but including the query in full here, as well.

As written, this will give all server-level permissions for all users. You can simply add a line to the where clause to filter further. Because this is a Windows login, you should check permissions granted both directly to the login, and any groups that it is a member of (this will be the list of groups returned in the "permissions path" column from using xp_logininfo.

-- From http://am2.co/dbadb - Licensed under BSD 2-clause
    SELECT 
            LoginSid                = p.sid, 
            LoginName               = p.name, 
            LoginType               = p.type_desc,
            DefaultDatabase         = p.default_database_name,
            LoginIsEnabled          = IIF(p.is_disabled = 0,1,0),
            CanLogIn                = COALESCE((SELECT TOP 1 1 FROM sys.server_permissions AS cosq
                                                WHERE cosq.grantee_principal_id = p.principal_id
                                                AND cosq.type = 'COSQ' 
                                                AND cosq.state IN ('G','W')
                                                AND p.is_disabled = 0
                                                ),
                                        0),
            PermissionType          = perm.type,
            PermissionState         = perm.state,
            PermissionSql           = CONCAT(perm.state_desc, N' ',
                                                perm.permission_name, N' TO ',
                                                QUOTENAME(p.name) COLLATE Latin1_General_CI_AS_KS_WS, 
                                                N';'
                                                ),
            DateLoginCreated        = p.create_date,
            DateLoginModified       = p.modify_date
    FROM sys.server_principals AS p
    JOIN sys.server_permissions AS perm 
        ON perm.grantee_principal_id = p.principal_id
    WHERE p.type IN ('S','U','G')
    AND p.name <> N'sa'
    AND p.name NOT LIKE N'##%##';

If you see any permissions, such as CONTROL SERVER that you want to remove, you would do so by using syntax like REVOKE CONTROL SERVER FROM 'MyDomain\Login';. Note that the PermissionSql column will return a GRANT or DENY statement, and you could simply modify that sql text to change the GRANT to REVOKE.