RSS

Trigger to Prevent any user to Create Table on Database

03 Oct

Event Can prevent happend on your database
CREATE_TABLE
CREATE_INDEX
CREATE_FUNCTION

Example

USE
DBNAME;
GO
CREATE TRIGGER Prevent_Create

ON
DATABASE FOR CREATE_TABLE
AS
PRINT ‘CREATE TABLE Issued.’
SELECT EVENTDATA().value
(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’)
RAISERROR (‘New tables cannot be created in this database.’, 16, 1)
ROLLBACK;
GO
–Test the trigger.
CREATE TABLE T2 (C1 int);
GO
–Drop the trigger.
DROP TRIGGER Prevent_Create_Table ON DATABASE;
GO

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s