I List Users with System Admin membership before agreeing on the permissions for a health check. This query inspects visible principals.
SELECT name, type_desc, is_disabled, create_date
FROM sys.server_principals
WHERE IS_SRVROLEMEMBER(N'sysadmin', name) = 1
ORDER BY name;
SELECT IS_SRVROLEMEMBER(N'sysadmin') AS CurrentLoginIsSysadmin;During my guided consultation, the customer’s DBA operates their system. I don’t ask them to send usernames or passwords. Some tasks require elevated rights. Many diagnostic queries can use specific state or performance-state permissions instead.
The first query lists memberships visible to the caller. Windows groups, directory resolution and metadata visibility affect the result. A login receiving group rights may not appear separately. IS_SRVROLEMEMBER can return NULL when it can’t determine membership.
Use the minimum permissions required for the agreed work. Choose a separately authorized administrative account when a server-level change requires it. This example inspects access without granting sysadmin.
Related reading
A sysadmin inventory is not a permission grant, it is evidence to review for the intended task.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
5 Comments. Leave new
I found most of this script somewhere so I can’t take credit for it. I tweaked it a bit to include logins with CONTROL_SERVER as well since we sometimes grant that to developers but want to limit sysadmin:
USE master
GO
SELECT DISTINCT p.name AS [loginname] ,
p.type ,
p.type_desc ,
p.is_disabled,
s.sysadmin,
CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id
WHERE p.type_desc IN (‘SQL_LOGIN’, ‘WINDOWS_LOGIN’, ‘WINDOWS_GROUP’)
— Logins that are not process logins
AND p.name NOT LIKE ‘##%’
— Logins that are sysadmins or have GRANT CONTROL SERVER
AND (s.sysadmin = 1 OR sp.permission_name = ‘CONTROL SERVER’)
ORDER BY p.name
GO
Very nice script.
Hi Pinal,
Yes, I’m sa and also the only one.
Great!
An issue with your query is that if will show sysadmin role for an individual account that has permissions through membership on an AD group with sysadmin privileges, even if the individual account has sysadmin disabled. In order to avoid this issue; we simply use “select name, sysadmin from syslogins where sysadmin = 1;”