Server-Level Roles
SQL Server security is like a box of chocolates. Wait, it is more like an onion – with all of the layers that get to be peeled back. One of the more important layers, in my opinion, is the layer dealing with Roles.
I have written about the various types of roles on several occasions. Whether it be Fixed Server Role memberships, Fixed Server Role permissions, or Database Roles permissions (among several options), you can presume that I deem the topic to be of importance.
Within the “Roles” layer of the SQL Server security onion, there are multiple additional layers (as alluded to just a moment ago) such as Database Roles and Server Roles. Focusing on Server Roles, did you know there are different types of Server Roles? These types are “fixed roles” and “custom roles.” Today, I want to focus on the custom type of role.
Custom Server Roles
Starting with SQL Server 2014, we were given a new “feature” to help us minimize our security administration efforts. The new “feature” is that which allows a data professional to create a “Server Role” in SQL Server and to grant specific permissions to that role. I wrote about how to take advantage of this in the 2014 recipes book I helped to author, but never got around to creating an article here on how to do it.
In this article, I will take you through a quick example of how to take advantage of these custom roles.
First let’s create a login principal. This principal is a “login” so will be created at the server level. Notice that I perform an existence check for the principal before trying to create it. We wouldn’t want to run into an ugly error, right? Also, when you use this script in your environment, be sure to change the DEFAULT_DATABASE to one that exists in your environment. While [] is an actual database in my environment, it is highly unlikely it exists in yours!
USE [master];
GO
IF NOT EXISTS
(
SELECT name
FROM sys.server_principals
WHERE name = 'Gargouille'
)
BEGIN
CREATE LOGIN [Gargouille]
WITH PASSWORD = N'SuperDuperLongComplexandHardtoRememberPasswordlikePassw0rd1!'
, DEFAULT_DATABASE = []
, CHECK_EXPIRATION = OFF
, CHECK_POLICY = OFF;
END;
Next, we want to go ahead and create a custom server level role. Once created, we will grant specific permissions to that role.
--check for the server role
IF NOT EXISTS
(
SELECT name
FROM sys.server_principals
WHERE name = 'SpyRead'
AND type_desc = 'SERVER_ROLE'
)
BEGIN
CREATE SERVER ROLE [SpyRead] AUTHORIZATION [securityadmin];
GRANT CONNECT ANY DATABASE TO [SpyRead];
GRANT SELECT ALL USER SECURABLES TO [SpyRead];
END;
As you can see, there is nothing terrifyingly complex about this so far. The statements should be pretty familiar to the data professional and they are fairly similar to routine tasks performed every day. Note in this second script that after I check for the existence of the role, I simply use “CREATE SERVER ROLE” to create the role, then I add permissions explicitly to that role.
Now, I will add the login “Gargouille” to the Server Role “SpyRead”. In addition to adding the login principal to the role principal, I will validate permissions before and after – permissions for Gargouille that is.
EXECUTE AS LOGIN = 'Gargouille'
GO
USE [];
GO
SELECT *
FROM fn_my_permissions(NULL, 'DATABASE') fn;
REVERT
USE master;
GO
IF NOT EXISTS
(
SELECT mem.name AS MemberName
FROM sys.server_role_members rm
INNER JOIN sys.server_principals sp
ON rm.role_principal_id = sp.principal_id
LEFT OUTER JOIN sys.server_principals mem
ON rm.member_principal_id = mem.principal_id
WHERE sp.name = 'SpyRead'
AND sp.type_desc = 'SERVER_ROLE'
AND mem.name = 'Gargouille'
)
BEGIN
ALTER SERVER ROLE [SpyRead] ADD MEMBER [Gargouille];
END;
EXECUTE AS LOGIN = 'Gargouille'
GO
USE [];
GO
SELECT *
FROM fn_my_permissions(NULL, 'DATABASE') fn;
REVERT
We have a few more things happening in this code snippet. Let’s take a closer look and break it down a little bit. The first section tries to execute some statements as “Gargouille”. When this attempt is made, there is an error that is produced – which is good because it validates the principal does not have permission to connect to the requested database.
The next statement of interest adds the “Gargouille” principal to the SpyRead Server role. After the principal is added to the custom server role, I attempt to impersonate the “Gargouille” principal again and connect to the database and run a permissions check. These are the results from that last query.
Lastly, I run a check to validate that Gargouille is indeed a member of the server role “SpyRead” – which it is. From these results we can see the power of the customer server role. In this case, I had a user that “needed” to access every database on the server. Instead of granting permissions on each database one by one, I granted the “Connect” (and a couple of other permissions to be discussed in the follow-up article) to the server role and then added Gargouille to that role. This reduced my administration time requirement quite a bit – more if there are hundreds of databases on the server.
In the follow-up article, I will show how this will help to make it easier to grant a user the ability to view schema definitions as well as read from every database with one fell swoop. Stay tuned!
Wrapping it Up
In this article, I have shown how to use the power of custom server roles to help reduce your administration time. The custom security role is like using a security group to grant a bunch of people the same sets of permissions. When you use a security group to manage multiple people, it makes administration very much like you have offloaded the job to somebody else to do because it becomes that easy!
Now it is your turn, take what you have learned in this article and see how you could apply it within your environment to help you be a rockstar data professional.
Feel free to explore some of the other Back to Basics posts I have written.
Are you interested in more articles showing what and how to audit? I recommend reading through some of my auditing articles. Feeling like you need to explore more about the security within SQL Server, check out this library of articles here.
Related Posts:
SQL Server User Already Exists - Back to Basics January 24, 2018
Quick Permissions Audit January 14, 2019
When Too Much is Not a Good Thing December 13, 2019
Easy Permissions Audit January 21, 2019
Cannot Use the Special Principal - Back to Basics November 7, 2018
The post Server-Level Roles - Back to Basics first appeared on SQL RNNR.
The post Server-Level Roles – Back to Basics appeared first on SQLServerCentral.
Read more
United States
Great Britain
India
Germany
France
Canada
Russia
Spain
Brazil
Australia
South Africa
Thailand
Ukraine
Switzerland
Slovakia
Luxembourg
Hungary
Romania
Denmark
Ireland
Estonia
Belgium
Italy
Finland
Cyprus
Lithuania
Latvia
Malta
Netherlands
Portugal
Slovenia
Sweden
Argentina
Colombia
Ecuador
Indonesia
Mexico
New Zealand
Norway
South Korea
Taiwan
Turkey
Czechia
Austria
Greece
Isle of Man
Bulgaria
Japan
Philippines
Poland
Singapore
Egypt
Chile
Malaysia
