TSQL: Database Permission Exercise for 70-462

Here’s the TSQL for an exercise, involving database permissions, from the 70-462. Explanatory comments are included.

CREATE DATABASE Saturn;
GO
 
USE Saturn;
GO
CREATE ROLE Moon_Table_Editors;
GO
 
-- Create tables
CREATE TABLE Mimas 
(
	id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
	column1 VARCHAR(100) NOT NULL
);
GO
CREATE TABLE Thethys 
(
	id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
	column1 VARCHAR(100) NOT NULL
);
GO
CREATE TABLE Hyperion
(
	id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
	column1 VARCHAR(100) NOT NULL
);
GO
 
CREATE SCHEMA Orbits;
GO
 
-- Add permissions to the tole
GRANT SELECT, REFERENCES, INSERT, UPDATE, DELETE, VIEW DEFINITION ON SCHEMA::Orbits TO Moon_Table_Editors;
GO
 
-- Check these permissions
SELECT dp.name, pm.[permission_name]
FROM sys.database_principals dp
INNER JOIN sys.database_permissions pm 
	ON pm.grantee_principal_id = dp.principal_id 
WHERE dp.[name] = 'Moon_Table_Editors';
 
-- Now we want to modify these permissions to include only SELECT and REFERENCES
REVOKE INSERT, UPDATE, DELETE, VIEW DEFINITION ON SCHEMA::Orbits TO Moon_Table_Editors;
 
-- Check this has worked...
SELECT dp.name, pm.[permission_name]
FROM sys.database_principals dp
INNER JOIN sys.database_permissions pm 
	ON pm.grantee_principal_id = dp.principal_id 
WHERE dp.[name] = 'Moon_Table_Editors';
 
-- Now create a new role  called Moon_Table_Designers
CREATE ROLE Moon_Table_Designers;
GO
 
-- This role should be able to create tables in the Orbits schema only
GRANT CREATE TABLE TO Moon_Table_Designers;
GO
GRANT ALTER ON SCHEMA::Orbits TO Moon_Table_Designers;
GO
 
-- Create a user to test this
CREATE USER [user1] FOR LOGIN [user1];
GO
EXEC sp_addrolemember 'Moon_Table_Designers', 'user1';
GO
 
-- Impersonate this user
EXECUTE AS USER = 'user1';
GO
 
CREATE TABLE dbo.ShouldNotCreate
(
	id INTEGER NOT NULL PRIMARY KEY
);
GO
 
CREATE TABLE Orbits.TableShouldCreate
(
	id INTEGER NOT NULL PRIMARY KEY
);
GO
 
REVERT;
GO
 
-- Check which tables exist
SELECT *
FROM sys.tables
WHERE [name] IN ('ShouldNotCreate', 'TableShouldCreate');
GO

2 Comments

  1. M Yoakam says:

    I guess it is implied that when granting the ability to create tables you should also grant the ability to alter and drop them as well. I tried this and unless I did something wrong I could also drop the table. It seems all of the solutions I’ve seen for this exercise are handled this way but I was hoping to see a solution that ONLY allows for CREATING tables on the Orbits schema.

  2. Rhys says:

    Hi There,

    What did you do? I think you should be able to achieve this but unfortunately I don’t have a windows machine to test this out on.

    You can use the sys.fn_my_permissions to see which permissions are assigned to the current login.

    If it’s not possible to achieve what you want through permissions then you can always look at DDL Triggers to prevent certain actions.

    Cheers,

    Rhys

Leave a Reply