How can I delete a user that has granted access to others?

Question

How can I delete a user that has granted access to others?

laker_42

SSCarpal Tunnel

Points: 4785
More actions
August 4, 2004 at 10:53 am

#360526

I am trying to convert a login from SQL Server to Windows authentication. I am trying to delete the sql login so I can create the windows nt login but it won't let me. It keeps saying "The user has granted or revoked privileges to the following in the database and cannot be dropped." I did some more digging and he granted access to a table to the public role. What can I do to be able to delete this login? I have tried several things but have had no luck. Any help would be appreciated.
An update on what I have found: This login has entries in the sysprotects as the grantor. I have tried granting these same permissions as dbo hoping that it would override what the user did but that didn't work either. Do I need to login with this user's login and remove the grant permissions?

Thanks
John

Viewing 6 posts - 1 through 5 (of 5 total)

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

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 1

I am not sure what was going on but I got a workaround. I ended up copying the table and renaming it. I then dropped the table, renamed the new table back to the old name and then deleted the user's login. I then granted permissions to the new table.

So am I correct in saying that you cannot drop a login if that login has granted or revoked any permissions within a database? Is there an easier way around this?

John

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

If the user has granted permissions, you are correct. Same with deny. Revoke simply means to remove whatever the permissions are, be they grant or deny. Not sure why BOL has the wording the way it does, it means deny where it has revoke. In any case, the grantor is present in both syspermissions and sysprotects.

Instead of recreating the objects, you can run a query to determine what permissions the user granted or denied, revoke those permissions, readd them from dbo, and then you should be able to drop the user.

K. Brian Kelley
@kbriankelley

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 3

I did try what you suggested. I determined that the user had granted permissions to a table for the public role. I then tried revoking the permissions as dbo but it wouldn't take. I even logged in as the user and tried to revoke the permissions. It still showed that the permissions were granted. It seemed like SQL was stuck and wouldn't let the permissions go. So as a last resort, I recreated the object.

John

Nathan Campoli Grasshopper Points: 11 More actions · Answer 4

I was having trouble with this too.

I found that I could alter the syspermissions table updating the grantor uid of whom I needed to revoke and set it to the dbo uid (1).

In query analyzer, I logged in as the SA user to the database I needed to revoke a user against:

-- Allows to modify sys objects

EXEC sp_configure 'allow updates', 1

reconfigure with override

-- uid 5 is the user I want to revoke access to the db

update syspermissions set grantor = 1 where grantor = 5

-- At this point sysprotects reflects the change that I made to syspermissions.

-- Proceeds as expected

exec sp_revokedbaccess 'MyUserName'

-- Remember to set configuration back to not allow updating of sys objects to be safe.

EXEC sp_configure 'allow updates', 0

reconfigure with override

This worked for me. I don't know if it is a best practices approach to this, but for my development purposes, it was fine.

Frankie-464050 Ten Centuries Points: 1010 More actions · Answer 5

Nathan Campoli

Thanks so much for the post. Made life less interesting. It's a script I'll keep - hopefully I will have forgotten why I needed it but won't delete

Thanks again

:hehe: