Cannot Add a Sql Server Login

Question

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?

Answer 1

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... :-)

Answer 2

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’