SQL Server Performance

revoke DBO help

Discussion in 'Performance Tuning for DBAs' started by bzeebee, Oct 15, 2003.

  1. bzeebee New Member

    I accidentally attached a database with another user as the database owner...
    How do i revoke the rights from this user without deleting him...Now this db has 2 db_owners user1 and user2...I cannot drop the user2 coz this is our prod system and the user2 owns a db already...

    Please help!!!
  2. satya Moderator

    Use REVOKE statement, refer to Books online for more information.
    Run SP_HELPDB and see who is the current owner.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. bzeebee New Member

    Can we use REVOKE to revoke DBO rights....from a user...if he is the db_owner?
    I am the owner of the db in sp_helpdb
  4. satya Moderator

    Run sp_changedbowner and the new owner is known as the dbo user inside the database. The dbo has implied permissions to perform all activities in the database

    Executing sp_changedbowner with only the login parameter changes database ownership to login and maps the aliases of users who were previously aliased to dbo to the new database owner.

    Run sp_changedbowner for user2 and check user1's privilege with sp_helplogins.
    Yes you can use REVOKE ALL to take out all permissions for user1, if required.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. bzeebee New Member

    Satya,
    I ran sp_helplogins user1 has the correct permissions to the database AAA
    User2 has additional permissions as user_name = 'dbo' and 'db_owner' which i have to revoke...

    How do i do this?

  6. satya Moderator

    If no other permission is required for User2 then use REVOKE ALL and refer to books online for thorough information on this statement.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. bzeebee New Member

    user2 is the db_owner for another database 'BBB'....I only need to revoke db_owner/dbo perms from 'AAA'
  8. satya Moderator

    Use the database AAA and you can use sp_droprolemember to drop from 'db_owner' fixed database role.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. bzeebee New Member

    Satya-I got this error when i executed this

    EXEC sp_droprolemember 'db_owner','user2'



    Server: Msg 15410, Level 11, State 1, Procedure sp_droprolemember, Line 61
    User or role 'user2' does not exist in this database.


    If i open user2 he is listed as a dbo for the 'AAA' database
  10. Twan New Member

    are your user user2 and login user2 out of synch for that database/server? try running sp_change_users_login on the new database and then sp_droprolemember

    Cheers
    Twan
  11. satya Moderator

    True, initially run SP_CHANGE_USERS_LOGIN with REPORT clause and see whether this user is reported.
    ANd only this will work with SQL logins not for windows logins.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page