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 GRANT
s 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
.