How to revoke SA permissions that seem to be "stuck" to a login?
Bogopo last edited by
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
- 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.
There are a few different ways that a user gets permissions in SQL Server. In addition to role membership (like the
sysadminfixed 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
sysadminin 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_logininfosystem stored procedure:
EXEC xp_logininfo @acctname = 'MyDomain\amtwo', @option = 'ALL';
The output will look something like this. Notice the first line, which shows my
adminprivileges via the "permission path" (read: group membership) in the
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
sysadminpermissions from the
amtwologin, I would need to remove it from the
SQLSERVER_SYSADMINgroup on the active directory side. (ie, there's nothing I can do about it via the SQL Server configuration & permissions.)
You might just have
There is a permission called
CONTROL SERVERwhich is almost the same as
sysadmin, but with some differences.
CONTROL SERVERwill obey having a
DENYenforced for more granular permissions (
sysadminoverrides everything, including
DENYpermissions). 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
sysadminmembership, and the
The best way to check this is with a script to query all the permissions. This query is from https://github.com/amtwo/dba-database/blob/production/views/dbo.ServerLoginPermissions.sql in https://am2.co/dbadb , 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
-- 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 SERVERthat you want to remove, you would do so by using syntax like
REVOKE CONTROL SERVER FROM 'MyDomain\Login';. Note that the
PermissionSqlcolumn will return a
DENYstatement, and you could simply modify that sql text to change the