SQL Server drop and create all users in every DB

Question

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]
GO

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

USE [Maintenance]
GO

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

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.

Thanks!

Answer 1

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

Answer 2

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 @databaseName = DB_NAME() PRINT 'SERVER: ' + @@SERVERNAME PRINT 'DATABASE: ' + @databaseName PRINT 'DATE: '+ CONVERT(VARCHAR(100),GETDATE(),120) DECLARE fixusers CURSOR
   FOR
   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
   WHILE (@@FETCH_STATUS = 0 ) BEGIN
   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
   END
   BEGIN TRY CLOSE fixusers
   DEALLOCATE fixusers
   END TRY BEGIN CATCH PRINT 'FAILED TO CLOSE CURSOR fixusers' END CATCH