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!