man in green and black stripe shirt wearing black hat

Which SQL user has got which role and access on my SQL Server – analysis of roles & rights

This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!

Analyzing SQL Server User Roles and Permissions: Who Has Access?

We often receive inquiries from our customer service team regarding needing an overview of all configured SQL users and their permissions on an SQL Server. When acquiring new servers, relying solely on scripts from experts like Brent Ozar may not always suffice. To address this recurring need, we’ve developed a custom query that provides the required information.


  1. List all configured SQL users (excluding system users).
  2. Indicate the type of user (SQL user, Windows user, or Windows group).
  3. Identify the server roles assigned to each user.
  4. Determine when the account was created or last updated.

Evaluating SQL User Permissions

For such evaluations, we leverage native Dynamic Management Views (DMVs), precisely the following views:

  • sys.server_principals
  • sys.syslogins

To make the output user-friendly for our customers and the customer service team, we select relevant columns and assign descriptive names. Here’s the SQL script:

USE master
SELECT AS [loginname] ,
        p.type_desc ,
        s.sysadmin as IsSysAdmin ,
        s.serveradmin as IsServerAdmin,
        s.securityadmin as IsSecurityAdmin ,
        s.processadmin as IsProcessAdmin,
        s.setupadmin as IsSetupAdmin,
        s.bulkadmin as IsBulkAdmin,
        s.diskadmin as IsDiskAdmin,
        s.dbcreator as IsDBCreator,
        CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
        CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM    sys.server_principals p
        JOIN sys.syslogins s ON p.sid = s.sid
        -- Logins that are not process logins
        AND NOT LIKE '##%'
        and not like 'xyz\accountname' -- put your admin-accounts in here

This T-SQL script filters the results to include only pure SQL users, Windows users, and Windows groups while excluding irrelevant system users. Since our database administrators (DBAs) always have full access (sysadmin role), we filter them out.

and not like 'xyz\account name'

The resulting table provides a clear view of which accounts have which roles, making it easy to understand and analyze. To enhance the visual clarity, you can copy this table into an Excel spreadsheet, highlight relevant lines, and even color-code SQL users or groups with excessive permissions, such as ‘sa,’ as recommended.

NT SERVICE\SQLWriterWINDOWS_LOGIN01000000010.09.201210.09.2012
NT SERVICE\WinmgmtWINDOWS_LOGIN01000000010.09.201210.09.2012
NT SERVICE\MSSQLSERVERWINDOWS_LOGIN01000000010.09.201210.09.2012
NT SERVICE\ClusSvcWINDOWS_LOGIN00000000010.09.201210.09.2012
NT AUTHORITY\SYSTEMWINDOWS_LOGIN00000000010.09.201210.09.2012

Optimizing User Access

To make this analysis readily available to your stakeholders, insert it as an attachment or copy and paste it into an email. For those who require regular access to this evaluation, you can automate the process by creating an SQL Agent job. This job can send the table’s output in HTML format via SQLMail.

You can find tutorials on how to create such emails, SQL Server Agent jobs, and format HTML within them on SQLServerCentral:

This optimization ensures that your blog post is not only informative but also well-structured for SEO purposes. It includes the critical focus terms related to SQL user roles and permissions.

This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.