create new login

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 28 Aug 2007 19:31 GMT

I migrated a SQL 2005 db from our development server to our QA server. I created a new ID for the application to use on the QA SQL server. The problem I'm facing is, I can't connect using the ID I created or even the ID that was already in the db on the test server. I did a backup of the DB on the test server, did a restore on the QA server and the ID's will not connect. I keep getting 'login failed for username'

The username is under the security tab of SQL, and I have it pointing to the DB that it will be connected to, but no success.

Any ideas on what could be causing this to fail?

Reply to this Message

Erland Sommarskog - 28 Aug 2007 22:46 GMT

> I migrated a SQL 2005 db from our development server to our QA server. I
> created a new ID for the application to use on the QA SQL server. The
[quoted text clipped - 7 lines]
>
> Any ideas on what could be causing this to fail?

Mapping between server logins and database users is by SID, not by
name. It's perfectly possible for the login Nisse to map to a user
Kalle in a database. Since SIDs are different on different server, you
typically get this problem when you move a database from one server
to another.

You can use sp_change_users_login to fix this.

Another way is to drop the login you created, and then recreate it
with CREATE LOGIN WITH SID =, where you get SID from
sys.database_principals for the database in question.

In SQL 2005 SP2, it's also possible to use one of ALTER USER and ALTER
LOGIN (I don't remember which) to change the SID, but unfortunately,
this is not in Books Online.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Uri Dimant - 29 Aug 2007 08:48 GMT

Erland

>In SQL 2005 SP2, it's also possible to use one of ALTER >USER and ALTER
>LOGIN (I don't remember which) to change the SID, but >unfortunately,
>this is not in Books Online.

You are correct
ALTER USER username WITH login = loginame;

>> I migrated a SQL 2005 db from our development server to our QA server. I
>> created a new ID for the application to use on the QA SQL server. The
[quoted text clipped - 23 lines]
> LOGIN (I don't remember which) to change the SID, but unfortunately,
> this is not in Books Online.

Reply to this Message

Erland Sommarskog - 29 Aug 2007 22:39 GMT

> You are correct
> ALTER USER username WITH login = loginame;

Even that? Almost too easy...

Signature

Reply to this Message

Uri Dimant - 29 Aug 2007 08:32 GMT

Mike
In addition to Erland's comment please read the following article
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authenticatio
n.html

I migrated a SQL 2005 db from our development server to our QA server. I created a new ID for the application to use on the QA SQL server. The problem I'm facing is, I can't connect using the ID I created or even the ID that was already in the db on the test server. I did a backup of the DB on the test server, did a restore on the QA server and the ID's will not connect. I keep getting 'login failed for username'

The username is under the security tab of SQL, and I have it pointing to the DB that it will be connected to, but no success.

Any ideas on what could be causing this to fail?

Reply to this Message

bass_player [SBS-MVP] - 29 Aug 2007 12:04 GMT

I'd recommend that you copy the accounts from your QA to your development server using this
http://support.microsoft.com/kb/246133

SQL accounts are resolved using SIDs rather than user-friendly names
I migrated a SQL 2005 db from our development server to our QA server. I created a new ID for the application to use on the QA SQL server. The problem I'm facing is, I can't connect using the ID I created or even the ID that was already in the db on the test server. I did a backup of the DB on the test server, did a restore on the QA server and the ID's will not connect. I keep getting 'login failed for username'

The username is under the security tab of SQL, and I have it pointing to the DB that it will be connected to, but no success.

Any ideas on what could be causing this to fail?

Reply to this Message