One of the pre-requesting of Comprehensive Database Performance Health Check is to check if user has necessary system admin rights or not. It is almost impossible to help customer with SQL Server Performance if they do have a DBA who has access to system admin rights.
Please note that I NEVER ask for any username or password, during the consultation. My performance tuning technique is such where I will guide you step by step about how you can tune your SQL Server yourself. I teach you with every single click and also I teach you how to use all the scripts which I provide during consultancy. Everything is scientific and systematic. However, there is one requirement in this arrangement.
The DBA who is on the other side of the computer MUST have a system admin rights. If he doe snot have enough rights we will be able to do only limited exercise with regards to performance. Most of my diagnosis scripts only work with sysadmin rights.
Here is the quick script which you can run and list all the users with admin rights. If you find your username there, you know you are an admin.
SELECT name,type_desc,is_disabled, create_date
FROM master.sys.server_principals
WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY nameLet me know if you use any other script. I will be happy to publish that on blog with due credit to you.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
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;”