January 24, 2012 at 2:13 pm
Hi guys help with DBA work for a test server...
What I need to do is create a new db role that can only create, alter and drop VIEWs. That role will be assigned to a user account that I will create... So the user account should be able to create, alter and drop views.
Please let me know how to do this in SQL Server 2008.
Thanks,
Laura
January 24, 2012 at 2:46 pm
I tried doing this
sp_addrole 'db_alterview', 'dbo'
--Add member to role
sp_addrolemember 'db_alterview', 'testUser'
--grant permission to create view
GRANT CREATE VIEW TO [db_alterview];
GRANT SELECT ON SCHEMA::[dbo] TO [db_alterview];
I was able to select from table using the testUser but when I tried creating view this is the error I got:
Msg 2760, Level 16, State 1, Procedure vw_test, Line 3
The specified schema name "dbo" either does not exist or you do not have permission to use it.
Not sure what I am missing... I want the testUser to be the member of the db_alterview ROLE and that role should be able to create, alter and drop VIEWs...
Thanks,
Laura
January 24, 2012 at 3:21 pm
Laura this is a tough one;
SQL doesn't have granularity you are looking for;
it's pretty much grant ALTER on the schema, and that applies to all the DDL for all objects, and the only way to prevent them from altering tables/procs and functions after THAT would be with a DDL trigger.
I do not see any way to create create/alter on just one kind of object.
Lowell
January 24, 2012 at 5:23 pm
Thanks a lot Lowell.
January 24, 2012 at 6:04 pm
So Lowell I should make the user db_ddladmin and then create DDL triggers to stop users from altering, dropping objects?
January 24, 2012 at 8:06 pm
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
January 24, 2012 at 9:04 pm
Wow Lowell... this is awesome... your comments on every line has made this code so clear. Thanks a bunch!
January 26, 2012 at 6:56 am
Laura_SqlNovice (1/24/2012)
Wow Lowell... this is awesome... your comments on every line has made this code so clear. Thanks a bunch!
Laura I was feeling a little inspired when i fiddled with this;
Glad it was easy to follow and understand, that's the feedback I need to keep posting here sometimes.
I guess it seems to be working for you?
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply