Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jshurak
Starting Member
44 Posts |
 Posted - 2006-11-28 : 08:36:22
|
| We have two production servers that use the same application. The first one is for production use of the application and the second is used for reporting. Each night the back up from the production server is restored on the reporting server. Each night specific users database access are being revoked. I ran the sp_change_users_logins AUTO_FIX yesterday and the users were able to access what they needed to access. But this morning these users access were revoked again. This time I ran the sp_change_users_logins UPDATE_ONE. I ran this on the reporting server with success, it did nothing when I ran it on the production server. Should this fix the problem?Jshurak - The International Businessman of Mystery |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-11-28 : 10:16:09
|
quote:
Originally posted by jshurak
This time I ran the sp_change_users_logins UPDATE_ONE. I ran this on the reporting server with success, it did nothing when I ran it on the production server. Should this fix the problem?
The reason why the users are unable to login is due to the SID that is attached to the logins. Each server has users two types on users (dbusers and logins). The logins are either NT or SQL. On an NT login the SID is stored in NT not in SQL Server. For SQL logins the SID is stored inside SQL Server. This means that each SQL Server will give it's own SID to the login. So if you setup "Billy" as a login on two SQL Servers they will not be the same be cause server1 will be 1234.billy while server2 is 5678.billy. When you add a user to a database the user is added with the SID from that server. When you restore from one server to another you are mix&matching the users. You have 1234.billy in the database but the server login is 5678.billy. So after each and every restore you will need to do the sp_change_users_login 'auto_fix' for the SQL login that you want to work, NT logins do not have this issue. I hope that helps. Running the sp_change_users_login proc sync the sid on the dbuser to match that of the sql server login.**Edit: ** "it did nothing when I ran it on the production server" ** One thing that I hope you got from that is that you should not be doing anything to the production server.Regards,Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
 |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-11-28 : 23:43:43
|
| What you might can do is to "copy" the user from server1 to server2, so that you do not need to "sync" the user every night. By "copy", I mean copy the SID over. |
|
|
|
jshurak
Starting Member
44 Posts |
Posted - 2006-11-29 : 08:21:35
|
| Thanks for your posts. If we create the users on server1 and restore the second server would that sync the SID's? This thing is I've been created these users on both servers for over a year and we're only seeing this problem in the past week. We did some user clean up recently, but none of these users were on the list for deletion. Is it possible for a specific users role on the server was to sync the SIDs? |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-11-29 : 20:56:40
|
| Nope you cannot just "sync" SID, if it were working before without any need to re-sync the users, they might be created with the same SID (as what I mention before, "copied" the SID from server1). Most likely recently someone delete that user and recreate them again (in your clean up process?), that will make the SID out-syn-ed. So you might need to "copy" the SID again.Restore will only restore the database user access, it will not restore the actual user to DB, so the SID of those user will still be base on the server.Hope that helps :) |
|
|
|
|
|
|
|
|
sp_change_users_logins question