Laura_SqlNovice (1/24/2012)
So Lowell I should make the user db_ddladmin and then create DDL triggers to stop users from altering, dropping objects?
well Laura i threw some time at this, as it was interesting for me;
here's what i have so far;
i created my version of a DDL trigger, a role, and a user. i tested as myself and as my user ClarkKent, and i could create DDL items, but ClarkKent could only do Views, as expected.
test this and see if it's going to work for you as a model;
don't forget to drop your trigger, and of course test this on dev, not production.
test this line by line so you have a full understanding of it; this is more of a script to provide a prototype, and not a paste-into-production solution.
I was a little heavy handed with permissions, as well, just throwing out built in roles sufficient for proof of concept.
here's my example:
--#################################################################################################
--Objective: create a role that allows the creation of Views, but not allowed to change other objects(tables/procs/functions/triggers)
--#################################################################################################
--Our Trigger to limit our role:
CREATE TRIGGER TR_Limit_Role_ViewMaker_DDL_Events
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
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),
@CMD VARCHAR(500)
--Load Variables from the xml
SET @eventData = eventdata()
--my standard variables for a DDL trigger are above,
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)')
--all my variables are now avaialble above, use what is needed for the specific requirement below
--now lets test if this user in the viewmaker role
IF IS_MEMBER ('ViewMaker') = 1
BEGIN
--if you are in this role, and doing any DDL event except CREATE_VIEW ALTER_VIEW DROP_VIEW then rollback
IF @EVENTTYPE NOT IN('CREATE_VIEW', 'ALTER_VIEW', 'DROP_VIEW')
BEGIN
RAISERROR ('DB Trigger "TR_Limit_ViewMaker_DDL_Events" does not allow any DDL Events except those related to Views.', 16, 1)
ROLLBACK
END
END
END --trigger
GO
--enable the trigger
ENABLE TRIGGER TR_Limit_Role_ViewMaker_DDL_Events ON DATABASE
GO
--#################################################################################################
--Create our role for proof of concept
--#################################################################################################
CREATE ROLE ViewMaker
--need to see the table and object structures in order to create the views.
GRANT VIEW DEFINITION TO ViewMaker;
--need to at least SELECT permissions in order to test the views and confirm the data is correct
--assuming all objects, and not a specific schema, i'll suggest db_datareader
EXEC sp_addrolemember N'db_datareader', N'ViewMaker';
--our role needs the ability to CREATE/ALTER/DROP views, but there's secuirtly level granular enough for that...
--so grant ALTER on everything,a nd use the trigger to prevent everything except VIEW related operations
EXEC sp_addrolemember N'db_ddladmin', N'ViewMaker';
GO
--create a test user.
CREATE USER ClarkKent WITHOUT LOGIN;
--Add that user to our special role
EXEC sp_addrolemember N'ViewMaker',N'ClarkKent';
--change from superman(sa?) to Clark Kent
EXECUTE AS USER = 'ClarkKent';
--who am i? ClarkKent!
select USER_NAME()
--returns ClarkKent.
select IS_MEMBER('ViewMaker') --confirm if ClarkKent is a member of our role.
--can ClarkKent select from the sys.tables/sp_help etc?
select * from sys.tables --yes
--can ClarkKent select from those tables?
select * from TBSTATE --yes..if your database has that table of course
--you can test that he does not have insert/update/delete, nore execute on the procedures and functions.
GO
--prove ClarkKent can create a view
CREATE VIEW testView
AS
SELECT * FROM TBSTATE
GO
--sweet! the above worked
--can ClarkKent Create a procedure?
--for users who are too lazy to type "SELECT * FROM"
CREATE procedure sp_show
--USAGE: sp_show gmact
@TblName varchar(128)
--WITH ENCRYPTION
As
Begin
exec('Select * from ' + @TblName)
End
GO
--ouch! the above gets stopped with this error:
/*
Msg 50000, Level 16, State 1, Procedure TR_Limit_Role_ViewMaker_DDL_Events, Line 51
DB Trigger "TR_Limit_ViewMaker_DDL_Events" does not allow any DDL Events except those related to Views.
Msg 3609, Level 16, State 2, Procedure sp_show, Line 7
The transaction ended in the trigger. The batch has been aborted.
*/
--Prove ClarkKent can drop the View he created.
DROP VIEW TestView
GO
--change back into superman/sa
REVERT;
GO
--clean up after our examples
DISABLE TRIGGER TR_Limit_Role_ViewMaker_DDL_Events ON DATABASE;
GO
DROP TRIGGER TR_Limit_Role_ViewMaker_DDL_Events ON DATABASE;
GO
DROP USER ClarkKent;
GO
DROP ROLE ViewMaker;
Lowell