Checking for users with sysadmin privileges

Category : Scripts User Rating : 4.5 Stars      Views : 4.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.
One thing I like to do occasionally, or when “inheriting” a new SQL server, is to check which users have sysadmin privileges. As these users have access to perform any activity in SQL Server it’s important to keep the number of these users to a minimum especially on production systems.

The following script will list all sysadmins on a server, in name order :
SELECT   name,type_desc,is_disabled
FROM     master.sys.server_principals
WHERE    IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY name


Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/Checking for users with sysadmin privileges.aspx

Keywords

SQL,scripts,sysadmin,security,audit


Comments
Post by Sudhakar on Thu 22 Oct 2015 22:06. Report Inappropriate Post

Thanks for the script. It helped to get information of accounts.
Post by Vutisani on Mon 08 Feb 2016 10:16. Report Inappropriate Post

wow good one.
Post by anon on Fri 27 May 2016 10:23. Report Inappropriate Post

thanks!!
Post by Biggie on Tue 31 Jan 2017 07:54. Report Inappropriate Post
Website : http://www.badboy.com
Thanx
Post by pr on Wed 01 Feb 2017 17:41. Report Inappropriate Post

Hi,
how can i pull a single report, that who can have sysadmin access on the different sql servers(Example: from 30 sql servers we want to know who are the sysadmins in that server, in a single report).
can any one help me on this.
Post by SQLMatters on Wed 01 Feb 2017 21:39. Report Inappropriate Post

If you wanted to do this from within Management Studio, then you can run the query against multiple SQL Servers in one query window by using SQLCMD - more details here : http://www.sqlmatters.com/Articles/Changing%20the%20SQL%20Server%20connection%20within%20an%20SSMS%20Query%20Windows%20using%20SQLCMD%20Mode.aspx

Alternatively you can use either Registered Servers or Central Management Servers with Management Studio to run queries against multiple SQL Servers simultaneously.

If the report doesn't have to be done within Management Studio, I'd suggest a reporting tool such as SSRS, which can connect to multiple data sources.

Hope that's of use.
Post by minnu on Mon 28 Dec 2020 08:08. Report Inappropriate Post
Website : https://www.sqlmatters.com/Articles/Checking%20for
how to get the list of logins having sysadmin permissions in 300 servers using SSIS package

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
 Not displayed Optional, but displayed if entered