SQL Server Principals

Prelude in SQL Minor

Back in late December of 2015, a challenge of sorts was issued by Tim Ford (twitter) to write a blog post each month on a SQL Server Basic. Some have hash-tagged this as #backtobasics. Here is the link to that challenge sent via tweet.

I did not officially accept the challenge. Was an official acceptance required? I don’t know. I do know that I think it is a good challenge and that I intend to participate in the challenge. I hope I can meet the requirements and keep the posts to “basics”. Let’s just call this first post in the challenge to be my official acceptance.

A fundamental component of SQL Server is the security layer. A principle player in security in SQL Server comes via principals. SQL Server principals come in more than flavor. (This is where a lot of confusion gets introduced.) The different types of principals are database and server. A database principal is also called a database user (sometimes just user). A server principal is also called a server login, or login for short.

Server and database principals are quite a bit different in function and come with different permission sets. The two are sometimes used, in reference, interchangeably, but that is done incorrectly. Today I hope to unravel some of what each is and how to see permissions for each type of principal.

Let’s first look at definitions for each type of principal and how to query for basic information about each principal (e.g. name and creation date).

Principals

In general principals are entities to which permissions are granted. These entities can request access or resources. As mentioned already, these principals can be scoped to different levels. These levels as mentioned include database and server.

Server Principals

Server Principals are the kind of principals that include logins and server roles. You may be familiar with some of the server roles:

public
sysadmin
securityadmin
serveradmin
setupadmin
processadmin
diskadmin
dbcreator
bulkadmin

The logins can be created from a windows login or group, or be created as a SQL Login. These principals can also include custom created server roles (in addition to the system created server roles already listed). Once a principal is created, permissions may be granted to the principal. When these permissions are granted, then when the principal attempts to request a resource (related to the permission), to perform a task, the principal can complete that task.

What permissions can be granted to a principal at the server scope? A list of permissions can be created via the following query:

SELECT *
FROM fn_builtin_permissions('SERVER') bp
ORDER BY bp.permission_name;
GO

And a sample of the results could look like this:

An interesting note here is in the red highlighted permissions. If you look at the documentation for server permissions you will not find those two permissions (at least not as of this writing).

From the permissions returned by the query, you will see that these are all permissions related to server administration type of tasks. Note that these permissions do not grant the ability to do the type of tasks attributed to database types of actions. For instance, the server permissions do not grant the explicit permission to create a reference, execute a procedure or create a table within a database. These are all permissions reserved for the database scope.

Database Principals

Database principals are the type of principals scoped to the database level. These principals will request resources from the database and depending on permissions granted to the principal be able to perform various tasks within the database. The types of database principals include database roles, application roles, and database users. SQL Server Logins can be mapped to a database user and thereby be granted access to the database as the database principal.

Since a database principal can include the database roles, here is a list of the potential database roles:

public
db_owner
db_accessadmin
db_securityadmin
db_ddladmin
db_backupoperator
db_datareader
db_datawriter
db_denydatareader
db_denydatawriter

These roles can significantly simplify security management within the database. One can easily assign a database principal to be a member of the db_backupoperator role and thus grant that principal the ability to backup the database without much more need to grant or deny permissions.

Considering the ease of role management, one thing that bugs me and that I see frequently is the addition of a database user to every database role in all databases. It makes little sense to add a user to the db_owner role and then also to the db_datareader and db_denydatareader roles. For one, db_owner already has the ability to read from the tables thus negating db_datareader. Consider the db_denydatareader now – it is opposite to db_datareader. Why try to grant and deny read access to the same user? It makes no sense at all.

The next thing that pains me about these roles is the public role. I have written about the public role previously, but it needs stating again. Do not grant any additional permissions to the public role. This is like enabling the guest user and opening up the database to everybody. I have seen a rash of granting “alter server state” and “view server state” to the public server level role and it is painful to see. The same advice applies to the public role whether it is at the server or database scope.

Once a database principal has been created, it is time to proceed to giving the principal the necessary access. Here are some of the possible permissions that can be granted (along with a query to find even more):

SELECT *
FROM fn_builtin_permissions('DATABASE') bp
ORDER BY bp.permission_name;
GO

Within these results, it is apparent that a database principal can be granted the ability to perform backups, create procedures, execute procedures and even create encryption keys. Between the server scope and the database scope, there is decent level of granularity to control access and resources within the database instance.

Finding Permissions for Each Principal

It is not uncommon to need to know and report on who has been granted what level of permissions within the database environment. If you have been with the database since inception to conception, you probably have documentation on every permission that has been granted. If you inherit a database, your odds on good documentation about the permissions is probably significantly lower. I have shared a comprehensive script previously to show all of the permissions. Sometimes a little less info is more than adequate for the current needs.

