Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Tighten SQL Server security with custom server and database roles


By:   |   Read Comments (1)   |   Related Tips: More > Security

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

SQL Server security architecture contains a hierarchical collection of entities, starting with server, database and objects (also called levels) and SQL Server provides a set of fixed roles to easily manage the permissions.

We often create logins for DBAs using the sysadmin role and this role gives access to everything. Normally, database administrators donít need permissions to insert, update or delete records, and denying this kind of access is the first step to improve your security in your production environments.

Solution

First, letís see who has sysadmin access, by executing the following query to see the fixed roles granted for the principals:

SELECT 
   SP1.name AS ServerRoleName, 
   ISNULL(SP2.name, 'No members') AS LoginName
FROM sys.server_role_members AS SRM
RIGHT OUTER JOIN sys.server_principals AS SP1 ON SRM.role_principal_id = SP1.principal_id
LEFT OUTER JOIN sys.server_principals AS SP2 ON SRM.member_principal_id = SP2.principal_id
WHERE SP1.is_fixed_role = 1 and SP1.name = Ďsysadminí
ORDER BY SP1.name;
GO

The next step is remove the sysadmin role from the logins we want to restrict access to and grant CONTROL SERVER permission for that login.

Control Server and sysadmin have the same kind of permissions and with that they can manage the entire database server instance. But, giving CONTROL SERVER permissions we can also deny updates, deletes and inserts when we map the login with the database user.

At this point we know which logins we need to change and what kind of permission to use. Now, we can define a new server role and a database role for our database administrator and separate activities like monitoring, backup, maintenance, etc.

Creating a new SQL Server server role

Here is the code to create a new SQL Server server role, with the Control Server access.

USE [master]
GO
CREATE SERVER ROLE [dbaadmin]
GO

GRANT CONTROL SERVER TO [dbaadmin]
GO 

Mapping the database user with the db_owner database fixed role will grant more access than necessary, so we will create our own database role with limited permissions.  We will do this for the AdventureWorks2012 database.

Creating a new SQL Server database role

Here are denying update and delete privileges for this database role.

USE [AdventureWorks2012]
GO
CREATE ROLE [db_dbaadmin]
GO

DENY UPDATE TO [db_dbaadmin]
GO
DENY DELETE TO [db_dbaadmin]
GO 

Also, there are more permissions we will deny in this example.

DENY ALTER TO db_dbaadmin
GO
DENY EXECUTE TO db_dbaadmin
GO 

Additional information about permissions

The most specific and limited permissions that can be granted on an object are listed in the following table:

Object Permissions
ALTER ALTER permission on a schema includes the ability to create, alter, and drop objects from the schema
CONTROL CONTROL on a database implies all permissions on the database
DELETE Deletes all classes of objects except DATABASE SCOPED CONFIGURATION, and SERVER
EXECUTE To run CLR types, external scripts, procedures, scalar and aggregate functions
INSERT Insert records using synonyms, tables and views objects. Permission can be granted at the database, schema, or object level.
RECEIVE Service Broker queues
REFERENCES The REFERENCES permission on a table is needed to create a FOREIGN KEY constraint that references that table
SELECT Select synonyms, tables and columns, views and columns. Permission can be granted at the database, schema, or object level. Also, is necessary to see the executing plan
TAKE OWNERSHIP Enables the grantee to take ownership of the securable on which it is granted
UPDATE Update records using synonyms, tables and views objects. Permission can be granted at the database, schema, or object level.
VIEW CHANGE TRACKING Manage change tracking on schemas and tables
VIEW DEFINITION Enables the grantee to access metadata

Read the following to learn more about deny permissions on a database and server in SQL Server:

Create the SQL Server login and assign roles

After designing our own roles, the next step is to create the logins and users.

In the example below, we will create a new login named dbaLogin and add this login to the dbaadmin server role and the db_dbadmin database role.

Note, if the login already exists we only need to add the server role for the login, remove the sysadmin fixed role and add the user to the database and add the user to the database role.

USE [master]
GO
CREATE LOGIN [dbaLogin] WITH PASSWORD=N'sql', DEFAULT_DATABASE=[AdventureWorks2012], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [dbaadmin] ADD MEMBER [dbaLogin]
GO

USE [AdventureWorks2012]
GO
CREATE USER [dbaUser] FOR LOGIN [dbaLogin]
GO
USE [AdventureWorks2012]
GO
ALTER ROLE [db_dbaadmin] ADD MEMBER [dbaUser]
GO

Testing the new SQL Server roles

We will test the login by executing some code in the user database.  As expected, these commands fail.

execute as login

But if we run a system stored procedure like sp_who2 it returns the necessary results.

tas manager

In addition, CONTROL SERVER gives permission to add or remove members from any role, but when we denied the ALTER permission the login can't change its own permission as follows.

alter server role

Conclusion

As stated before, configuring our own roles is a fast and easy way to control server and database access in SQL Server.  Also your security will be in compliance with the company's security policies.

The downside to this is if you restore a SQL Server database to another environment, you will need to remap the server logins and database users.

Creating different access for specifics activities is a good way to have a better security solution. For example, the application login doesnít need permission to alter objects and with the right security solution you can find out fast what happened in your environment using audit filters for a specific login.

Take this as a starter to begin defining better security roles for your SQL Server environment.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Douglas Correa Douglas Correa is a database professional, focused on tuning, high-availability and infrastructure.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, August 02, 2017 - 3:03:52 PM - TamusJRoyce Back To Top

Not in agreement. But I do support your goals.

I think everyone in IT, DBA, Developer, Ops, should have multiple roles.

1. Administrative Roles (Team Leads, DBA's, Ops, or Available Upon Request / seen responsibility) - rarely used

2. Regular roles as mentioned above

3. Business only rights (to test as if you are a business user)

And we should log into our computers with whatever rights we need. If we need to switch users:  Linux, Windows, and Mac supports multiple users logged in and user switching. Backgrounds, borders, etc, should be selected to make it inuitive in which environment we are in.

Agile manifesto places importance in communication over technology. Good communication / certifications should be rewarded with #1 having more permissions (via requests and consideration). Bad communication (over, under, or non-relevant communication) should result in less permissions for #1. Length of employment is needed to build a track record. But certifications can bypass it.

Asking permissions to do your job is unproductive. But also, given free access is dangerous. Don't make it cost days of waiting for a request to be fulfilled. But do have a little pain via user switching.

 


Learn more about SQL Server tools