K
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 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 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.
