Sql-server – How to give an existing server login access to an attached/migrated database

loginsmigrationsql server

I'm migrating databases from SQL Server 2012 to 2014. I detached the databases from source server, then copied the files (.mdf, .ldf, etc.) to the new server and attached them. That process went fine, now the databases are available on SQL Server 2014.

The problem now is that when I go to Security -> Logins on the new server and check "User Mappings" for LoginX all the new attached databases are checked and apparently LoginX should have access to them. But when trying to access those databases using that login they are not available for that user. I have tried several things:

  1. I tried to remove those mappings to try to recreate them but it fails saying that "Cannot drop the user dbo.. (Error 15150)".

  2. Also checked Security -> Users under the new attached databases and there is no user associated with LoginX. Well I tried creating a user for that login on one of the databases and that also fails saying that "The login already has an account under a different user name. Error 15063.

  3. I have also tried sp_change_users_login 'Auto_Fix' 'loginX' but that did not work and actually was expected to do nothing because loginX is not an orphan… but I'm running out of ideas.

  4. Deleted LoginX from server and then recreated it and then the mappings just reappeared (I did not put them there). I have no clue why/how those mappings are there.

  5. According sp_change_users_login 'report' there are no duplicate logins or orphan logins/users.

The question is, how can I fix that? This scenario is kind of the opposite of an orphan where I have a login and it is supposed to have access to some databases but the databases do not know about the login and I don't know how to recreate that link or destroy it and make it right.

All users/logins are Windows/Active Directory users so SIDs are (should be?) the same for both servers.

The login was not created using same SID. We are migrating dozens of databases, so the migration was scripted using the detach/attach method.

All our users/logins are Windows AD logins. I'm going to check that maybe I have mixed SQL and Windows types.

I tried ALTER USER [user name] WITH LOGIN = [Windows Account]; but it fails saying "Cannot alter the user because it does not exists or you do not have permission" (Error 15151). I can see the user listed on server logins and I'm using a sysadmin account.

Best Answer

Finally worked after changing the database owner. The database was reattached by a different user so the owner was something else.

use [database name];
exec sp_changedbowner @loginame = 'loginX';

After that the user loginX was able to access the database. It still does not show under Security -> Users but the database is mapped for the loginX login on the server.

Related Question