June 8, 2018 at 8:19 am
I've created a user, TestUser, in SQL Server 2012 and grant it DataReader permissin to two databases, DB_One and DB_Two. The DB_One is backed up and restored every night, and DB_Two remains unchanged. After DB_One restored, I run a stored procedure to restored the user permissions, sp_change_users_login 'AUTO_FIX', 'TestUser'. However, when I check the user permission next day, under the DB Server > Security > Logins > TestUser > User Mapping, the permission to DB_One is not check anymore.
We've been using sp_change_users_login 'AUTO_FIX' to restore other user accounts in DB_One without any issues. For this user, TestUser, it has permissin to two different databases. How can I restore its permission after one of database backed-up and restored?
Please help!
June 8, 2018 at 12:06 pm
It sounds like a step is missing in your description somewhere.
Is DB_One being restored to the same server it was backed up on, or a different server (or instance?) If it's being restored on the same server, is it to a different name or are you over-writing the existing database?
I'm presuming your setup is something like:
Server_A
>>> DB_One
>>> DB_Two
Lastly, MS suggests rather than using sp_change_users_login, you instead do:use [DBName];
alter user SQLUser with
login = SQLLogin
So for your situation, it would look something like:use [DB_One];
alter user TestUser with
login = TestUser
June 8, 2018 at 12:31 pm
Using sp_change_users_login or alter user will re-associate the database user to an instance login. Maybe we need clarification on what "permission" seems to be missing after the re-association?
June 8, 2018 at 12:39 pm
jasona.work - Friday, June 8, 2018 12:06 PMIt sounds like a step is missing in your description somewhere.Is DB_One being restored to the same server it was backed up on, or a different server (or instance?) If it's being restored on the same server, is it to a different name or are you over-writing the existing database?
I'm presuming your setup is something like:
Server_A
>>> DB_One
>>> DB_TwoLastly, MS suggests rather than using sp_change_users_login, you instead do:
use [DBName];
alter user SQLUser with
login = SQLLoginSo for your situation, it would look something like:
use [DB_One];
alter user TestUser with
login = TestUser
Thank you very much for your advise. The two databases are on the same server. I'm going to give it a try.
June 8, 2018 at 12:47 pm
Chris Harshman - Friday, June 8, 2018 12:31 PMUsing sp_change_users_login or alter user will re-associate the database user to an instance login. Maybe we need clarification on what "permission" seems to be missing after the re-association?
The user has DataReader permission to both DB_One and DB_Two before the restoration. After DB_One restored, the user mapping to DB_One is lost, "Unchecked" in user property panel. So the user can only access DB_Two after DB_One restoration. Both databases are on the same server.
June 8, 2018 at 2:30 pm
jay-125866 - Friday, June 8, 2018 12:39 PMjasona.work - Friday, June 8, 2018 12:06 PMIt sounds like a step is missing in your description somewhere.Is DB_One being restored to the same server it was backed up on, or a different server (or instance?) If it's being restored on the same server, is it to a different name or are you over-writing the existing database?
I'm presuming your setup is something like:
Server_A
>>> DB_One
>>> DB_TwoLastly, MS suggests rather than using sp_change_users_login, you instead do:
use [DBName];
alter user SQLUser with
login = SQLLoginSo for your situation, it would look something like:
use [DB_One];
alter user TestUser with
login = TestUserThank you very much for your advise. The two databases are on the same server. I'm going to give it a try.
So, to clarify, you back up DB_One on server_A and then restore that backup to server_A as DB_One?
Overwriting the DB_One that you just backed up?
June 9, 2018 at 6:30 am
jasona.work - Friday, June 8, 2018 2:30 PMjay-125866 - Friday, June 8, 2018 12:39 PMjasona.work - Friday, June 8, 2018 12:06 PMIt sounds like a step is missing in your description somewhere.Is DB_One being restored to the same server it was backed up on, or a different server (or instance?) If it's being restored on the same server, is it to a different name or are you over-writing the existing database?
I'm presuming your setup is something like:
Server_A
>>> DB_One
>>> DB_TwoLastly, MS suggests rather than using sp_change_users_login, you instead do:
use [DBName];
alter user SQLUser with
login = SQLLoginSo for your situation, it would look something like:
use [DB_One];
alter user TestUser with
login = TestUserThank you very much for your advise. The two databases are on the same server. I'm going to give it a try.
So, to clarify, you back up DB_One on server_A and then restore that backup to server_A as DB_One?
Overwriting the DB_One that you just backed up?
More background information:
The backup job is started from a production server, lest's call it Server_Prod. The DB_One from Server_Prod is backed up daily, and restored on Server_A. The Server_A is used for reporting purpose only. There is another database in Server_A, DB_Two, which is used to generate reports to clients. In Server_A, the TestUser is used to retrieve data from both DB_One and DB_Two. The problem is that after DB_One restored from production copy to Server_A, the TestUser's permission to access DB_One is lost. So the TestUser is unable to retrieve data from both databases anymore.
June 9, 2018 at 7:00 am
jay-125866 - Saturday, June 9, 2018 6:30 AMjasona.work - Friday, June 8, 2018 2:30 PMjay-125866 - Friday, June 8, 2018 12:39 PMjasona.work - Friday, June 8, 2018 12:06 PMIt sounds like a step is missing in your description somewhere.Is DB_One being restored to the same server it was backed up on, or a different server (or instance?) If it's being restored on the same server, is it to a different name or are you over-writing the existing database?
I'm presuming your setup is something like:
Server_A
>>> DB_One
>>> DB_TwoLastly, MS suggests rather than using sp_change_users_login, you instead do:
use [DBName];
alter user SQLUser with
login = SQLLoginSo for your situation, it would look something like:
use [DB_One];
alter user TestUser with
login = TestUserThank you very much for your advise. The two databases are on the same server. I'm going to give it a try.
So, to clarify, you back up DB_One on server_A and then restore that backup to server_A as DB_One?
Overwriting the DB_One that you just backed up?More background information:
The backup job is started from a production server, lest's call it Server_Prod. The DB_One from Server_Prod is backed up daily, and restored on Server_A. The Server_A is used for reporting purpose only. There is another database in Server_A, DB_Two, which is used to generate reports to clients. In Server_A, the TestUser is used to retrieve data from both DB_One and DB_Two. The problem is that after DB_One restored from production copy to Server_A, the TestUser's permission to access DB_One is lost. So the TestUser is unable to retrieve data from both databases anymore.
OK, that makes a lot more sense to explain what you're seeing. I'm presuming that the user is a SQL Login, not a Domain account as well.
So, yes, you are running into an "orphan user," and the user will be orphaned every time you restore the database, even if you "fixed it" the last time you restored. There's two ways you can deal with it. Probably the overall least painful way is to add a step to your restore process to "de-orphan" the user with the "ALTER USER" script above. The other option would be, on Server_A, to drop and recreate the login with the same SID (Security IDentifier) as it has on the production server. Glenn Berry explains the process here: https://www.sqlskills.com/blogs/glenn/how-to-avoid-orphaned-database-users-with-sql-server-authentication/
I have used that process when I migrated to new servers to avoid having a LOT of orphans to clean up after the migration, so it does work.
June 9, 2018 at 9:05 am
jasona.work - Saturday, June 9, 2018 7:00 AMjay-125866 - Saturday, June 9, 2018 6:30 AMjasona.work - Friday, June 8, 2018 2:30 PMjay-125866 - Friday, June 8, 2018 12:39 PMjasona.work - Friday, June 8, 2018 12:06 PMIt sounds like a step is missing in your description somewhere.Is DB_One being restored to the same server it was backed up on, or a different server (or instance?) If it's being restored on the same server, is it to a different name or are you over-writing the existing database?
I'm presuming your setup is something like:
Server_A
>>> DB_One
>>> DB_TwoLastly, MS suggests rather than using sp_change_users_login, you instead do:
use [DBName];
alter user SQLUser with
login = SQLLoginSo for your situation, it would look something like:
use [DB_One];
alter user TestUser with
login = TestUserThank you very much for your advise. The two databases are on the same server. I'm going to give it a try.
So, to clarify, you back up DB_One on server_A and then restore that backup to server_A as DB_One?
Overwriting the DB_One that you just backed up?More background information:
The backup job is started from a production server, lest's call it Server_Prod. The DB_One from Server_Prod is backed up daily, and restored on Server_A. The Server_A is used for reporting purpose only. There is another database in Server_A, DB_Two, which is used to generate reports to clients. In Server_A, the TestUser is used to retrieve data from both DB_One and DB_Two. The problem is that after DB_One restored from production copy to Server_A, the TestUser's permission to access DB_One is lost. So the TestUser is unable to retrieve data from both databases anymore.OK, that makes a lot more sense to explain what you're seeing. I'm presuming that the user is a SQL Login, not a Domain account as well.
So, yes, you are running into an "orphan user," and the user will be orphaned every time you restore the database, even if you "fixed it" the last time you restored. There's two ways you can deal with it. Probably the overall least painful way is to add a step to your restore process to "de-orphan" the user with the "ALTER USER" script above. The other option would be, on Server_A, to drop and recreate the login with the same SID (Security IDentifier) as it has on the production server. Glenn Berry explains the process here: https://www.sqlskills.com/blogs/glenn/how-to-avoid-orphaned-database-users-with-sql-server-authentication/
I have used that process when I migrated to new servers to avoid having a LOT of orphans to clean up after the migration, so it does work.
I followed Glenn Berry's article and resolved my issue. Thank you very much for your help!
June 9, 2018 at 9:36 am
jay-125866 - Saturday, June 9, 2018 9:05 AMjasona.work - Saturday, June 9, 2018 7:00 AMjay-125866 - Saturday, June 9, 2018 6:30 AMjasona.work - Friday, June 8, 2018 2:30 PMjay-125866 - Friday, June 8, 2018 12:39 PMjasona.work - Friday, June 8, 2018 12:06 PMIt sounds like a step is missing in your description somewhere.Is DB_One being restored to the same server it was backed up on, or a different server (or instance?) If it's being restored on the same server, is it to a different name or are you over-writing the existing database?
I'm presuming your setup is something like:
Server_A
>>> DB_One
>>> DB_TwoLastly, MS suggests rather than using sp_change_users_login, you instead do:
use [DBName];
alter user SQLUser with
login = SQLLoginSo for your situation, it would look something like:
use [DB_One];
alter user TestUser with
login = TestUserThank you very much for your advise. The two databases are on the same server. I'm going to give it a try.
So, to clarify, you back up DB_One on server_A and then restore that backup to server_A as DB_One?
Overwriting the DB_One that you just backed up?More background information:
The backup job is started from a production server, lest's call it Server_Prod. The DB_One from Server_Prod is backed up daily, and restored on Server_A. The Server_A is used for reporting purpose only. There is another database in Server_A, DB_Two, which is used to generate reports to clients. In Server_A, the TestUser is used to retrieve data from both DB_One and DB_Two. The problem is that after DB_One restored from production copy to Server_A, the TestUser's permission to access DB_One is lost. So the TestUser is unable to retrieve data from both databases anymore.OK, that makes a lot more sense to explain what you're seeing. I'm presuming that the user is a SQL Login, not a Domain account as well.
So, yes, you are running into an "orphan user," and the user will be orphaned every time you restore the database, even if you "fixed it" the last time you restored. There's two ways you can deal with it. Probably the overall least painful way is to add a step to your restore process to "de-orphan" the user with the "ALTER USER" script above. The other option would be, on Server_A, to drop and recreate the login with the same SID (Security IDentifier) as it has on the production server. Glenn Berry explains the process here: https://www.sqlskills.com/blogs/glenn/how-to-avoid-orphaned-database-users-with-sql-server-authentication/
I have used that process when I migrated to new servers to avoid having a LOT of orphans to clean up after the migration, so it does work.I followed Glenn Berry's article and resolved my issue. Thank you very much for your help!
You're quite welcome!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply