SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create User, Role, Grant Permission


Create User, Role, Grant Permission

Author
Message
Laura_SqlNovice
Laura_SqlNovice
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 501
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
Laura_SqlNovice
Laura_SqlNovice
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 501
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
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70372 Visits: 40924
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Laura_SqlNovice
Laura_SqlNovice
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 501
Thanks a lot Lowell.
Laura_SqlNovice
Laura_SqlNovice
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 501
So Lowell I should make the user db_ddladmin and then create DDL triggers to stop users from altering, dropping objects?
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70372 Visits: 40924
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Laura_SqlNovice
Laura_SqlNovice
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 501
Wow Lowell... this is awesome... your comments on every line has made this code so clear. Thanks a bunch!
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70372 Visits: 40924
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search