I would like to drop and recreate every user in a database without having to manually do this for 500 users. I have this so far:

USE [Maintenance]

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'xxxxxx\xxxxx')
DROP USER [xxxxx\xxxxx]

USE [Maintenance]

CREATE USER [xxxxx\xxxxx] FOR LOGIN [xxxxx\xxxxx] WITH DEFAULT_SCHEMA=[dbo]

The real problem is when I detach and reattach db from a SQL Server 2005 and restore to a SQL Server 2008, all the logins carry over, but the do not work unless I drop and add them back. I have read many many links and other threads about them and tried How to transfer logins and passwords between instances of SQL Server(http://support.microsoft.com/kb/246133) but it did not work.

On a LAN, we have 500 domain users that access a SQL Server 2005 database. I have created a backup from the SQL Server 2005 databases and want to move all the databases to a new server that will run SQL Server 2008. I have 12 people that have SQL Server login's. I have remade those and they work. I have 500 user's that, through there dsn, should be able to access a database, if the are assigned roles db_datareader/db_datawriter to the DBNAME->Security->User list. The ones that don't work are the domain database users. Under the DBname/instancename->Security->Logins every db domain user is not currently listed under the logins

The message I keep getting on the SQL Server 2005 is This is the message I keep getting. Login failed for user 'COMPANY\USER'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: XX.XXX.XX.XX]

Here is the dsn:

Microsoft SQL Server ODBC Driver Version 03.85.1132
Data Source Name: xxxxsql002
Data Source Description: xxxxsql002
Server: xxxxsql002\xxxxbaconsql
Database: (Default)
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Integrated Security: Yes
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No

The UAC and firewall are disabled.


  • They are all public users. They are users with windows logins. Certain users have server access to all db and the server login. I also have 500+ user's that have read/write to specific db. If I can get them to at least be scripted as public, I can take the time to add back the read right permissions
    – crh225
    Jan 3, 2013 at 18:03
  • I have tried most methods and they seem to have all failed. I have tried importing the databases using sql 2008 data import (failed). I have tried to restore from backups(failed) and I have also detached and reattached the db(also failed)
    – crh225
    Jan 3, 2013 at 18:07
  • When I say failed, I meant it did not work when trying to access the server through a client's dsn.This is the message I keep getting. Login failed for user 'COMPANY\USER'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: XX.XXX.XX.XX]
    – crh225
    Jan 3, 2013 at 18:18

2 Answers 2


OK, there are several things that it could be but I'm not sure if we can determine which from here. These include the following:

  1. AD propagation issues
  2. AD caching "stickiness" on the target SQL Server
  3. Domain Trust issues (particularly if the target server is in a different domain)
  4. Windows Group membership/visibility issues
  5. UAC blocking users access to certain groups needed for the new server
  6. Kerberos issues (very common)
  7. Etc...

And yes, dropping and re-adding the Users does seem to fix it in some of these cases, even though this should in theory never be needed because the SIDs should always match for Windows Users in transferred databases. It may be that the target SQL server simply lacks the same necessary server-level principal definitions as the original servers (and this as a side-effect creates explicit ones), or it may be that the act of adding the Login/user causes SQL Server to forcibly rerun the query and reload it's AD information for that user.

In any event, the real problem tends to be that dropping a user is a potentially disruptive and destructive act. It may work, but it may also cause some bad side-effects. Nonetheless, if you want to try that, then the best procs I know of for that are from Ted Krueger and they are located here: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users.

Other (possible) solutions that you could employ include:

  • Automated procedure that does not drop the User, but does add and associate a server-level Windows Login for it.
  • Create a Windows AD group for access to the database, add all of the users to that, and then add that to the SQL Server as a Login and Database as a User. (this is the recommended approach for managing large-scale access anyway).
  • ...
  • to be more specific to my solution, the BUILTIN\USERS had the wrong permissions set up. I did not originally set it up and I had know idea what it was.
    – crh225
    Jan 4, 2013 at 13:27

Short answer... don't drop any logins/users, use the stored procedure sp_change_users_login to fix your issue.

Here are step-by-step instructions:

You do not need to drop and re-create users.

Follow the steps below

  1. script logins from source server -- use sp_help_revlogin which you can download from microsoft at http://support.microsoft.com/kb/918992
  2. create logins on target server
  3. script out server role memberships from source server (optional)
  4. assign logins to server roles on target server (optional)
  5. use the following code to fix the mapping between your logins and database users after each database restore.

    USE [DATABASE_NAME]; DECLARE @databaseName VARCHAR(100) DECLARE @username varchar(25)
    SELECT userName = name FROM sysusers
    WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
    and suser_sname(sid) is null
    ORDER BY name
    OPEN fixusers
    FETCH NEXT FROM fixusers
    INTO @username
    BEGIN TRY EXEC sp_change_users_login 'update_one', @username, @username
    PRINT 'OK: Fix Users Run for Username: ' + @username + ' in database: ' + @databaseName END TRY BEGIN CATCH PRINT 'FAIL: Fix Users Run for Username: ' + @username + ' in database: ' + @databaseName END CATCH FETCH NEXT FROM fixusers
    INTO @username
    BEGIN TRY CLOSE fixusers
    DEALLOCATE fixusers

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.