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.  

SELECT NAME, isntname FROM SYSLOGINS
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 @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty

  • Bilalmuhammad007

    Dear Mr BRU can you please guide me how to add a server group on SQL 2000

  • http://www.LearnSQLWithBru.com Bru Medishetty

    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 https://www.facebook.com/LearnSQLWithBru, so that you can ask questions directly on that page..

  • Marc Jellinek

    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?

  • http://www.LearnSQLWithBru.com Bru Medishetty

    Open Active Directory Users and Computers then go to the properties of the group and click on the Members tab..

  • Marc Jellinek

    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]?

  • Kiran

    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..

  • http://www.LearnSQLWithBru.com Bru Medishetty

    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..

    Thanks,
    Bru Medishetty

  • Jdoe

    care to update this script for SQL Server 2008 and later???

  • http://www.LearnSQLWithBru.com Bru Medishetty

    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..

  • Richie

    How can I allow access to sa account or another one I want?