Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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]

This question came from our site for professional and enthusiast programmers.

3

That's going to be a big problem if any of those users are the Owners of any objects.
–
RBarryYoungJan 3 '13 at 17:56

Is this really for both MySql and MS SQL Server? They very likely will be completely different procedures.
–
RBarryYoungJan 3 '13 at 17:57

1

Are the users members of any Roles? Do the have any Granted or Denied access permissions to objects? Are they all Users associated with Windows Logins that are registered as server-principals? Do they have any granted DB or Schema privileges?
–
RBarryYoungJan 3 '13 at 18:01

2

OK, chat's out, we'll do this here... Do these users have windows logins that are registered on the SQL Server as "Logins" (i.e., server level principals)?
–
RBarryYoungJan 3 '13 at 18:37

2

WHY do you create 500 individual logins for each user? Wouldn't it be much easier to put those users into a Windows NT group and just give that one group access to SQL Server??
–
marc_sJan 3 '13 at 19:16

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:

AD propagation issues

AD caching "stickiness" on the target SQL Server

Domain Trust issues (particularly if the target server is in a different domain)

Windows Group membership/visibility issues

UAC blocking users access to certain groups needed for the new server

Kerberos issues (very common)

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 requery and reload it's AD information for that user.

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 Datbase as a User. (this is the recommended approach for managing large-scale access anyway).