Take the 2-minute tour ×
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.

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!

share|improve this question

migrated from stackoverflow.com Jan 4 '13 at 3:18

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. –  RBarryYoung Jan 3 '13 at 17:56
    
Is this really for both MySql and MS SQL Server? They very likely will be completely different procedures. –  RBarryYoung Jan 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? –  RBarryYoung Jan 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)? –  RBarryYoung Jan 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_s Jan 3 '13 at 19:16
show 20 more comments

2 Answers 2

up vote 5 down vote accepted

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 requery 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 Datbase as a User. (this is the recommended approach for managing large-scale access anyway).
  • ...
share|improve this answer
    
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 '13 at 13:27
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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