question

abhishek120684 avatar image
abhishek120684 asked

Query to Find Privileged Users in a MS SQL Server

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.
sql-serverusers
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

abhishek120684 avatar image
abhishek120684 answered
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 '##%'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

eghetto avatar image
eghetto answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.