Category Archives: LearnSQL

Learn SQL Server Login Impersonation

Technically SQL Server and windows both can be configured to authenticate to another SQL instance\Windows Server against the current login account, this behavior is known as Impersonation. When one login\user acts on behalf of another login\user this behavior is also known as impersonation.

Server Level

We can easily impersonate a session by explicitly mentioning a standalone EXECUTE AS statement but for doing so you should have Impersonate permissions on the other login else you’ll not be allowed to impersonate and you’ll face the similar error mentioned below:

Impersonate error:

clip_image002

Error: Msg 15406, Level 16, State 1, Line 1

Cannot execute as the server principal because the principal “sarab_SecurityAdmin” does not exist, this type of principal cannot be impersonated, or you do not have permission.

 

Check the Permission to Impersonate

Let’s check if the login (Sarabpreet) has the Impersonate Permission to impersonate as Sarab_SecurityAdmin, for doing so we’ll run the below mentioned query:

select * from sys.server_principals

If the current logged in login has impersonate permission to impersonate the principal name will appear in the result set. Since we got the error & now the result set also doesn’t show the principal Sarab_SecurityAdmin, it is now confirmed that Sarabpreet login doesn’t have appropriate impersonate permissions.

clip_image003

 

Grant Impersonate Permission on Login

To grant the impersonate permission you can execute the below mentioned query, in this query we are granting Impersonate permission to Sarabpreet so that the login Sarabpreet can impersonate as Sarab_SecurityAdmin.

GRANT IMPERSONATE ON Login::Sarab_SecurityAdmin TO Sarabpreet

Since we’ve granted the permission let’s cross verify this once:

clip_image004

You can see that now the principal Sarab_SecurityAdmin is visible to Sarabpreet login, which means now the login Sarabpreet can execute queries by impersonating as Sarab_SecurityAdmin.

 

To Revoke Impersonate Permission

REVOKE IMPERSONATE ON Login::Sarab_SecurityAdmin TO sarabpreet

For User Impersonation read: Learn SQL Server User Impersonation

 

Hope you enjoyed the post, feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

SQL Server Resource Governor Webcast – Demo Scripts

Hi Friends,

Last week I took a webcast “Tap is the only Solution Ver.2 (SQL Server Resource Governor)“. The webcast was very well received and there were a lot of questions regarding the Demo and the scripts. So in this blog post I’ll provide all the scripts to implement, test, tweak & remove Resource Governor. You can also use these scripts to do Proof of Concept (POC) on Resource Governor.

Hope this will help you.

Happy Learning\Testing – DO leave a comment to let us know how we are doing.

Implement\ configure Resource Governor

 

Create database simple

go

—Login creation for all users, I disabled strong password checking for demo

 

–purposes only but this is against best practices

USE [master]

GO

CREATE LOGIN [Sales_app] WITH PASSWORD=N’sales_app’, DEFAULT_DATABASE=[simple], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE LOGIN [Reporting_app] WITH PASSWORD=N’reporting_app’, DEFAULT_DATABASE=[simple], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE LOGIN [Fin_App] WITH PASSWORD=N’fin_app’, DEFAULT_DATABASE=[simple],CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Go

use simple

Go

CREATE USER [Sales_app] FOR LOGIN [Sales_app]

CREATE USER [Reporting_app] FOR LOGIN [Reporting_app]

CREATE USER [Fin_App] FOR LOGIN [Fin_App]

GO

–limiting resources to test Resource Governor because I am using a laptop with 8 proc and 8GB ram

 

–which will be too much and complex to do POC

sp_configure ‘show ‘,1

go

reconfigure

go

sp_configure ‘min server’, 2048;

go

sp_configure ‘max server’, 2048;

RECONFIGURE

GO

— create user pools

— note that we are using all default parameters

CREATE RESOURCE POOL Pool_Sales_fin_app

CREATE RESOURCE POOL Pool_reporting

— create user groups also note that all groups created with default parameters only pointing to the

 

–corresponding pools (and not ‘default’ pool)

CREATE WORKLOAD GROUP Sales_Group

USING Pool_Sales_fin_app

CREATE WORKLOAD GROUP Reporting_Group

USING Pool_reporting

CREATE WORKLOAD GROUP Fin_Group

