Sign in
 

none
Not able to revoke db_owner permission

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I am trying to revoke db_owner permission to users, but getting below error.

    Login name must be specified(sqlmanagerUI)

    How can i fix this.

    Please help.

    Thanks

    Shashikala

    Saturday, May 10, 2014 3:22 AM
    |

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    To remove a user from a role on SQL 2008 and earlier:

    EXEC sp_droprolemember 'db_owner', 'thisuser'

    On SQL 2012 and later:

    ALTER ROLE db_owner DROP MEMBER thisuser

    If you insist of using the UI, you will need to figure what commands etc it uses.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 10, 2014 10:35 AM
    |

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Check if the user is not Orphan user by below command

    exec sp_change_users_login 'Report'

     

    If this is orphan the first this and then revoke.


    USE <database_name>;
    GO
    sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>',    @LoginName='<login_name>';
    GO


    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    Saturday, May 10, 2014 3:48 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    If I execute the below qeury

     

    exec

    sp_change_users_login 'Report'

    Nothing is showing in output.

    Shashikala

    Saturday, May 10, 2014 3:55 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    If I execute the below qeury

     

    exec

    sp_change_users_login'Report'

    Nothing is showing in output.

    Shashikala


    Make sure you run this query in DATABASE in which you are trying to find orphaned user

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    My TechNet Wiki Articles

    Saturday, May 10, 2014 9:52 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    To remove a user from a role on SQL 2008 and earlier:

    EXEC sp_droprolemember 'db_owner', 'thisuser'

    On SQL 2012 and later:

    ALTER ROLE db_owner DROP MEMBER thisuser

    If you insist of using the UI, you will need to figure what commands etc it uses.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 10, 2014 10:35 AM
    |