Though the script sounded simple to me, but I found that there are lots of incorrect scripts available on the internet. Here is the one of the script I found to find out the details of the system admin.
Please note that following script is not accurate and I do not encourage you to depend on it. You will find the correct script at the end of this article, please continue reading till the end of the blog post.
SELECT name,type_desc,is_disabled
FROM master.sys.server_principals
WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY nameAbove script looks very simple. When I ran on my machine I got below.
I realized that some entries are missing. So, I went ahead and checked the properties of SysAdmin role and found below
As we can see, I am not seeing all 6 members in the output. So, here is the query which I was able to write which would give accurate information.
SELECT 'Name' = sp.NAME
,sp.is_disabled AS [Is_disabled]
FROM sys.server_role_members rm
,sys.server_principals sp
WHERE rm.role_principal_id = SUSER_ID('Sysadmin')
AND rm.member_principal_id = sp.principal_id
Here is the output, which is accurate.
Do you have any similar interesting queries? Please share them with other readers via the comments section.
Reference: Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
Thank you very much for your script.
With respect, sir, would the ANSI-92 join syntax be better?
SELECT ‘Name’ = sp.NAME
,sp.is_disabled AS [Is_disabled]
FROM sys.server_role_members rm
inner join sys.server_principals sp on rm.member_principal_id = sp.principal_id
WHERE rm.role_principal_id = SUSER_ID(‘Sysadmin’)
Works for me. Thanks.
I can’t remember where I found the original part of this script, but I know I borrowed it from somewhere. I was also interested in finding all the sysadmins on a server, but also wanted to list any logins with the “Control Server” permission on boxes where server roles could be used to elevate rights without resorting to sysadmin. I tweaked it to include logins with Control Server, but return 0 for the sysadmin column.
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
This is nice!
prefer my script
SELECT sp.name AS ServerPrincipal,
sp.type_desc AS LoginType,
CASE sp.is_disabled
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS UserDisabled,
sp.create_date AS DateCreated,
sp.modify_date AS DateModified,
sp.default_database_name AS DefaultDB,
sp.default_language_name AS DefaultLang,
ISNULL(STUFF((SELECT ‘,’ +
CASE
WHEN ssp22.name = ‘sysadmin’ THEN ssp22.name + ‘ “Danger Will Robinson”‘
ELSE ssp22.name
END
FROM sys.server_principals ssp2
INNER JOIN sys.server_role_members ssrm2
ON ssp2.principal_id = ssrm2.member_principal_id
INNER JOIN sys.server_principals ssp22
ON ssrm2.role_principal_id = ssp22.principal_id
WHERE ssp2.principal_id = sp.principal_id
ORDER BY sp.name
FOR XML PATH(N”), TYPE).value(N’.[1]’, N’nvarchar(max)’), 1, 1, N”), ‘NoRolesHeld’) AS ListofServerRoles
FROM sys.server_principals sp
LEFT OUTER JOIN sys.server_permissions sper ON sp.principal_id = sper.grantee_principal_id
WHERE sp.type IN (‘S’,’G’,’U’) AND sp.name NOT LIKE ‘##%##’
GROUP BY sp.name, sp.type_desc, sp.is_disabled, sp.create_date, sp.modify_date,
sp.default_database_name, sp.default_language_name, sp.principal_id
ORDER BY ServerPrincipal
You also want to know who has explicit permission such as ‘CONTROL SERVER’, this is the same as sysadmin but doesnt show up as a role but a server level securable. For this i use
SET CONCAT_NULL_YIELDS_NULL OFF
select ‘Servername is ‘ + @@SERVERNAME + CHAR(10) + CHAR(13)
union all
select ‘Server principal ”’ + sp.name + ”’ holds SQL Server role ”’ + sp2.name + ””
from sys.server_principals sp
inner join sys.server_role_members srm on sp.principal_id = srm.member_principal_id
inner join sys.server_principals sp2 on srm.role_principal_id = sp2.principal_id
WHERE sp.principal_id > 4
UNION ALL
select ‘Server principal ”’ + sp.name + ”’ is a ‘ +
sp.type_desc collate Latin1_General_CI_AS_KS + ‘ created on ”’ +
CAST(sp.create_date AS VARCHAR(25)) + ”’, last modified on ”’ +
CAST(sp.modify_date AS VARCHAR(25)) + ”’, default database is [‘ +
sp.default_database_name + ‘], with ‘ +
CASE srp.state_desc
WHEN ‘GRANT’ THEN ‘Granted’
WHEN ‘DENY’ THEN ‘Denied’
END + ‘ permission ‘ + srp.class_desc + ‘ -> ‘ + srp.permission_name
from sys.server_principals sp
inner join sys.server_permissions srp on sp.principal_id = srp.grantee_principal_id
inner join sys.server_principals sp2 on srp.grantor_principal_id = sp2.principal_id
where
sp.principal_id > 256 AND sp.name NOT LIKE ‘NT[^][AS][UE][TR][HV]%\%’ AND sp.name NOT LIKE ‘##MS%##’
and srp.permission_name NOT IN (‘CONNECT SQL’, ‘CONNECT’)
Interesting. Thanks for sharing.
simple is better:
exec sp_helpsrvrolemember @srvrolename=’sysadmin’
go
:)
i got this error please help me
CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file ‘C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\FruitAndsubziSystem.mdf’. (.Net SqlClient Data Provider)
I need the same type of script to execute in azure pass sql server