USING Pool_Sales_fin_app

GO

— now create the classifier function

Use master

go

IF OBJECT_ID(‘DBO.CLASSIFIER_1′,’FN’) IS NOT NULL

DROP FUNCTION DBO.CLASSIFIER_1

GO

— note that this is just a regular (UDF) User Defined Function

CREATE FUNCTION DBO.CLASSIFIER_1()

RETURNS SYSNAME WITH SCHEMABINDING

BEGIN

DECLARE @val varchar(32)

SET @val = ‘default’;

if ‘Sales_app’ = SUSER_SNAME()

SET @val = ‘Sales_Group’;

else if ‘Reporting_app’ = SUSER_SNAME()

SET @val = ‘Reporting_Group’;

else if ‘Fin_App’ = SUSER_SNAME()

SET @val = ‘Fin_Group’;

return @val;

END

GO

— make function known to the Resource Governor

ALTER RESOURCE GOVERNOR

WITH (CLASSIFIER_FUNCTION = DBO.CLASSIFIER_1)

GO

–Check if Reconfigure is pending for any setting of Resource Governor

SELECT * FROM sys.dm_resource_governor_configuration

— make the changes effective

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

Workload – by different Sessions

 

declare @i int

declare @s varchar(100)

set @i = 10

while @i > 0

begin

select @s = @@version;

end

Using Cap_CPU_Percent Parameter – New to SQL 2012 to configure Hard Cap on CPU

CPU_CAP_PERCENT & AFFINITY SCHEDULER settings are available only through Scripts as of now- Planned to come in GUI from SP1

 

ALTER RESOURCE POOL [Pool_Sales_fin_app]

WITH (CAP_CPU_PERCENT=30)

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

ALTER RESOURCE POOL [Pool_Reporting]

WITH (CAP_CPU_PERCENT=40)

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

Using Affinity Parameter to dedicate a scheduler to a single pool

 

ALTER RESOURCE POOL Pool_Sales_fin_app

WITH (AFFINITY SCHEDULER = (0))

GO

ALTER RESOURCE POOL Pool_reporting

WITH (AFFINITY SCHEDULER = (1))

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

Go

Internal Tables\DMVs for Resource Governor

–Metadata Tables

 

SELECT * FROM sys.resource_governor_workload_groups

SELECT * FROM sys.resource_governor_resource_pools

SELECT * FROM sys.resource_governor_configuration

DMV’s

 

SELECT * FROM sys.dm_resource_governor_workload_groups

SELECT * FROM sys.dm_resource_governor_resource_pools

SELECT * FROM sys.dm_resource_governor_configuration

Select * from sys.dm_resource_governor_resource_pool_affinity — – New DMV in SQL 2012 for Affinity setting & Schedulers mask

Script to check which scheduler is being used by which Resource Group

select

r.session_id,

CONVERT(NCHAR(20), wg.name) as group_name,

t.scheduler_id,

r.status

from sys.dm_exec_requests r

join sys.dm_os_tasks t on r.task_address = t.task_address

join sys.dm_resource_governor_workload_groups wg on r.group_id = wg.group_id

where

r.session_id > 50

Clean-Up

—Clean Up Script

ALTER RESOURCE GOVERNOR disable;

–Drop Logins and associated Users

USE [simple]

GO

DROP USER [Sales_app]

DROP USER [Reporting_app]

DROP USER [Fin_App]

GO

USE [master]

GO

DROP LOGIN [Sales_app]

DROP LOGIN [Reporting_app]

DROP LOGIN [Fin_App]

GO

–drop workload

USE [master]

GO

DROP WORKLOAD GROUP [Reporting_Group]

DROP WORKLOAD GROUP [Sales_Group]

DROP WORKLOAD GROUP [Fin_Group]

go

–drop pools

USE [master]

GO

DROP RESOURCE POOL [Pool_Sales_fin_app]

DROP RESOURCE POOL [Pool_Reporting]

GO

—drop classifier function

ALTER RESOURCE GOVERNOR

WITH (CLASSIFIER_FUNCTION = Null)

GO

DROP FUNCTION DBO.CLASSIFIER_1

 

Reference: Boris Baryshnikov’s blog post & Whitepapers for Resource Governor 2008 & 2012.

Regards

Sarabpreet Anand