USE
GO
CREATE
LOGIN SIMPLEPWD WITH PASSWORD = 'SIMPLEPWD'
GO
ALTER
LOGIN Topaz WITH PASSWORD = 'Bo3EXNWJ#N6ndg5',
CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
ALTER
-- *** Authorization ***
==
*********************
-- *** Server Roles
-- Add login Topaz to the sysadmin role
sp_addsrvrolemember
'Topaz', 'sysadmin'
-- Get a list of all server roles
EXEC
sp_helpsrvrole
-- Get the description of a single server role
EXEC
-- Get list of members of the diskadmin role
EXEC
-- *** Database roles
/*** Execution Context
**********************/
-- Execution Context Sample
-- *** Create the database for this demo ***
USE
GO
CREATE
LOGIN UserProc WITH password = 'Y&2!@37z#F!l1zB'
CREATE
LOGIN UserTable WITH password = 'Y&2!@37z#F!l1zB'
CREATE
LOGIN RealUser WITH password = 'Y&2!@37z#F!l1zB'
GO
CREATE
GO
USE
GO
-- Create the users
CREATE
CREATE
CREATE
GO
-- Create the schemas
CREATE
GO
CREATE
GO
-- Create a table and a proc in different schemas to ensure that
-- there is no ownerhship chaining.
CREATE
TABLE SchemaUserTable.Vendor (ID int, name varchar(50), state char(2), phno char(12))
GO
INSERT
INTO SchemaUserTable.Vendor VALUES (1,'Vendor1','AK','123-345-1232')
INSERT
INTO SchemaUserTable.Vendor VALUES (2,'Vendor2','WA','454-765-3233')
INSERT
INTO SchemaUserTable.Vendor VALUES (3,'Vendor3','OR','345-776-3433')
INSERT
INTO SchemaUserTable.Vendor VALUES (4,'Vendor4','AK','232-454-5654')
INSERT
INTO SchemaUserTable.Vendor VALUES (5,'Vendor5','OR','454-545-5654')
INSERT
INTO SchemaUserTable.Vendor VALUES (6,'Vendor6','HI','232-655-1232')
INSERT
INTO SchemaUserTable.Vendor VALUES (7,'Vendor7','HI','453-454-1232')
INSERT
INTO SchemaUserTable.Vendor VALUES (8,'Vendor8','WA','555-654-1232')
INSERT
INTO SchemaUserTable.Vendor VALUES (9,'Vendor9','AK','555-345-1232')
GO
-- Create the stored procedure in SchemaUserProc
CREATE
PROC SchemaUserProc.VendorAccessProc @state char(2)
AS
SELECT * FROM SchemaUserTable.Vendor WHERE state = @state
GO
-- Grant permissions on the stored procedure
GRANT
GO
-- *** Ready to test execution context ***
-- Now try and access the proc as RealUser
EXECUTE
AS user = 'RealUser'
EXEC
SchemaUserProc.VendorAccessProc 'AK'
-- The permission is denied on the underlying table.
-- But do not want to have to grant permissions to all callers on the underlying table.
-- Instead have the proc run as UserTable, which has SELECT permissions on the table,
-- since that user owns the schema it is in.
REVERT
-- Alter the procedure with UserTable as the execution context
-- ALTER preserves the permissions on the object
ALTER
PROC SchemaUserProc.VendorAccessProc @state char(2)
WITH
EXECUTE AS 'UserTable'
AS
SELECT * FROM SchemaUserTable.Vendor WHERE state = @state
GO
-- Now try and execute the proc as RealUser
EXECUTE
AS user = 'RealUser'
EXEC
SchemaUserProc.VendorAccessProc 'AK'
REVERT
-- Clean up
USE
GO
DROP
DROP
DROP
DROP
/*** Metadata Security
**********************/
USE
CREATE
LOGIN User1 WITH password = 'mC%929N*!LkDvuJ'
-- Use the database of your choice
USE
CREATE
EXECUTE
AS LOGIN = 'User1'
SELECT
-- Because the user has no permissions in the database, no rows are returned
* FROM sys.objects
REVERT
-- Give User1 rights to a table and stored procedure
GRANT
GRANT
-- Execute again as User1
EXECUTE
AS LOGIN = 'User1'
SELECT
-- Attempt to drop a table in the database
-- Throws an error that doesn't confirm the existence of the table
* FROM sys.objects
DROP
REVERT
-- Clean up
DROP
GO
USE
DROP
LOGIN User1 master USER User1 TABLE Production.Product EXECUTE ON dbo.uspGetBillOfMaterials TO User1 SELECT ON Person.Contact TO User1 USER User1 AdventureWorks master DATABASE ExecuteContextDB LOGIN RealUser LOGIN UserTable LOGIN UserProc master EXECUTE ON SchemaUserProc.VendorAccessProc TO RealUser SCHEMA SchemaUserTable AUTHORIZATION UserTable SCHEMA SchemaUserProc AUTHORIZATION UserProc USER RealUser USER UserTable USER UserProc ExecuteContextDB DATABASE ExecuteContextDB master sp_helpsrvrolemember securityadmin sp_helpsrvrole securityadmin LOGIN Topaz WITH PASSWORD = 'Bo3EXNWJ#N6ndg5' UNLOCK
-- Unlock after too many unsuccessful login attempts