question

Leo avatar image
Leo asked

Database User Permissions

Hi, Something come up while I was restoring the Database this morning from one server to another server. There is a user called ***UserA*** and it has Database Fixed Role 'SetupAdmin' on *DB1* on *SVR1*. It is working fine whoever login as UserA. [This is **Production Server**] Same setup on *SVR2*, it has ***UserA*** with DB Fixed Role 'SetupAdmin' on *DB1*. [That server is **Dev-Server**] Restored **DB1** from **Production** to **Dev-Server**. UserA is no longer access into the DB1 on Dev-Server after restored until I need to login as 'sa' or something and set the permission again for UserA under 'User Mapping'. That is wasting the time for me to add the user 'UserA' every time I restored the Database. Anyway can we get around it? Thanks.
sql-server-2008sqlpermissions
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
The issue is that the underlying identifier for UserA on SVR1, is not the same as the UserA on SVR2 - hence you get these 'orphaned users' whenever you restore a backup from another server. The simplest option would be to script the resolution and apply this at the end of your backup process. You are restoring a database, so I presume you are using an account that has permissions enough to do this. To de-orphan the users use ALTER USER UserA WITH LOGIN = UserA Alternatively script the whole user deletion and recreation for the server level.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks. that solved my problem.
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
adding to kev Rileys answer you can also use sp_change_users_login procedure to fix orphan user issuess The following example shows how to map an existing userA to a login of the same name.

EXEC sp_change_users_login 'Auto_Fix', 'UserA', NULL, 'Password';

3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Cyborg, yes that is the another way of fixing orphaned users, however that is now deprecated.
0 Likes 0 ·
Oh! thank you Kev its new to my knowledge. I thought its will be removed in future release of SQL Server.
0 Likes 0 ·
+1 @Cyborg, You where too quick for me this time.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
+1 To Kev, his solution is the right way of doing it. I love the sp_change_users_login procedure that will be removed in a future release, and I want to show what we will miss when it's gone. To find any 'orphaned users' you can use: EXEC sp_change_users_login 'report' To fix the 'orphaned users' you can use: EXEC sp_change_users_login 'Auto_Fix', 'UserA', NULL, 'B3r12-3x$098f6'; GO But as I said before, this is a "deprecated feature" and is not for new development projects. Read more about it in [ http://msdn.microsoft.com/en-us/library/ms174378.aspx][1] Sorry for recommending something that is going to be removed! [1]: http://msdn.microsoft.com/en-us/library/ms174378.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'm going to miss this too :( ...talked about it here
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.