Re: create new login

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;


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns999AF27A4FBB3Yazorman@xxxxxxxxxxxx
Mike (Mike@xxxxxxxxxxxxxxxxxxxx) writes:
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?

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.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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


.

Relevant Pages

  • Re: ASP.NET User.Identity.Name value after a domain username chang
    ... Can you point me to any resources I can use about getting the SID of the ... WebRequest user without using the username as the base of a lookup (since I ... access a website on the server. ... I have a very confusing issue when the domain login of a user is changed ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: SQL User SID format?
    ... When you move databases to another server, you can remap users to logins ... capability to remap a user to a login with new ALTER USER syntax - I ... The new ALTER USER syntax works for remapping both SQL and Windows ... this means you already have access to a SID from the syslogins catalog; ...
    (microsoft.public.sqlserver.security)
  • Re: Shared folders in a different domain
    ... The only error I get is when I try to login from the Domain2 server using ... When I go into the share permissions I see a SID where the Domain 1 group ... indication of connectivity issues, ...
    (microsoft.public.windows.server.general)
  • Re: Converting User SIDS from SQL 2000 to SQL 2005
    ... server, ... DECLARE @charvalue varchar ... SELECT sid, name, xstatus, password FROM master..sysxlogins ... BEGIN -- NT login is denied access ...
    (microsoft.public.sqlserver.security)
  • gdm hangs
    ... gdm will hang 9 of 10 times when logging out. ... with or without the client having been connected to the Server. ... # Timed login, useful for kiosks. ... Must output the chosen host on stdout, ...
    (Debian-User)