User needs sysadmin permission to the instance but he should be denied permission on some databases

If someone wants to manage the SQL Server instance completely and perform any activity in the server, she or he should be a member of sysadmin server-level role because it bypasses all security checks. But what if you need to someone be a member of sysadmin server-level role and restrict him to some databases? Is it possible?

Although it is not a common scenario, a requirement like this can be popped up and we may struggle to find a solution. How can we do it? If User1 is a member of sysadmin server-level role, we have no way of restricting User1 accessing a database. In order to handle the scenario, User1 needs to be given all permissions offered with sysadmin role manually without adding him to sysadmin role and limit his permission only for required databases. Yes, it is possible to address individual permission with individual statements up to some extent but it is time consuming and makes the whole process complex. It becomes more and more complex and increases the maintenance cost if more users have to be managed in similar way. The best solution is, grant Control Server permission to User1.

The Control Permission is similar to Sysadmin fixed server role but not identical. It offers all the permissions offered with Sysadmin role but there are some limitations with some objects such as DBCC commands and some system stored procedures. However, a requirement like above can possibly managed with it.

For more info on this: https://msdn.microsoft.com/en-us/library/ms188659.aspx

Let's test this. Assume that Jane and Joe are database administrators and they need full permissions on the instance. However, Joe should not be able to access AdventureWorksDW2014 database. Let's try to handles this scenario with Control Permission though it does not offer a 100% working solution.

Below code creates logins for Jane and Joe; Jane is in Sysadmin fixed server role and Joe has Control Server permission.

USE master;
GO

-- Create Jane login
CREATE LOGIN Jane
WITH PASSWORD = '123'
, CHECK_EXPIRATION=OFF
, CHECK_POLICY=OFF;
GO

-- Add Jane to Sysadmin role
ALTER SERVER ROLE sysadmin ADD MEMBER Jane;
GO

-- Create Joe Login
CREATE LOGIN Joe
WITH PASSWORD = '123'
, CHECK_EXPIRATION=OFF
, CHECK_POLICY=OFF;
GO

-- Grant Control Server permission to Joe
GRANT CONTROL SERVER TO Joe;
GO

Let's check permission assigned for both. Below query confirms that both have same permissions.

EXECUTE AS LOGIN = 'Jane';
SELECT * FROM sys.fn_my_permissions(null, null);
REVERT;
GO

EXECUTE AS LOGIN = 'Joe';
SELECT * FROM sys.fn_my_permissions(null, null);
REVERT;
GO

Now we need to restrict Joe to AdventureWorksDW2014 database. For testing purposes, let's deny permission to both Jane and Joe.

-- deny permission to AdventureWorksDW2014
USE AdventureWorksDW2014;
GO

-- We do not need User account in the database
-- because both Jane and Joe have admin rights
-- But let's create them
CREATE USER Jane FOR LOGIN Jane;
CREATE USER Joe FOR LOGIN Joe;
GO

-- This gives deny permission to Jane
ALTER ROLE db_denydatareader ADD MEMBER jane
ALTER ROLE db_denydatawriter ADD MEMBER Jane
GO

-- This gives deny permission to Joe
ALTER ROLE db_denydatareader ADD MEMBER Joe
ALTER ROLE db_denydatawriter ADD MEMBER Joe
GO

Let's see how this works now. Below code shows that Jane can still access the database (tables particularly) but Joe cannot access.

-- Jane tryint to access AdventureWorksDW2014
-- Jane will be able to access because she is in sysadmin
-- role and system bypasses security checks.
EXECUTE AS LOGIN = 'Jane';
SELECT * FROM AdventureWorksDW2014.dbo.DimProduct;
REVERT;
GO

-- Joe trying to access AdventureWorksDW2014
-- Joe will NOT be able to access because he is not in syadmin 
-- role and system will check for permissions
EXECUTE AS LOGIN = 'Joe';
SELECT * FROM AdventureWorksDW2014.dbo.DimProduct;
REVERT;
GO

Have we solve the problem with this solution completely? Since we just restrict table reading and writing, we have not addressed it fully but I believe that it is managed up to some extent now.

Can Joe change his User Account in the AdventureWorksDW2014 with high permissions?

Can Joe upgrade himself as Sysadmin?

As you see, many things are not possible with Joe's account. However this needs to be thoroughly tested as I have not used this in production.

SQL Server Security Concepts: Securables, Pricipals and Permissions

When discussing and learning security related to SQL Server, it is always better to know the key concepts and terms related to it. Some of the concepts are not only related to SQL Server, they are related to almost all applications in terms of security. In simplest way, security is all about allowing someone or something to access a resource and perform actions on it. Here are some terms used when describing it:

Securables

Securable is mainly a resource which we can assign permissions. SQL Server has securables at multiple level of a hierarchical architecture. The hierarchy starts from the server level which includes securables like Endpoints, Logins, Server Roles and Databases. These securables are called as server-level securables as well as server scope securables.

Next level of the hierarchy is the database. Database-level or database scope securables includes items like Users, Database Roles, Certificates and Schemas.

SQL Server includes securables at Schema level too. They are called as schema scope securables that includes resources like Tables, Views, and Procedures.

Principals

The someone or something that perform actions on securables is called as a Principal. There are three types of principals related to SQL Server security: Window's Principals, SQL Server Principals, and Database Principals.

Window's principals and SQL Server principals are considered as server level principals. Windows level principals are generally domain or local server user accounts or groups that are used to connect with SQL Server instance. Authentication is done by either local server or domain controller, and SQL Server trusts the account without performing authentication. SQL Server level principals are logins created at SQL Server instance and authentication is done by SQL Server itself.

Database principals includes database users, database roles and application roles.

** Some principals are also securables. As an example, Login is a principal as well as a securable. It is a principal that because it can access the SQL Server instance and it is also a securable because there are actions that can be performed on it such as enabling and disabling that require permission.

Permissions

Permissions allow principals to perform actions on securables. There are two types of permissions related to SQL Server: Statement permission and Object Permission.

Statement permissions refer actions that can be performed by executing a statement. The principal creating a table using CREATE TABLE statement with CREATE TABLE permission is an example for it.

Object permissions refer actions that can be performed on securables. A principal having SELECT permission on a table is an example for this.

This image shows how these concepts are worked together. Note that the image has been taken from an article in TechNet Magazine. You can refer the article at: https://technet.microsoft.com/en-us/magazine/2009.05.sql.aspx