my daily business with MS SQL Servern

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.

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:

loginname

type_desc

is_disabled

IsSysAdmin

IsServerAdmin

IsSecurityAdmin

IsProcessAdmin

IsSetupAdmin

IsBulkAdmin

IsDiskAdmin

IsDBCreator

created

update

sa

SQL_LOGIN

0

1

0

0

0

0

0

0

0

04.08.2003

10.08.2013

NT SERVICE\SQLWriter

WINDOWS_LOGIN

0

1

0

0

0

0

0

0

0

10.09.2012

10.09.2012

NT SERVICE\Winmgmt

WINDOWS_LOGIN

0

1

0

0

0

0

0

0

0

10.09.2012

10.09.2012

NT SERVICE\MSSQLSERVER

WINDOWS_LOGIN

0

1

0

0

0

0

0

0

0

10.09.2012

10.09.2012

NT SERVICE\ClusSvc

WINDOWS_LOGIN

0

0

0

0

0

0

0

0

0

10.09.2012

10.09.2012

NT AUTHORITY\SYSTEM

WINDOWS_LOGIN

0

0

0

0

0

0

0

0

0

10.09.2012

10.09.2012

NT SERVICE\SQLSERVERAGENT

WINDOWS_LOGIN

0

1

0

0

0

0

0

0

0

10.09.2012

10.09.2012

EP\sql_serv

WINDOWS_LOGIN

0

1

0

0

0

0

0

0

0

10.09.2012

10.09.2012

Attunity

SQL_LOGIN

0

0

0

0

0

0

0

0

0

10/16/2012

12/23/2012

BackupHist

SQL_LOGIN

0

0

0

0

0

0

0

0

0

11/15/2013

11/15/2013

domain\accountname

WINDOWS_LOGIN

0

1

0

0

0

0

0

0

0

11/15/2013

11/15/2013

domain\groupname

WINDOWS_GROUP

0

1

0

0

0

0

0

0

0

11/15/2013

11/15/2013

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.

Björn works as a database administrator and Head of Competence for MS SQL and mySQL in Hamburg (Germany). He regularly participates in the PASS regional group meetings, events of the PASS such as SQLSaturday and SQLGrillen and he organizes the Azure Meetup group in Hamburg. He is interested in topics like SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.

SQL from Hamburg

I got in contact with MS SQL databases for the first time in 2000 and took care of these database systems for around 7 years. Since 2007, I have been working as a database administrator and remains responsible for a large number of SQL servers of all sizes of companies from different technology sectors.
Actually, I do not have any certificates, my knowledge and knowledge about the SQL Server purely from the daily business, the reading/tracking of numerous forums/blogs.
I' m not specialized in any topic really, but put my focus on the performance analysis.
Since the end of 2016, I'm the main organizer of the Azure Meetup Hamburg and since April 2017 I'm a Cloud and Datacenter Management MVP.