Ownership chain in SQL Server

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question about SQL Server stored procedures and ownership chain.

Security is a big thing in SQL Server and can be implemented in various levels.

Before we move on, we should define some basic terms that are very important when we talk about security in SQL Server.

The principal access data. It should be authenticated by SQL Server which basically means that the principal should “prove” that it is , whom it claims to be.

Principal is a user a group or an application granted permission to access securables.So what is a securable? Securable is an object to which access privileges are granted.

Privilege is an action that a principal is allowed to perform an a securable.

I want to show you an example where we want to deny a user to the actual table-data but grant him access through a view or a stored procedure.UserA is the owner of tableA and creates StoredProcA that gets data from tableA. Then if the UserB is granted permission to execute the StoredProcA even though he does not have direct access to tableA he will be able to get access (indirectly) to the table. This is the concept of ownership chaining.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

USE master
GO

--Create a sample database

CREATE DATABASE mysampledb;
GO

-- create a login

CREATE LOGIN login1 WITH PASSWORD ='login1', CHECK_POLICY = OFF;

USE mysampledb;
go

--Create a User for that login
CREATE USER thelogin1 FOR LOGIN login1;

--see the user that you are now (dbo)
SELECT user
--create table and populate it with sample data

CREATE TABLE dbo.person

(id INT IDENTITY (1,1),
 firstname varchar(40),
 lastname VARCHAR(50)

);
go

INSERT dbo.person VALUES ('nick','kenteris'),('maria','negri'),('apostolis','kaperis'),('kostas','gekas');

CREATE PROCEDURE dbo.getpersons
AS SELECT id,firstname,lastname FROM dbo.person
go

--give permissions to execute the procedure to the thelogin1 user

GRANT EXECUTE ON dbo.getpersons TO thelogin1

--change the current user as thelogin1

EXECUTE AS USER = 'thelogin1'

--check if we are indeed thelogin1 user

SELECT USER

--fails
SELECT * FROM dbo.person

--works

EXECUTE dbo.getpersons

--get back to the dbo user

REVERT;

--see if the user is indeed dbo

SELECT USER

--we change the stored proc to use dynamic sql

ALTER PROCEDURE dbo.getpersons
AS
EXEC('SELECT id,firstname,lastname FROM dbo.person')
GO

--change the current user as thelogin1

EXECUTE AS USER = 'thelogin1'

--check to see if indeed the user is now thelogin1

SELECT USER

--this will fail

EXECUTE dbo.getpersons

--get back to the dbo user
REVERT;

--check to see if we are dbo user

SELECT user

--this will work

ALTER PROCEDURE dbo.getpersons
WITH EXECUTE AS OWNER
AS
EXEC('SELECT id,firstname,lastname FROM dbo.person')
GO

EXECUTE AS USER = 'thelogin1'

--check to see if we are dbo user

SELECT USER

--this will work

EXECUTE dbo.getpersons

Let  me explain what I am doing in this bit of code

• I am creating a database in line 6
• I create a login in line 11
• I create a user-thelogin1 for that login in line 17
• I create a new table in lines 23-30
• Then I populate the table – dbo.person in lines 32
• Then I create a procedure – dbo.getpersons in lines 34-36
• Then I grant permissions to the stored procedure to the user thelogin1
• Then I execute the statements like user – thelogin1
• If I try to access the table directly then I fail. That makes sense.
• Then I execute the stored procedure as user – thelogin1 in lines 55
• Then I revert back to the dbo user
• Then I make a change in lines 67-70 to the stored procedure. I use in the body of stored procedure dynamic sql.
• Then I change the user to thelogin1 in lines 74
• Then I execute the stored procedure in line 82 . This will fail.The dynamic SQL statement causes a break in the ownership chain.
• Then I revert back to line 85 as the dbo user
• In the lines 93-97 I change the stored procedure again (WITH EXECUTE AS OWNER)
• Then I change the user to thelogin1 in lines 99 
• Finally, I execute the stored procedure again in lines 107 and it works

Hope it helps!!!