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.