July 30, 2014 at 1:48 pm
Hi everyone
Hope someone can offer some advice. Been stuck on this one for a while.
Trying to use the TSQL below to get a listing of all server roles , excluding Public.
Basically want all server roles less Public.
Here is the code been trying to work.
Any help \ improvement appreciated .
SELECT @@servername as Server, a.name as LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,a.is_disabled as Status,
CASE WHEN b.sysadmin = 1 THEN 'sysadmin'
WHEN b.securityadmin=1 THEN 'securityadmin'
WHEN b.serveradmin=1 THEN 'serveradmin'
WHEN b.setupadmin=1 THEN 'setupadmin'
WHEN b.processadmin=1 THEN 'processadmin'
WHEN b.diskadmin=1 THEN 'diskadmin'
WHEN b.dbcreator=1 THEN 'dbcreator'
WHEN b.bulkadmin=1 THEN 'bulkadmin'
ELSE 'Public' END AS 'ServerRole'
FROM sys.server_principals a JOIN master..syslogins b ON a.sid=b.sid
WHERE a.type <> 'R'
AND (a.name NOT LIKE '##%')
AND (a.name <> 'PUBLIC')
and (a.name NOT LIKE 'NT SERVICE\MSSQL$INST1')
and (a.name NOT LIKE 'NT AUTHORITY\SYSTEM')
and (a.name NOT LIKE 'NT AUTHORITY\NETWORK SERVICE')
July 30, 2014 at 2:30 pm
It may not be exactly what you are requesting, but here is a start. Ask if you have any questions.
select
sp.name,
spa.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 spa
on (srm.role_principal_id = spa.principal_id)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply