Sql-server – How to restore a SQL Server 2008 R2 database

sql-server-2008-r2windows

Here's my issue.

  • I have a server created from a windows image that includes SQL Server 2008 and two specific windows users, these windows users are a part of the sysadmin role and are users for the SQL Server instance.

  • If I detach then reattach the database everything works fine, the two windows users are able to connect no problem.

  • If I detach the database, re-image the system, then re-attach the database the windows users will still be listed as I can see them via Management Studio.

  • I'd imagine that the attaching and detaching of the database has nothing to do with it since the logins are listed under 'Security' for the SQL Server instance and not the db.

  • At this point users exist and have rights, but they can not connect unless you delete and re-add the windows users, I believe this is because the SID's are messed up.

Any idea how to 'update' the sid's to reflect the current windows users and not the previous ones?

Best Answer

If the SIDs change on re-imaging then SQL Server will regard them as new Windows logins. They'll have to be dropped and re-added: as you are doing.

You can test for changed SIDs by using SUSER_SID('domain\somelogin').
The results should b e different before and after.

What you can do is to use a Windows Group instead. So the Windows group contains the 2 users. SIDs will change but you only have to drop and re-add the single group.

Now, you may be able to use a start-up stored procedure to test for SID differences using sys.server_principals and SUSER_SID. If different for a given name you can automate the DROP LOGIN, CREATE LOGIN, ALTER USER

Note: "logins" at the server level are expected to be static. SQL Server provides tools to remap a database-level user to a login via ALTER USER

Related Question