SQL Server 2014 new Server Roles

In this blog post we will talk about SQL Server 2014 new Server Roles. There are three server roles added CONNECT ANY DATABASE, IMPERSONATE ANY LOGIN, and SELECT ALL USER SECURABLES.

--List server role permissions
 
select *
from sys.fn_builtin_permissions('SERVER')
order by permission_name;
 
--If you execute this query you will get 31 permission in SQL Server 2012 
--whereas there are 34 permissions in SQL Server 2014

Let us see each one of them in action:
Now I have created a test windows account named “testUser” which doesn’t have any privilege on SQL Server at this time.
Let us first create a login for “testUser”

use master
GO
 
CREATE LOGIN [BIZHOST\testUser] 
	FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

Next let us create a new instance of SQL Server for our new user. If you are not sure how to do that check out this blog first.
Now if we try to see the schema of any database in the server I will get an error.

Nothing surprising here so far so either I have to upgrade the login permission on the server level to the a higher server role such as sysAdmin. But If I don’t want to do that I have to create a User for the login on each database okay I could do that too. But what if I just don’t want to get bother to create a user an account on each future database I might have then the best bet will be to give the login to

--give connect to any existing or future databases to testUser
GRANT CONNECT ANY DATABASE TO [BIZHOST\testUser]
GO

EXECUTE AS statement help us to change the execution context of the session is switched to the specified login or user name. But so far unless the caller is the database owner, or is a member of the sysadmin fixed server role, the principal must exist even when the user is accessing the database or instance of SQL Server through a Windows group membership. Then new server role GRANT IMPERSONATE ANY LOGIN enables us to curb the traditional restriction.
Now to do the next exercise let us create a user for the login and make it the database reader to make our life easier

--create user for testUser login on contosoRetailDW
USE [ContosoRetailDW]
GO
CREATE USER [testUser] FOR LOGIN [BIZHOST\testUser] WITH DEFAULT_SCHEMA=[dbo];
GO
ALTER ROLE [db_datareader] ADD MEMBER [testUser]
GO

Now if we try to impersonate using our testUser account we will get this error

EXECUTE AS USER = 'bizNigatu';
 
select top(20) *
from [dbo].[FactSales];
 
REVERT;

--grant permsission to imporsonate
use master
GO
GRANT IMPERSONATE ANY LOGIN TO [BIZHOST\testUser] 
GO

Now could impersonate any user without being a sysAdmin or any other power user.

The third Server Role SELECT ALL USER SECURABLES it is similar to CONNECT TO ANY DATABASE but this time not only having the see the schema we could also see users data.
For this exercise I am going to drop the user account we created above on ContosoRetailDW.

--drop user from contosoRetailDW
USE [ContosoRetailDW]
GO
DROP USER [testUser]
GO

Now if we grant our testUser on to select all securable we could see the database the schema and the data.

--grant permsission to select user securables
use master
GO
GRANT SELECT ALL USER SECURABLES TO [BIZHOST\testUser] 
GO