SQL Server Performance Forum – Threads Archive

How to delete the login permanently from Logins

Hi Friends, I have a problem in SQL Server 2005. I created a login(SQL Authentication) and then delete it from Mangement Studion. Then again tried to create it(SQL Authentication) and able to do that. But when I tried to login through that login, its giving me the error and not able to login.
Do we have any solution through which we can permanently delete the login, Bks I think this is the problem of SID mismatching.
Please let me know the solution. Thanks in advance… Regards
Ashish Kuriyal

It has become orphaned, for mistmatching of logins you can takehelp of SP_CHANGE_USERS_LOGINS in this case and use AUTO_FIX option. Look under BOL for more information on this SP. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Its not showing in the list of orphaned user as welll..

The SID for a login is used as the basis for implementing database-level access. A login may have two different SIDs in two different databases on a server. In this case, the login can only access the database that has the SID that matches the SID in the sys.server_principals view. This problem may occur if the two databases are consolidated from two different servers. To resolve this problem, manually remove the login from the database that has a SID mismatch by using the DROP USER statement. Then, add the login again by using the CREATE USER statement. Are you getting user already exists or user doesn’t exists. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I am getting the error of User already exists..

Examine the contents of the sys.server_principals view in the instance Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thanks Satya,
I dropped the login with this and now the problem resolved.
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N’time’)
DROP LOGIN [time] Regards
Ashish Kuriyal Ashish

This the new feature in SQL 2005 for having sys.server_principals for the logins, not like straight forward in SQL 2000. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

When you try to delete the login through SSMS you get the following message…
You have handle manually…. as Satya stated… "Deleting server logins does not delete the database users associated with the logins. To complete the process, delete the users in each database. It may be necessary to first transfer the ownership of schemas to new users."
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>