Thursday, August 25, 2011

Lets Start with Kid's Stuff..

How to delete a login which has users mapped to different databases?
Well you can drop a login to which database users are mapped but this will create orphaned users.

Well to begin with we have to know the name of the login name from syslogins table in case you are not sure abpout the login name.

query : select name from syslogins where name like '%%'

Now we got the login name to begin with and then we can get all the user databases the login has access to with help of sp_helplogins
query: Exec sp_helplogins 'loginName'

Now we need to delete the user from the different databases.
We will use the sp_dropuser to delete the user from the databases.
query : EXEC revokedbaccess ''
remeber this name is the username in the database corresponding to the login.

Now after deleting all users from all the databases. We need to delete the login as well. You can do that from the GUI or use the script.

script: Exec sp_droplogin ''

References: MSDN


No comments:

Post a Comment