When Builtin Database Roles Just Don’t Make the Grade

How to create a DDLAdmin-ish database role

A client of mine had a request to create a database role that allowed users to Create, Modify, and Drop Stored Procedures and Views.  Sounds simple enough, right?  Right… (the ellipsis implies sarcasm here).  Part of this request was that no other schema changes should be allowed.  Luckily the users requiring these permissions are developers and not DBAs.  They don’t really care about triggers, synonyms, etc so I find my job a little easier.  The only major objects I need to ensure they can’t touch are the tables; basically everything else is fair-game.

Setup the Custom Database Role

Quickly defining custom Database Roles once for existing and not-yet-created objects is a feature within SQL Server that makes it shine over some of its counterpart RDBMSs *COUGH* Oracle *COUGH* because you don’t have to go through all the work of defining every single object you want to modify permissions on annually or when schema changes are made.  This is especially useful when new objects will be added to the database long after these requirements fade from memory.  No additional work configuring permissions is required so long as the role is defined properly.

First, we need to create a custom database role to grant these permissions against.  Once done, we’ll just add users to this role and let them at the DB.  Let’s start with creating the role itself:

CREATE ROLE [db_dbdeveloper]
GO

Now let’s grant the ability to Create Stored Procedures and Views:

GRANT CREATE PROCEDURE TO [db_dbdeveloper]
GRANT CREATE VIEW TO [db_dbdeveloper]

One thing we need to ensure is that the developers can actually view the definitions of any existing or newly created objects:

GRANT VIEW DEFINITION TO [db_dbdeveloper]

Now let’s ensure they can actually run stored procedures:

GRANT EXECUTE TO [db_dbdeveloper]
GO

Looks good right?  Let’s check.  Add my users to the database role; check.  Now, let’s create a test view as one of these users:

CREATE VIEW myView AS
  SELECT ID, Data
  FROM TestTable1
  WHERE ID < 1000

Execute (F5)…

Msg 2760, Level 16, State 1, Procedure myView, Line 1
The specified schema name "dbo" either does not exist or you do not have permission to use it.

What!?!?  I granted CREATE VIEW permissions!  In order to get the CREATE VIEW command to actually run, we need to also allow this role to ALTER the dbo schema, per MSDN CREATE VIEW (Transact-SQL) article:

Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.

“and ALTER permission on the schema” being the key words here.  Sounds simple enough, let’s grant that too, shall we?

GRANT ALTER ON SCHEMA::[dbo] TO [db_dbdeveloper]
GO

Can we create the view now?  Yes!  Success!

What else do ALTER permissions grant?  I wish I fully knew to be honest, as this is where either Microsoft or my Google Search skills truly fail because I cannot find an article anywhere outlining exactly what permissions are granted by way of the GRANT ALTER ON SCHEMA command.  What I can tell you though, is it allows you the ability to ALTER and DROP objects, but not CREATE them.  Objects is italicized for a reason as we’ll soon find out.

Let’s test our custom role; can I drop my new View?  Yes, success!  What about say… a table?  Yes…  YES!?!?!  That’s not what I want; the client specifically requested they did not want users in this role to be able to drop/modify objects other than Views or Stored Procedures.  Grr….

I think this is where the folks designing the permissions model of SQL Server decided to take a coffee break.  We can GRANT CREATE permissions, but there isn’t a single DROP permission we can grant against a schema or in general.  DROPS are part of the ALTER grant and frankly they’re not specific to certain objects, so now we find that we’ve provided too much permission in an attempt to provide the minimum/proper amount.

We could address this as if it were Oracle, which requires we grant appropriate permissions to each object explicitly.  To emulate that model we would need to explicitly DENY DROP and ALTER permissions on every table within the database.  The issue here is you can be certain someone with the proper CREATE TABLE permissions will create a table at some point in the future but forget to set the proper DENY(ies) for the db_dbdeveloper role…  …and chances are it’ll also be you, the DBA.

DDL Triggers to the rescue!

Looks like our easy job just got a little harder.  So, what other options do we have if we’re not going to explicitly set DENY permissions on every table within the database?  I hope you’re thinking DDL Trigger, because frankly that’s the heading to this section and that should have been a big hint.  Thankfully DDL Triggers on the database are not that complex to create, and are perfectly suited to fix this over-grant of permissions.

