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

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.

Requirements:

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
GO
 
SELECT  p.name AS [loginname] ,
        p.type_desc ,
        p.is_disabled,
        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
WHERE   p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
        -- Logins that are not process logins
        AND p.name NOT LIKE '##%'
        and p.name 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 p.name 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.

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:  http://www.sqlservercentral.com/articles/T-SQL/99398/

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.

Björn Peters

Björn continues to work from Mexico as a Senior Consultant – Microsoft Data Platform and Cloud for Kramer&Crew in Cologne. He also remains loyal to the community from his new home, he is involved in Data Saturdays or in various forums. Besides the topics around SQL Server, Powershell and Azure SQL, he is interested in science fiction, baking 😉 and cycling.