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.

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
add comment

1 Answer 1

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