Grant limited permissions to create SQL views in another schema Part 2

Grant limited permissions to create SQL Server views in another schema Part 2

By: K. Brian Kelley | Updated: 2009-07-27 | Comments (1) | Related: > Security

Problem

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.

Solution

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:

CREATE DATABASE MSSQLTips; 
GO 

USE MSSQLTips; 
GO 

CREATE ROLE LimitedCreatorRights; 
GO 

GRANT CREATE VIEW TO LimitedCreatorRights; 
GO 

GRANT SELECT ON SCHEMA::dbo TO LimitedCreatorRights; 
GO 

CREATE USER TestUser WITHOUT LOGIN; 
GO

GRANT ALTER ON SCHEMA::dbo TO LimitedCreatorRights; GO

EXEC sp_addrolemember 'LimitedCreatorRights', 'TestUser'; GO CREATE TABLE dbo.ATest (TestID INT); GO

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:

-- This trigger only fires on alter and drop table 
-- events. It then checks to see if the user doing the  
-- dropping is a member of the LimitedCreatorRights role.  
CREATE TRIGGER trig_db_BlockAlterDropTable 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
BEGIN 
   IF IS_MEMBER('LimitedCreatorRights') = 1 
   BEGIN 
       PRINT 'You are not authorized to alter or drop a table.'; 
       ROLLBACK TRANSACTION; 
   END; 
END; 
GO

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.

EXECUTE AS USER = 'TestUser'; 
GO 

ALTER TABLE dbo.ATest 
ADD AnotherID INT NULL; 
GO 

REVERT; 
GO

-- and

EXECUTE AS USER = 'TestUser'; GO DROP TABLE dbo.ATest; GO REVERT; GO

Summary

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.

Next Steps

Take the time to learn more about DDL triggers and the IS_MEMBER function.
Download the scripts

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2009-07-27

Comments For This Article

Wednesday, November 14, 2012 - 4:42:30 PM - Rahul Patel

Hi Brian,

I am facing some isue, when I am creating a trigger for developer.

I have created Developer role in the database, which has below functionality.

deny create table to Developer

deny create defult to Developer

deny create rule to Developer

deny take ownership on schema::dbo to Developer

I have granted this role to user on the database. User has requested for db_ddladmin permission for 1 day. Now user has "Developer" and "db_ddladmin" permission on the database. We are not revoking Developer role before granting db_ddladmin permission. We have 1000+ databases and we need to build a solution, which can cover this kind of sitiuation.

I have created DDLSafety trigger on the database, which has below functionality.

FOR DROP_TABLE, ALTER_TABLE, ALTER_INDEX, DROP_INDEX, CREATE_TABLE, CREATE_INDEX

In trigger, I have added below line.

if (is_rolemember('Developer',user_name()) = 1) and (is_rolemember('db_ddladmin',user_name()) = 1)

begin

Commit Transactin

end

User has tried to drop and create the table, but he can not do it.

Please advise, how to solve this kind of problem

Thanks

Rahul