Here is a quick alternative with just a little less info to provide database permissions and server permissions based on the input of a specific list of users and databases.

SET NOCOUNT ON;
GO
DECLARE @DBList VARCHAR(1000) = 'master,AdventureWorks2014,dba'
/* delimited list of databases */DECLARE @DBToQuery sysname;
DECLARE @SQL VARCHAR(4000);
DECLARE @UsersorGroups VARCHAR(1024) = '[Phantom],[Garguoille],[TestUser]'
/* comma delimited list of groups or users to check */CREATE TABLE #Principals 
( UserorGroup sysname )  
INSERT INTO #Principals (UserorGroup)
     SELECT REPLACE(REPLACE(Item,'[',''),']','')   
           FROM DBA.dbo.DelimitedSplit8K(@UsersorGroups,',')  
CREATE TABLE #DBs 
( DBName sysname )  
INSERT INTO #DBs (DBName)
     SELECT Item   
           FROM DBA.dbo.DelimitedSplit8K(@DBList,',')  
CREATE TABLE #PermsAudit (DBName sysname,ServerRole VARCHAR(128), PrincipalName sysname,PrincipalPerms VARCHAR(128)
,ServerRolePermissions VARCHAR(2048),DBRolePermissions VARCHAR(2048),ServerPermissions VARCHAR(2048))
DECLARE getperms CURSOR
  LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
 SELECT d.name
FROM sys.databases d
INNER JOIN #DBs td
ON d.name = td.DBName;
OPEN getperms;
FETCH NEXT FROM getperms INTO @DBToQuery;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'USE ' + @DBtoQuery +';' + char(10) +
'SELECT DB_NAME() AS DBName,roleprin.name AS ServerRole, tp.UserorGroup AS PrincipalName, sp.permission_name AS PrincipalPerms
,(SELECT 
STUFF( 
(SELECT '', <'' + sp.permission_name + ''>'' 
FROM sys.server_permissions sp
WHERE roleprin.principal_id = sp.grantee_principal_id
ORDER BY sp.permission_name 
FOR XML PATH(''''), type 
).value(''(./text())[1]'',''varchar(max)'') 
, 1, 2, '''') ) AS ServerRolePermissions
,(SELECT 
STUFF( 
(SELECT '', <'' + dpr.permission_name + ''>'' 
FROM sys.database_principals dp
INNER JOIN sys.database_permissions dpr
ON dp.principal_id = dpr.grantee_principal_id
WHERE dp.sid = pr.sid
ORDER BY dpr.permission_name 
FOR XML PATH(''''), type 
).value(''(./text())[1]'',''varchar(max)'') 
, 1, 2, '''') ) AS DBRolePermissions
,(SELECT 
STUFF( 
(SELECT '', <'' + dp.permission_name + ''>'' 
FROM sys.server_permissions dp
WHERE pr.principal_id = dp.grantee_principal_id
ORDER BY dp.permission_name 
FOR XML PATH(''''), type 
).value(''(./text())[1]'',''varchar(max)'') 
, 1, 2, '''') ) AS ServerPermissions
FROM sys.server_principals pr
LEFT OUTER JOIN sys.server_role_members srm
ON pr.principal_id = srm.member_principal_id
LEFT OUTER JOIN sys.server_permissions sp
ON sp.grantee_principal_id = pr.principal_id
LEFT OUTER JOIN sys.server_principals roleprin
ON srm.role_principal_id = roleprin.principal_id
RIGHT OUTER JOIN #Principals tp
ON pr.name = tp.UserorGroup'
;
INSERT INTO #PermsAudit
        ( DBName ,
          ServerRole ,
          PrincipalName ,
          PrincipalPerms ,
          ServerRolePermissions ,
          DBRolePermissions,
  ServerPermissions
        )
EXECUTE (@SQL);
FETCH NEXT FROM getperms INTO @DBToQuery;
END
CLOSE getperms;
DEALLOCATE getperms;
SELECT @@SERVERNAME AS SvrName,*
FROM #PermsAudit;
DROP TABLE #Principals;
DROP TABLE #DBs;
DROP TABLE #PermsAudit;
GO

Now, this script does require the use of a function to split strings. The one I am referencing was written by Jeff Moden and can be found here. In this example, I am looking at a few test principals that I created – testuser, Phantom, Gargouille and Garguoille (which is invalid). Running the script, I would receive results such as the following:

This is a pretty quick running script to gather report worthy data on principals and permissions.

Security as a Fleeting Thought February 10, 2015
Bare Naked Skinny on SQL Server March 23, 2016
Audit who Dropped the Database April 7, 2015
Effects of sp_rename on Stored Procedures August 14, 2014
SQL Server Locks – Back to Basics March 1, 2016

SQL Server Principals – Back to Basics

Prelude in SQL Minor