Grant limited permissions to create SQL Server views in another schema Part 2
In a previous tip, Granting limited permissions to create views in another schema in SQL Server, I showed how to use the CREATE VIEW permission and the ALTER SCHEMA permission to allow users to create new views using the same schema as the tables. However, I have found that the users can alter and drop tables in this schema which is beyond what I want them to do. A DENY CREATE TABLE doesn't work, so what can I do to prevent them from touching the tables in this way? In this tip I will show you how you can use a DDL trigger to prevent this unneeded access.
If you haven't set up the environment from the previous tip or you've deleted the database, here are the pertinent T-SQL commands to setup the example:
If you have the environment, but no longer have the table, you only need to run the CREATE TABLE statement at the end. With the setup back in place, let's attack the problem.
Because the user has ALTER SCHEMA permissions, we cannot stop them from altering or dropping objects that are contained within the schema simply by the use of permissions. We can prevent them from creating new objects, but not changing existing ones. The catch is to intercept their command and roll it back. We can do so using a DDL trigger at the database level.
If you're familiar with triggers, but not DDL triggers, DDL triggers fire on DDL statements (such as DROP TABLE) as opposed to the DML statements INSERT, UPDATE, or DELETE. DDL triggers can be at the database or server level, depending on at what level the event is we want to intercept. ALTER TABLE and CREATE TABLE are at the database level, so we'll want to create the DDL trigger at that level.
Now we don't want to block everyone from altering or dropping tables. After all, if a DBA needs to, we don't want them blocked. The key is to use something that differentiates the users we want to block from others. Since we're assigning the limited rights to a role appropriately called LimitedCreatorRights, that would make a good way to filter out and trap commands. We can use the IS_MEMBER() function and test it against the user executing the ALTER TABLE or DROP TABLE command. When the DDL trigger fires, it actually fires in the context of that user, so if we use IS_MEMBER() we can check to see if the user is a member of LimitedCreatorRights. If the user is, we'll issue a ROLLBACK TRANSACTION, thereby blocking the change to the table.
Here's the DDL trigger:
Note that we specified two events to fire on: DROP_TABLE and ALTER_TABLE. Should either of these commands be executed within the database, they will be intercepted by the trigger. The trigger will then do a check against the role. If the user is a member of the LimitedCreatorRights role, the transaction will be rolled back and the ALTER TABLE or DROP TABLE statement will be effectively blocked.
With this DDL trigger in place, the following commands will now fail and return the error that we have specified. The user will also get a message that the transaction ended in a trigger, which means we know it was the trigger that did the job.
In this tip we have seen how to use DDL triggers to block unwanted commands from occurring and also seen how to use the IS_MEMBER function. Use these features to further secure your database environment, but still allow users to do their jobs.
Last Updated: 2009-07-27
About the author
View all my tips