Cannot Add a Sql Server Login

When I try to create a SQL Server Login by saying

CREATE LOGIN [ourdomain\SQLAccessGroup] FROM WINDOWS;

I get this error

The server principal 'ourdomain\SQLAccessGroup' already exists.

However, when I try this code

DROP LOGIN [ourdomain\SQLAccessGroup]

I get this error

Cannot drop the login 'ourdomain\SQLAccessGroup', because it does not exist or you do not have permission.

The user that I am executing this code as is a sysadmin. Additionally, the user ourdomain\SQLAccessGroup does not show up in this query

select * from sys.server_principals

Does anyone have any ideas?

5 Answers

We are still struggling to understand the HOW of this issue, but it seems that [ourdomain\SQLAccessGroup] was aliased by a consultant to a different user name (this is part of an MS CRM installation). We finally were able to use some logic and some good old SID comparisons to determine who was playing the imposter game.

Our hint came when I tried to add the login as a user to the database (since it supposedly already existed) and got this error:

The login already has an account under a different user name.

So, I started to examine each DB user and was able to figure out the culprit. I eventually tracked it down and was able to rename the user and login so that the CRM install would work. I wonder if I can bill them $165.00 an hour for my time... :-)

is this when you are restoring from a backup or something? I've found that the following works for me in situations when I'm having problems with user accounts in sql

EXEC sp_change_users_login ‘Auto_Fix’, ‘user_in_here’

This happened to me when I installed SQL Server using a Windows username and then I renamed the computer name and the Windows username from Windows. SQL server still has the old "Computername\Username" in its node of Server->Security->Logins.

The solution is to go to Server->Security->Logins and right-click -> rename the old Windows user and use the new MachineName\Username.

I faced similar issue and i believe the issue was as a result of trying to recreate a login account after deleting an existing one with same name. Just go through the various databases on the server using SQL Studio. Example steps:

DBName ->Security->users

at this level for each of the databases, you may see the name of the user account there. Delete all occurrence in each Database as well as its occurrence in the top level Security settings at

Security->Logins

When done, try recreating the login account again and you should be fine.

I had the same story as Shadi. On the top I can add that it can be also done by query:

ALTER LOGIN "oldname\RMS" WITH name="currentname\RMS"