Finding Sysadmins on a SQL Instance.

In this blog we shall see, how to find the users who have sysadmin rights on your SQL Server.

It is very important to know who are the users who have sysadmin rights, because sysadmin is the highest level of security permission on a SQL Server. A user having this permission can do everything on the SQL Instance, such as Create / Delete / Disable other users including other sysadmins. Create / Drop databases, Start / Stop SQL Server and the list can go on..

Using a T-SQL

We can query the catalog view syslogins and find those rows which have a value of 1 in sysadmin column. The query used below filters to find those users who have been granted access to the SQL Server.  

WHERE sysadmin = 1 and hasaccess = 1

The result when run on one of my personal SQL Server looks as shown below..

I selected an additional column isntname, in order to see what type of login is that user. When isntname = 0, it indicates the login is a SQL login and 1 indicates a windows based login.

Using SSMS

You can also find out using Management Studio. When connected to the SQL Server,  expand Security node, and expand Server Roles. From the list of Server Roles right click sysadmin and choose Properties from the popup Menu item.

You will see the properties dialog box for sysadmin role as shown below..

Do you like this site? Like our FB page @\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

11 thoughts on “Finding Sysadmins on a SQL Instance.

  1. I am guessing this is in SQL Server 2000 Enterprise Manager..

    If so, Open Enterprise Manager, In the left hand side, you should see Microsoft SQL Servers, right click on it and from the pop-up menu choose New SQL Server Group. In the next dialog box, type the Group Name and then choose if it is a Top level group (default) or a sub group, if you choose sub group, you will have to choose under which parent group is this new group going to be added..

    Any other questions please feel free to like LearnSQLWithBru on face book page, so that you can ask questions directly on that page..

  2. When security best practices are followed, users who need access to something get their access through group membership (usually AD groups, but may be local machine groups).

    If the group MYDOMAINSQL_ADMINS is a member of the sysadmin SQL Server server role, you will only know that the group is a sysadmin. How do you get the individual members of the group MYDOMAINSQL_ADMINS?

  3. Thanks Bru. Would you have an automated or programmatic way? Perhaps using a linked server to AD, using the ADsDSOObject provider? Joining back to [master].[sys].[server_principals]?

  4. Hi Bro,Can you please explain what is update statistics and what is the use of this in sql sever,what will happen when we run the sp_update stats.

    I need one more help from you,can you please explain different Isolation levels,what is the relation b/w locks and Isolation levels,what is use.Can you please explain fro me..

  5. Hi,

    I shall make a blog on the requested topics and let you know here in this comments.. May be next week should be possible for me to post the blogs..

    Bru Medishetty

  6. Hi, have you tried running the same SQL code on SQL Server 2008, I tried running the same SQL code on SQL Server 2008 R2 and runs fine and I am assuming it would be fine on 2008 too..

Leave a Reply