none
GRANT IMPERSONATE / CREATE SCHEMA WITH AUTHORIZATION dbo questions

    Question

  • Hey all,

    Say I want developers to create schemas with authorization to dbo ... is the only way to accomplish this by granting impersonate to the calling user/role to dbo? If so, what exactly does this do? I'm reading that this will also grant db_owner privileges? I don't believe this to be true though. I did a quick test case only granting a user impersonate to dbo and that user was not able to create a schema until I gave it db_ddladmin. So I guess at this point I'm simply trying to better understand exactly what granting impersonate to dbo does.

    Thank you


    --- Adam
    Wednesday, June 08, 2011 7:46 PM

Answers

  • Hi Adam,

     

    When you granting the IMPERSONATE to a user, it means he or she could act as the grantor after access the database. For example, you have using the below script to grant IMPERSONATE to user1 on dbo:

    GRANT IMPERSONATE ON USER::dbo to user1;

     

    When the user1 connected to the database, it could change his context to the dbo's by using the command EXECUTE AS USER='dbo', it looks like the user1 have all permission what dbo does.

     

    Please take a look on my below test steps:

     

    1. Log in SQL Server Management Studio with sa login.
    2. Create a login named as Login1, create a database with name as TEST, mapped the Login1 to user user1 in database TEST.
    3. Execute the below script to grant IMPERSONATE permission:          
    4. GRANT IMPERSONATE ON USER::dbo to user1;
      
    5. Login SSMS with Login1, and execute the below script before it can impersonate dbo:
      USE TEST
      GO
      EXECUTE AS USER='dbo'
      GO 
      
      
        
       
    6. Then we could create shcema and table:
      CREATE SCHEMA TEST CREATE TABLE TEST.TEST(ID INT)


    If there are anything unclear, please feel free to ask.

    Thanks,
    Weilin Qiao

     

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Thursday, June 09, 2011 8:18 AM
  • If you let the user impersonate dbo, you might as well just make them dbo. They can use that impersonation to elevate their privilegess like this:

    EXECUTE AS USER = 'dbo'
    GO
    GRANT CONTROL ON DATABASE::Test TO User1;
    GO

    Now they have given themselves CONTROL DATABASE permission, so they can do anything in the database.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Thursday, June 09, 2011 4:02 PM

All replies

  • Please, see more about it in: http://msdn.microsoft.com/en-us/library/ms178640.aspx.

    I think this can help you.


    Roberson Ferreira - Database Developer

    If this suggestion is helpful, please rate it as helpful.
    If it will help solve the problem, please mark it as Answer.

    Wednesday, June 08, 2011 9:39 PM
  • Hi Adam,

     

    When you granting the IMPERSONATE to a user, it means he or she could act as the grantor after access the database. For example, you have using the below script to grant IMPERSONATE to user1 on dbo:

    GRANT IMPERSONATE ON USER::dbo to user1;

     

    When the user1 connected to the database, it could change his context to the dbo's by using the command EXECUTE AS USER='dbo', it looks like the user1 have all permission what dbo does.

     

    Please take a look on my below test steps:

     

    1. Log in SQL Server Management Studio with sa login.
    2. Create a login named as Login1, create a database with name as TEST, mapped the Login1 to user user1 in database TEST.
    3. Execute the below script to grant IMPERSONATE permission:          
    4. GRANT IMPERSONATE ON USER::dbo to user1;
      
    5. Login SSMS with Login1, and execute the below script before it can impersonate dbo:
      USE TEST
      GO
      EXECUTE AS USER='dbo'
      GO 
      
      
        
       
    6. Then we could create shcema and table:
      CREATE SCHEMA TEST CREATE TABLE TEST.TEST(ID INT)


    If there are anything unclear, please feel free to ask.

    Thanks,
    Weilin Qiao

     

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Thursday, June 09, 2011 8:18 AM
  • If you let the user impersonate dbo, you might as well just make them dbo. They can use that impersonation to elevate their privilegess like this:

    EXECUTE AS USER = 'dbo'
    GO
    GRANT CONTROL ON DATABASE::Test TO User1;
    GO

    Now they have given themselves CONTROL DATABASE permission, so they can do anything in the database.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Thursday, June 09, 2011 4:02 PM