x

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.

more ▼

asked Oct 25, 2010 at 02:06 AM in Default

avatar image

Leo
1.6k 55 59 62

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Oct 25, 2010 at 02:16 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

Thanks. that solved my problem.

Oct 25, 2010 at 03:26 AM Leo
(comments are locked)
10|1200 characters needed characters left

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';

more ▼

answered Oct 25, 2010 at 02:27 AM

avatar image

Cyborg
10.8k 37 58 51

@Cyborg, yes that is the another way of fixing orphaned users, however that is now deprecated.

Oct 25, 2010 at 02:28 AM Kev Riley ♦♦

Oh! thank you Kev its new to my knowledge. I thought its will be removed in future release of SQL Server.

Oct 25, 2010 at 02:32 AM Cyborg
  • @Cyborg, You where too quick for me this time.

Oct 25, 2010 at 02:34 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
  • 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

Sorry for recommending something that is going to be removed!

more ▼

answered Oct 25, 2010 at 02:33 AM

avatar image

Håkan Winther
16.6k 38 46 58

I'm going to miss this too :( ...talked about it here http://ask.sqlservercentral.com/questions/2039/identifying-orphaned-users

Oct 25, 2010 at 02:37 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2221
x1208
x123

asked: Oct 25, 2010 at 02:06 AM

Seen: 1880 times

Last Updated: Oct 25, 2010 at 02:06 AM

Copyright 2018 Redgate Software. Privacy Policy