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

Once again received an inquiry from our customer service staff, we will once again provide an evaluation …

There are situations in which we or the customer like an overview of all configured SQL users and their permissions on a SQL Server or wishes, we at the acquisition of new servers not only on the scripts from Brent Ozar draw and is the above request frequently, we have built us a corresponding statement.

Requirement:

• all configured SQL users should be listed (not system users)
• Allocation should be apparent (SQL user, Windows user or Windows group)
• Which server role was assigned to the respective users?
• When the account has been created or when was the lastly updated?

For such evaluations, the native DMVs are wonderful, in this case, there are the views:

• sys.server_principals
• sys.syslogins

The selection of useful displayable columns in the evaluation – so that the customer and the customer service is able to “understood” – and a speaking name of any columns results in the following script. (I haven’t reinvented the wheel, just improved for me usage 😉 )

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

The TSQL statement filters the result set to pure SQL users and Windows users, and Windows groups (both local and domain), by means of the restriction ‘##%’ We distinguish also relevant (for this evaluation) system user.
As all recipients of this evaluation are aware that we as DBAs always have full access (sysadmin role), we filtered out these users (or user groups)

and p.name not like 'xyz\account name'

As a result, we obtain the following table showing us clearly which account has which role:

Now if you copy this table into an Excel spreadsheet, you can highlight the relevant lines visually better, so that the recipient will receive a quick and easy overview.
In accordance with our Security Baselines a customer-user may have a maximum server role “dbcreator“, therefore we could highlight the SQL user or group color (red), if they have too many rights or to highlight that the SQL user “sa” is not disabled as recommended.

Now you can insert the analysis either simply as an attachment or via copy / paste into an email and provide the respective receivers available.

Who wants to have this evaluation, if necessary at regular intervals, may create this as SQL Agent job, send output the table as HTML construct and the HTML output send per SQLMail.

Tuts on how to create such mails or SQL Server Agent jobs and formating HTML within can be found on SQLServerCentral: http://www.sqlservercentral.com/articles/T-SQL/99398/

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.

Amazon.com Empfehlungen

Damit ich auch meine Kosten für den Blog ein wenig senken kann, verwende ich auf diese Seite das Amazon.com Affiliate Programm, so bekomme ich - falls ihr ein Produkt über meinen Link kauft, eine kleine Provision (ohne zusätzliche Kosten für euch!).

Welcome Back to the United States

(62)

(as of 3. October 2023 20:01 GMT +02:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

$10 Xbox Gift Card [Digital Code]

(176129)

$10.00 (as of 3. October 2023 20:01 GMT +02:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Going Infinite: The Rise and Fall of a New Tycoon

(5)

10% Off $30.00 $27.00 (as of 3. October 2023 20:01 GMT +02:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

WYZE Cam Pan v3 Indoor/Outdoor IP65-Rated 1080p Pan/Tilt/Zoom Wi-Fi Smart Home Security Camera with Motion Tracking for Baby & Pet, Color Night Vision, 2-Way Audio, Works with Alexa & Google, Black

$39.98 (as of 3. October 2023 20:01 GMT +02:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Building a Holy Nation: The Role of Security

(1)

$9.99 (as of 3. October 2023 20:01 GMT +02:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)