Remove orphaned roles from database 2

[robsuttonjr]

I recently detached and attached a database to a different server. I had removed all of the users before attaching but one user is corrupt. How can I remove an orphaned user role from a database so I can link them again? If I add the user and try to give them access to the database in question it says they are already added.

 

[SQLBill]

EXEC SP_REVOKEDBACCESS 'username'

You must run this in the database that you want to fix.

-SQLBill

 

[robsuttonjr]

I am use query analyzer and I run the suggested sp but it says the user does not exist in the database. However when I look in roles the user is there. If I look in security the user does not exist. The user only exists in the role.

 

[Corran007]

Try sp_droprolemember [ @rolename = ] 'role' ,
[ @membername = ] 'security_account'

This example removes the user JonB from the role Sales.

EXEC sp_droprolemember 'Sales', 'Jonb'

Not sure if it will work if the user does not exist.

 

[robsuttonjr]

I have a role called 'public' and tried the command but it says role public does not exist.

 

[Corran007]

Public is a default role that you cannot remove anyone from, without removing thier access from that database. Its just a general default role that has no permissions unless you specifially give permissions to the publlic role in that database.

 

[MaffewW]

you shouldnt need to remove them, try this
sp_change_users_login 'update_one', 'user', 'login'


Matt

Brighton, UK

 

[robsuttonjr]

The problem occured when I detached the database and re-attached elsewhere. None of the user id's existed in the security section of sql server but in the role's of the actual database there were entries in 'public' for the users from the previous system the database was attached to. The problem is that the new server does not even see the users as existing but when I do add them under 'security' as a new login, I cannot attach them to the database which already has a role for them. It says a role already exists for xx user. Thanks

 

[MaffewW]

did you try the stored procedure as above?


Matt

Brighton, UK

 

[robsuttonjr]

I tried the sp_change_users_login_one but it did not work with the user/login since the login did not exist. I tried the same with autofix and it created the user and linked the roles but now it says the user does not exist in the users collection.

 

[uberskunk]

Hi,

Just wanted to say thanks to SQLBill, I restored a backup of a DB to a new server and left all the users and roles in. And your SP_REVOKEDBACCESS worked a treat to remove the old ones.

FYI, I transfered the DB from the states and it took 5 hours, so this was an excellent time saver.

Cracking,
Mike

 

[SQLBill]

I'm glad my solution was able to help you and thanks for the star.

-SQLBill