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

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 CREDE

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.

StackExchange Database Administrators Q#307824, answer score: 5