April 13, 2009 at 12:24 pm
Can we put restriction on dropping a table in the database with the same SQL acccount who has system admin role.
For example. There is an account called ABC which has System Admin role permission on the database. This account has to dropped some tables because of the nature of the application but I need to setup some kind of security that this account should not dropped some main tables.
Can I do that in SQL Server 2005.
April 13, 2009 at 12:30 pm
are you saying that your application id has sysadmin role ?
:Whistling:
you can setup a ddl trigger in 2005 to limit ddl activities.
April 13, 2009 at 1:01 pm
Like mentioned above, you could log any attempt to drop tables and completely restrict dropping of tables. It depends on what your requirements are.
April 13, 2009 at 1:10 pm
here's an example we tested to restrict some non-sa (but dbo) actions.
In your case you'll need to check for event type "DROP_TABLE".
/****** Object: DdlTrigger [ddlDatabaseTriggerNonSA] Script Date: 10/16/2008 13:36:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Restrict DDL to Func and Sproc for non-sa
CREATE TRIGGER [ddlDatabaseTriggerNonSA]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
-- Restrict DDL to Func and Sproc for non-sa
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
declare @db_id int;
declare @db_name sysname;
select @db_id = db_id();
select @db_name = db_name();
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
IF @object IS NOT NULL
PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object;
ELSE
PRINT ' ' + @eventType + ' - ' + @schema;
IF @eventType IS NULL
PRINT CONVERT(nvarchar(max), @data);
IF NOT ( @eventType LIKE '%function%'
OR @eventType LIKE '%procedure%' )
BEGIN
IF IS_SRVROLEMEMBER ('sysadmin',SUser_SName()) = 0
BEGIN
RAISERROR ('You are not entitled to perform this modification [d]' , 1,1,@eventType) WITH log
ROLLBACK TRAN
END
END
INSERT [admin].[dbo].[T_DBADatabaseLog]
([DatabaseID],
[DatabaseName],
[PostTime],
[DatabaseUser],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
VALUES
(@db_id,
@db_name,
GETDATE(),
CONVERT(sysname, CURRENT_USER),
@eventType,
CONVERT(sysname, @schema),
CONVERT(sysname, @object),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
);
END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [ddlDatabaseTriggerNonSA] ON DATABASE
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database trigger to Restrict DDL to Func and Sproc for non-sa.' , @level0type=N'TRIGGER',@level0name=N'ddlDatabaseTriggerNonSA'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 13, 2009 at 1:23 pm
Be aware that if you set up a DDL trigger like this, and you don't restrict it to this user, no one will be able to drop a trigger. Possibly you can disable it, drop a trigger, and enable it, but it's something to be aware of.
April 13, 2009 at 5:01 pm
Thanks guys, Great help. I modified like this. Just for testing purpose, i am trying to drop TT and TT! tables and getting the error. which is good.
ALTER trigger [TR_CaptureEvents]
on database
for
alter_view,drop_view,
create_user,alter_user,drop_user,
alter_table,drop_table,
alter_index,drop_index,
alter_procedure, drop_procedure
as
set nocount on
DECLARE @data xml
DECLARE @eventType sysname;
DECLARE @schema sysname;
DECLARE @object sysname;
set @data = EVENTDATA()
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
IF @EVENTTYPE = 'DROP_TABLE'
BEGIN
IF @OBJECT = 'TT' OR @OBJECT LIKE 'TT1'
BEGIN
RAISERROR ('You are not entitled to perform this modification [d]' , 1,1,@eventType) WITH log
ROLLBACK TRAN
END
END
insert into SC_ADMINDB.dbo.eventslog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
values(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [TR_CaptureEvents] ON DATABASE
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply