SQL Server – Script All Databases to .sql Files with Schema, Data, and Users

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

I am trying to create a process that will remove all users after the restore on the new servers as no user permissions will follow the database to the new server.

There is no need to delete all the users.

What you can do is use sp_help_revlogin to move all the logins that you are interested or your applicaiton/s need to the new server.

Once you move your database/s to the new server, you even dont have to sync up the users as the logins that you created from above script will be created with the SID from the old server.

As a side note, below script will help you fix the orphaned users:

WITH cte AS
(
SELECT dp.name AS DBUser, 
       dp.sid AS DBSid
  FROM sys.database_principals dp
       LEFT OUTER JOIN sys.server_principals sp 
         ON dp.sid = sp.sid 
 WHERE sp.sid IS NULL 
   AND dp.type = 'S'  -- SQL_USER
   AND dp.principal_id > 4
)
-- join to logins with the same name to get the sql statement to un-orphan the user
SELECT cte.*, 
       sp.name, 
       -- generate the SQL script to un-orphan the users that can be.
       LinkOrphanUserToLoginSQL = N'ALTER USER [' + cte.DBUser + N'] WITH LOGIN=' + sp.name + N';'
  FROM cte
       -- LEFT JOIN allows seeing all orphaned users
       LEFT JOIN sys.server_principals sp  
         ON cte.DBUser = sp.name
        AND sp.type = 'S'
ORDER BY DBUser;