locked
Working with Database Roles RRS feed

  • Question

  • Hi,

    I have created a user, role and assigned the user as a rolemember to the role. I have given db_owner permission for the role created.

    If i execute as the user created, and tried to create a object, its giving error like "does't have permission to create the table" .


    The code i have tried is...


    USE TestDB
    GO
    
    -- Creating user
    CREATE USER TestUser FOR LOGIN TestLogin
             WITH DEFAULT_SCHEMA = dbo
    GO
    
    -- Creating Role
    sp_addrole 'TestRole'
    GO
    
    -- giving permission to the role
    ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [TestRole]
    GO
    
    -- adding role member
    sp_addrolemember 'TestRole', 'TestUser'
    GO
    
    
    -- Executing as TestUser and creating the object
    EXECUTE as USER = TestUser
    GO
    
    CREATE TABLE Test(
    Column INT
    )
    GO
    
    ERROR: CREATE TABLE permission is denied in the database 'TestDB'



    Please let me know, if i missed something in the above


    Thanks
    Sr
    Thursday, August 27, 2009 12:02 PM

Answers

  • Thank You Ewan!!!


    Thanks for your info, It helped me a lot


    Thanks,
    Sr
    • Marked as answer by sr parimi Thursday, August 27, 2009 2:34 PM
    Thursday, August 27, 2009 2:27 PM

All replies

  • After:


    -- adding role member
    sp_addrolemember 'TestRole', 'TestUser'
    GO

    Add:


    GRANT CREATE TABLE to TestRole

    and than:

    EXECUTE as USER = TestUser
    GO

    CREATE TABLE TestRole.Test(
    Column1 INT
    )
    GO
    Thursday, August 27, 2009 12:59 PM
  • Is that not happened by doing the below

    -- giving permission to the role
    ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [TestRole]
    GO

    am giving db_owner permission to the database role right? is not enough to create/alter the objects?

    Thanks
    Sr
    Thursday, August 27, 2009 1:11 PM
  • You gave access to SCHEMA db_owner - so You can perform granted operations on this schema .

    SCHEMA db_owner and ROLE db_owner is not the same.
    Thursday, August 27, 2009 1:28 PM
  • Thanks for your clarification.

    How about the below code? is it make sence? will it work?
    ALTER AUTHORIZATION ON ROLE::[db_owner] TO [TestRole]
    GO


    Thanks
    Sr
    Thursday, August 27, 2009 1:39 PM
  • You must add Your user to a role:


    sp_addrolemember 'db_owner', 'TestUser'
    go


    or grant privileges on Your role
    i.e:

    grant create table,drop[,other_roles...] to TestRole
    Thursday, August 27, 2009 1:55 PM
  • Hi Sr

    You cannot transfer authorization of the dbo schema

    From BOL:
    Exceptions to ALTER AUTHORIZATION:
    Cannot change the owner of sys, dbo, or information_schema.

    As Piotr said, what you want to achieve is to add the TestRole to the db_owner fixed database role, thus granting dbo to the role

    Ewan


    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Thursday, August 27, 2009 2:15 PM
  • Thank You Ewan!!!


    Thanks for your info, It helped me a lot


    Thanks,
    Sr
    • Marked as answer by sr parimi Thursday, August 27, 2009 2:34 PM
    Thursday, August 27, 2009 2:27 PM