Create User, Role, Grant Permission

  • 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

  • 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

  • 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!

  • Thanks a lot Lowell.

  • So Lowell I should make the user db_ddladmin and then create DDL triggers to stop users from altering, dropping objects?

  • 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;

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

    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!

  • Wow Lowell... this is awesome... your comments on every line has made this code so clear. Thanks a bunch!

  • 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!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply