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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 4 down vote accepted

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

share|improve this answer

You can check for orphan logins (i.e. a DB user with the same name as an instance login, but a different SID) with this:

-- List orphaned users
USE <db name>
sp_change_users_login @Action='Report'

If this returns any users then use this to fix them up

--assign orphan user to login
USE <dbname>
sp_change_users_login @Action='update_one', @UserNamePattern='user', @LoginName='user'
share|improve this answer

Your Answer


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.