Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create User, Role, Grant Permission Expand / Collapse
Author
Message
Posted Tuesday, January 24, 2012 2:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:40 PM
Points: 103, Visits: 500
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
Post #1241211
Posted Tuesday, January 24, 2012 2:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:40 PM
Points: 103, Visits: 500
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
Post #1241231
Posted Tuesday, January 24, 2012 3:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 12,962, Visits: 32,505
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1241257
Posted Tuesday, January 24, 2012 5:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:40 PM
Points: 103, Visits: 500
Thanks a lot Lowell.
Post #1241294
Posted Tuesday, January 24, 2012 6:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:40 PM
Points: 103, Visits: 500
So Lowell I should make the user db_ddladmin and then create DDL triggers to stop users from altering, dropping objects?
Post #1241309
Posted Tuesday, January 24, 2012 8:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 12,962, Visits: 32,505
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1241331
Posted Tuesday, January 24, 2012 9:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:40 PM
Points: 103, Visits: 500
Wow Lowell... this is awesome... your comments on every line has made this code so clear. Thanks a bunch!
Post #1241343
Posted Thursday, January 26, 2012 6:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 12,962, Visits: 32,505
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1242137
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse