Post reply

Cannot delete user

  • January 20, 2005 at 8:00 pm

    #87369

    I'm trying to delete a user in a particular database and I get the following error:

    Error 15284: The user has granted or revoked privileges to the following in the database and cannot be dropped

    I've checked that the user doesn't have ownership of any tables and I can't see it has any rights to anything other than public.  Is there something else I can look at to see what else it owns.

    Thanks

    Thanks,

    Kris

  • January 21, 2005 at 2:21 am

    #538450

    What is the default database for the user? Make sure that it is set to the MASTER database.

    I had one hell of a job deleting a user in a database that was restored from one of our client's machines.

    Enterprise manager wouldn't allow it.

    sp_dropuser didn't work.

    I tried sp_change_users_login but to no avail.

    Eventually sp_revokedbaccess worked.

    LinkedIn Profile

  • January 21, 2005 at 3:05 am

    #538461

    I tried that and I still get the same error.  I even ran sp_MShasdbaccess to see if there it had ownership of anything and it doesn't.

    Thanks,

    Kris

  • January 21, 2005 at 4:27 am

    #538473

    Hello, Kriss,

    I think that I had a problem like Yours. First of all try to run [ sp_helplogins 'login' ]. This shows to You what rights the login has on particular DB's.

    Hope this helps

  • January 21, 2005 at 5:54 am

    #538482

    Thanks for your help, but I found the problem.  It said there was a table that was owned by that user even though it didn't appear that way, so I'm still confused.  Even though it says that the table owner belongs too one owner there seems to be a component that still says that the previous owner is still there and I'm not sure what.

    The database owner is different to the table owners, maybe this means something.

    Thanks,

    Kris

  • January 21, 2005 at 6:12 am

    #538490

    Hello. Kriss,

    If i correct understood Your problem (You need to remove the owner of the table), You need to change the object owner with sp_changeobjectowner

  • January 21, 2005 at 6:17 am

    #538491

    Continued...

    if not please explain me clearlier, maybye I still can help You

  • January 21, 2005 at 6:22 am

    #538493

    When you look at the table owners it says it is the one I set up, but for some reason when I run the TSQL it says the old owner is still the owner somewhere, but I can't tell where.

    Thanks,

    Kris

  • January 21, 2005 at 6:53 am

    #538502

    To give you an example.  When I run sp_revokedbaccess I get the same error, but the result set says the "grantee" is public and then the table name.

    Does that make sense?

    Thanks,

    Kris

  • January 21, 2005 at 7:19 am

    #538507

    It sounds like a user have been given the rights to grant other users access.

    Unless there is an absolute imperative I have every single object owned by the dbo.

    Are there any Views owned by your mystery user?  If so it could be that the error message on the table is being caused by an ownership chaining problem from the view.

    LinkedIn Profile

  • February 9, 2005 at 2:57 am

    #541327

    I encountered the same problem.

     

    My first gut-feeling also led me down the road of looking where exactly this user still had some hidden permissions. However, the message should be interpreted differently:

    'In the past, the user you are trying to remove has granted other users permissions.'

    The user in my scenario used to be the user that was used to connect to the SQL Server with. The database was subsequently restored to a different server. Although you will not be able to find any objects owned by this guy, looking at the syspermissions database will shed some light on why you are receiving the error.

    As it turns out, within a few minutes of finding this, a developer ran in to beg me NOT to remove the user because it was essential for the database.

    I have looked a bit into it after he left (just to satisfy my own curiosity), but have yet to figure out a way to solve this (other than running update statements against the syspermissions table).

    Just wanted to let you guys know about my findings

  • January 6, 2006 at 11:37 am

    #613357

    I finally found what the problem was on our server - the user in question owned a huge number of DTS packages. I have yet to fix that (another issue that has to wait for a little bit) - but that's what the user owns.


    DBA
    Getty Images, Inc.

  • March 5, 2007 at 11:01 am

    #693176

    I know this is an old thread but I thought I would post what I believe to be a solution in case someone comes along looking for an answer (I hate it when I find old threads with my exact problem but no solution posted).

    This was exactly my issue. I did the following:

    1) Opened sysusers table and retrieved the uid of the user I was trying to delete (5) and dbo (1)

    2) Ran the following, this switched the "grantor" to being dbo rather than the user I was trying to delete:

    sp_configure 'allow updates', '1'

    reconfigure WITH OVERRIDE

    GO

    update syspermissions set grantor = 1 where grantor = 5

    GO

    exec sp_configure 'allow updates', '0'

    reconfigure WITH OVERRIDE

    GO

    3) Deleted my orphaned user

    Hope this helps!

    X

  • May 19, 2011 at 8:42 pm

    #1327455

    You can't make ad-hoc updates to 2005/2008 system tables. Does anyone have a 2008 compatible solution?

  • May 20, 2011 at 12:50 pm

    #1327911

    Original post is over six years old

    Chris Powell

    George: You're kidding.
    Elroy: Nope.
    George: Then lie to me and say you're kidding.

Viewing 15 posts - 1 through 15 (of 15 total)

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