September 9, 2010 at 12:53 pm
Need help in creating a trigger which does the follwoing.
when table EMP_SSN is created across any databases in the server it has to DENY drop permissions for that table for all users in the server except user "ABC"
September 9, 2010 at 1:08 pm
You'll need to create a DDL Trigger.
CREATE TRIGGER YourTrigger
ON ALL SERVER
AFTER DROP_TABLE
AS
<Your code here.>
You'll need to use the EVENTDATA function to get the command issued to see if the table being dropped is the one that you're interested in. The event data also includes the UserName, which you can use for your exception.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 10, 2010 at 7:18 am
I understand this trigger fires on the whole server but do i need to create this on master database so that i can restrict people from dropping/updating the tables i am looking for.
September 10, 2010 at 8:46 am
Is it possible to create a trigger that will DENY few users to drop/insert/update/alter only on few tables across the server?
I have TableA,TableB,TableC on all databases in the server for which i want to restrcit above given previalges for UserA and UserB
September 10, 2010 at 8:49 am
Tara i saw the other thread , and yes it's possible, but you need to decide on some way to identify the table...specific name, maybe from a table in master, or a naming convention.
this works perfectly on my machine: i tested this as sa/dbowner, as well as a plain old role with ddl_admin; it stops everyone, but of course a sysadmin could disable or drop the trigger...you could put in additiona exemptions like i did witht eh trigger in this thread...allowing it for specific users or from specific machines.
--DROP TRIGGER [TR_DB_NO_DROPPING_OBJECTS] ON ALL SERVER
CREATE TRIGGER [TR_DB_NO_DROPPING_OBJECTS]
on ALL SERVER
FOR
DROP_PROCEDURE,DROP_FUNCTION,DROP_VIEW,DROP_TABLE
AS
BEGIN
SET NOCOUNT ON
--declare some variables
DECLARE
@eventData XML,
@DATABASENAME SYSNAME,
@EVENTDATE DATETIME,
@USERNAME SYSNAME,
@SYSTEMUSER VARCHAR(128),
@CURRENTUSER VARCHAR(128),
@ORIGINALUSER VARCHAR(128),
@HOSTNAME VARCHAR(128),
@APPLICATIONNAME VARCHAR(128),
@SCHEMANAME SYSNAME,
@OBJECTNAME SYSNAME,
@OBJECTTYPE SYSNAME,
@EVENTTYPE VARCHAR(128),
@COMMANDTEXT VARCHAR(max),
@NAMEFORDEFINITION VARCHAR(261)
--Load Variables from the xml
SET @eventData = eventdata()
SELECT
@DATABASENAME = db_name(),
@EVENTDATE = GETDATE(),
@USERNAME = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@SYSTEMUSER = SUSER_SNAME(),
@CURRENTUSER = CURRENT_USER,
@ORIGINALUSER = ORIGINAL_LOGIN(),
@HOSTNAME = HOST_NAME(),
@APPLICATIONNAME = APP_NAME(),
@SCHEMANAME = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
@OBJECTNAME = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@OBJECTTYPE = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),
@COMMANDTEXT = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),
@EVENTTYPE = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')
--print @OBJECTTYPE
IF @OBJECTTYPE ='TABLE'
BEGIN
IF @OBJECTNAME LIKE 'SEC%'
OR @OBJECTNAME ='EMP_SSN'
RAISERROR('SEC tables are protected and cannot be dropped', 16, 1)
--prevent the drop
ROLLBACK
END
IF @OBJECTTYPE ='VIEW'
BEGIN
IF @OBJECTNAME LIKE 'VW_%'
RAISERROR('VIEWS starting with [VW_] are protected and cannot be dropped', 16, 1)
--prevent the drop
ROLLBACK
END
END
then i did this:
create table secbananas(bananaid int,bananname varchar(30) )
drop table secbananas
Msg 50000, Level 16, State 1, Procedure TR_DB_NO_DROPPING_OBJECTS, Line 46
SEC tables are protected and cannot be dropped
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.
Lowell
September 10, 2010 at 8:58 am
Lowell
This looks exciting to me and would like to try but few questions..
1. Yes i have 3 tables only across all servers
2. i have 2 win groups to restrict this access others should be able to drop without problem.
3. where do i create this trigger? (is it in master)
September 10, 2010 at 9:21 am
Tara-1044200 (9/10/2010)
LowellThis looks exciting to me and would like to try but few questions..
1. Yes i have 3 tables only across all servers
2. i have 2 win groups to restrict this access others should be able to drop without problem.
you'll need to define this better...a domain\groupname would not be available to the trigger;
you can make the trigger test if the user is sysadmin or not, or maybe use a dynamic SQL to see if the user is part of databasename.sys.database_principals, joined to get the roles from within the database...but without specifics....
3. where do i create this trigger? (is it in master)
the ON ALL SERVER puts it in master, but it would make no difference what database you were currently in. ON ALL SERVER is not database specific.
Lowell
September 10, 2010 at 9:32 am
actually i want to handle this in reverse like say i want to restrict access to eevry one except one win group, how do i filter that group or is it best to list all users of that group?
September 10, 2010 at 9:43 am
Tara-1044200 (9/10/2010)
actually i want to handle this in reverse like say i want to restrict access to eevry one except one win group, how do i filter that group or is it best to list all users of that group?
the trigger currently prohibits everyone. we need the role/username the person deleting belongs to to decide if an exception is warranted.
i don't think you can discover that yourdomain\bob is in yourdomain\groupname; I'm thinking you have to find the database role in the database that that user belongs to, and go from there.
something like this finds the user and the roles they belong to:
select p.name as RoleName, m.name as UserName from sys.database_role_members mp
inner join sys.database_principals m on mp.member_principal_id = m.principal_id
inner join sys.database_principals p on mp.role_principal_id = p.principal_id
can you give a specific, concrete example? username and a role?
Lowell
September 10, 2010 at 9:51 am
Looks like this trigger prohibits dropping all tables and all users and it is raising error only for the given users.
I want to prohibit everyone except User1 and User2 only for 3 tables.
September 10, 2010 at 9:57 am
Tara-1044200 (9/10/2010)
Looks like this trigger prohibits dropping all tables and all users and it is raising error only for the given users.I want to prohibit everyone except User1 and User2 only for 3 tables.
that's not what you said before. don't change the target!
look at the other thread i responded to; it has an example allowing two specific users, andeven then only two specific machine names
IF @OBJECTTYPE ='TABLE'
BEGIN
--limit to the protected tables
IF @OBJECTNAME LIKE 'SEC%'
OR @OBJECTNAME ='EMP_SSN'
BEGIN
--skip if you aret he "right" user.
IF SUSER_SNAME() NOT IN('sa','USER1','USER2','USER3')
BEGIN
RAISERROR('SEC tables are protected and cannot be dropped', 16, 1)
--prevent the drop
ROLLBACK
END
END
END
Lowell
September 10, 2010 at 11:32 am
I am sorry if i have confused you.
from the above script does it mean that users listed in NOT IN cluse can drop the tables.
Actually my goal is to prohibit all users except 2
September 10, 2010 at 11:37 am
Tara-1044200 (9/10/2010)
I am sorry if i have confused you.from the above script does it mean that users listed in NOT IN cluse can drop the tables.
Actually my goal is to prohibit all users except 2
yes that's correct. this only allows that select group to drop...EVERYONE else would be blocked.
Lowell
September 10, 2010 at 12:14 pm
great thank a lot Lowell.
finally can i use triiger for prohibintg INSERT,UPDATE and DELETE also ?
September 10, 2010 at 12:31 pm
Tara-1044200 (9/10/2010)
great thank a lot Lowell.finally can i use triiger for prohibintg INSERT,UPDATE and DELETE also ?
not a server wide trigger No.
triggers for ALL SERVER can only track LOGON or DDL operations, not DML operations like insert/update/delete.
for that, you need a different DATABASE trigger in each database to be protected. at the database level, you can audit/track/prevent DML operations, or you can just put a trigger on the specific tables to be protected as well.
dumb question: why not send out an email stating "touch the SSN table and you get fired, we are watching you", andn then just check to see if it gets touched with a DML trace? why go through all the hassles of writing a suite of triggers? if you can't trust the trustees, just make the table read only with a trigger or something.
Lowell
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply