Identifying Role Permissions & Who Can Get You Fired

There are individuals in your company that have permission to get you fired. They’re not necessarily managers, directors, or anyone in a corner office on the top floor of headquarters. They’re sitting next to you. They’re probably in reception areas, clerical offices, cubicles to the left and right. They may be sitting in your chair. 

Related: Identifying SQL Server Failover Cluster Shared Drives, Nodes, and Current Ownership

These people have permissions to get you fired because they have permissions to do things such as dropping databases, overwriting backup files, creating databases with 2TB log files on a 500GB log volume, and who knows what else. They have server-level role membership.

Who Are These People?

When looking at role permissions, we need to discuss two system views in particular:

sys.[server_principals]

sys.[server_role_members]

These two system views provide insight into all the logins provisioned on your SQL instance, as well as any server-level permissions they may have. The bulk of the heavy lifting is done by the sys.[server_principals] system view. It provides information about every SQL login, Windows login or group, role and certificate on the instance as the following queries show.

First of all, let's look at all the various types of entities identified in the sys.[server_principals] system view:

--SERVER PRINCIPAL TYPESSELECT DISTINCT type, type_desc FROM sys.[server_principals]ORDER BY type;

If we drill in further to look at details of the user identities in this view, we'll see that we're able to see login identifies for a variety of login types (SQL logins, AD logins, or AD groups):

--LOGIN LISTSELECT principal_id, name, type, type_desc FROM sys.server_principals WHERE type IN ('U', 'S', 'G');

Furthermore, we also see that we have entries that include the server roles we're looking to identify membership in:

--SERVER ROLES LISTSELECT principal_id, name, type, type_desc FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE' ORDER BY name;

Meanwhile, the sys.[server_role_members] system view exists to store the assignment of each login or group to a particular role or roles:

--CONTENTS OF sys.[server_role_members]SELECT * FROM sys.[server_role_members]

Based upon the contents of both of these views, does anyone else notice the potential for a recursive relationship? I do . . .

Identifying the People Who Can Get You Fired

The role_pricipal_id column refers to the row in the sys.[server_pricipals] system view that identifies the SERVER_ROLE entry, whereas the membership_principal_id column is the login or group with membership is that SERVER_ROLE. By puting this relationship at the core of the following query, we get something that looks like this:

SELECT SR.[name] AS role__name, [server_principals].name AS login__name FROM sys.[server_role_members] INNER JOIN sys.[server_principals] ON [server_role_members].[member_principal_id] = [server_principals].[principal_id]INNER JOIN (SELECT [principal_id], nameFROM sys.[server_principals]WHERE [type_desc] = 'SERVER_ROLE') AS SR ON [server_role_members].[role_principal_id] = SR.[principal_id]ORDER BY SR.name, [server_principals].[name];

Monitor When Permissions are Altered & What Permissions Need Revoked

Depending on your environment, I recommend that you either periodically review the output from the previous query, or better yet, set up an audit against the sys.[server_role_members] view to monitor when these permissions are altered and to ensure only those individuals who should have these levels of permissions have them assigned. The first step is obviously an intitial review and subsequent consultation with these individuals and their upline management to determine what permissions need to be revoked and what the applicable replacement rights should be. This process is one of the first audit points I look at when working with a new client as it truly is low-hanging fruit that can prevent plenty of headaches later on if left untended.

Related: Policing the Use of SQL Server Management Studio