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;

• This reply was modified 5 years ago by  Lowell.
• This reply was modified 4 years, 6 months ago by  Lowell. Reason: reformatted due to new web design for code tags