Hi, I need to find out list of all Priviliged Users(Windows) that have access to the SQL Server Instance. The Priviliged Users. e,g. A DBA's Windows Login has all the access on the instance. I need this is for Auditing and Compliance. Only the DBA Ids are suppose to enjoy a privilege access to all the DBAs. I tried to get help from sp_helplogins. However I need some more and exact query to get the list of users I need.
I got answer to it. select a.name as LoginName, a.type_desc as LoginType, a.default_database_name as DefaultDBName, 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 '##%'