DDL triggers fire when a DDL statement is issued.  In our scenario, we want to block DROP TABLE (or ALTER TABLE) commands from getting executed by  most users.  We don’t want to block these statements carte blanche, so let’s make it so only users in the db_owner role can drop or alter tables.  To make this happen, we need to look at the IS_MEMBER function, which identifies if the user currently executing the statement is part of the role being passed to it.

Here’s a basic trigger we could easily setup and use:

CREATE TRIGGER NON_DB_OWNER_DROP_TABLE
ON DATABASE
FOR DROP_TABLE
AS
IF ISNULL((SELECT IS_MEMBER('db_owner')), 0) < 1
BEGIN
    ROLLBACK
    RAISERROR('You do not have permission to drop tables.', 16, 20)
END
GO

For all intents and purposes, we can make a similar trigger for the ALTER_TABLE event and call it a day, but I dislike generic errors like this as I feel they can lead to confusion.  The type of error I’m interested in will display the table name having the ALTER or DROP statement being run against it so no confusion exists about what is occurring   To do this we utilize the EVENTDATA function.  This function captures the entire command getting called, but thankfully we can easily capture the object name (aka table name) with minimal fuss.

The updated trigger is all of two lines bigger, but it kicks out much nicer errors that tell the user they don’t have permissions to drop/alter the specific table they issued the command against:

CREATE TRIGGER NON_DB_OWNER_DROP_TABLE
ON DATABASE
FOR DROP_TABLE
AS
-- If User executing Command is not in db_owner command, rollback the transaction
IF ISNULL((SELECT IS_MEMBER('db_owner')), 0) < 1
BEGIN
   DECLARE @Tablename NVARCHAR(256)
   -- Capture Table Name
   SELECT @Tablename = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')

   ROLLBACK
   RAISERROR('Cannot drop the table ''%s'', because you do not have permission.', 14, 20, @Tablename)
END
GO

So there you have it.  A custom DB role and a pair of triggers will get us what the client requested.  Not as clean as I was hoping for, but I’d rather grant “enough” permissions over “too much.”  The best part of this is that any new tables added to the database are setup so only db_owners can drop them as is outlined by the requirements.  Makes life easier when you have the memory of a fish… like me.  The entire code solution can be found below.

John

References (in order of appearance):

1. http://msdn.microsoft.com/en-us/library/ms187956.aspx
2. http://msdn.microsoft.com/en-us/library/ms186271.aspx
3. http://msdn.microsoft.com/en-us/library/ms187909.aspx

FULL Code:

-- Create new custom Database Role that allows for SP and View Creation/Modification, but not for Table Modification
CREATE ROLE [db_dbdeveloper]
GO
-- Create Objects
GRANT CREATE PROCEDURE TO [db_dbdeveloper]
GRANT CREATE VIEW TO [db_dbdeveloper]
-- View Permissions
GRANT VIEW DEFINITION TO [db_dbdeveloper]
-- SP Permissions
GRANT EXECUTE TO [db_dbdeveloper]
-- Required ALTER permissions
GRANT ALTER ON SCHEMA::[dbo] TO [db_dbdeveloper]
GO

-- Create Triggers that prevent dropping/altering a table from anyone aside from a user in db_owner role
CREATE TRIGGER NON_DB_OWNER_DROP_TABLE
ON DATABASE
FOR DROP_TABLE
AS 
-- If User executing Command is not in db_owner command, rollback the transaction
IF ISNULL((SELECT IS_MEMBER('db_owner')), 0) < 1
BEGIN
DECLARE @Tablename NVARCHAR(256)
   -- Capture Table Name
   SELECT @Tablename = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')

   ROLLBACK
   RAISERROR('Cannot drop the table ''%s'', because you do not have permission.', 14, 20, @Tablename)
END
GO

CREATE TRIGGER NON_DB_OWNER_ALTER_TABLE
ON DATABASE
FOR ALTER_TABLE
AS
-- If User executing Command is not in db_owner command, rollback the transaction
IF ISNULL((SELECT IS_MEMBER('db_owner')), 0) < 1
BEGIN
   DECLARE @Tablename NVARCHAR(256)
   -- Capture Table Name
   SELECT @Tablename = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')

   RAISERROR('Cannot alter the table ''%s'', because you do not have permission.', 14, 20, @Tablename)
   ROLLBACK
END
GO

Advertisement