May 27, 2009 at 4:58 am
Hi,
I have faced an unusual problem.Actually I restore three databases.But after restortaion when I saw the login I found that the perticular login ID doesnt have the permission.Why so?If I am changing it manually then also it is not changing showing error 21002.Also i found that some stored procedures are showing in master database but when i run sp_helptext"stored procedure name"then it is showing stored procedure does not exist.
how come this possible and why this happen???????
If anybody can help me out in this regared then I will be very grateful to u.
THanks.
Sunanda
May 27, 2009 at 5:18 am
I restore three databases.But after restortaion when I saw the login I found that the perticular login ID doesnt have the permission
Are you restoring on the same server or on a different server
when i run sp_helptext"stored procedure name"then it is showing stored procedure does not exist.
are you running helptext on the same database (master in this case)
May 27, 2009 at 5:43 am
I restore it on a different server.
May 27, 2009 at 5:55 am
I think the user is not mapped correctly for particular dB
[font="Comic Sans MS"]+++BLADE+++[/font]:cool:
May 27, 2009 at 5:59 am
then can u plz me out to resolve the problem????
May 27, 2009 at 6:25 am
Security--> logins --> properties --> usermapping
In that you can map the user to particular databases.also provide db_owner permission too
[font="Comic Sans MS"]+++BLADE+++[/font]:cool:
May 27, 2009 at 9:49 pm
Hi,
I am not getting it.
Will u plz tell me in details?????????
May 27, 2009 at 10:08 pm
The way to fix an unmapped SQL Server login is to execute the command:
exec sp_change_users_login 'auto_fix', 'loginname'
This updates the SID of the user in the database to match the SID of the login on the server. The SID of a login is unique to each server, so when you restore a database to a different server the SIDs won't match any more. Like most things in SQL Server, it's not the name of the thing (in this case the login) that matters, but rather the identifier (in this case the SID).
May 27, 2009 at 11:58 pm
Hi,
when I am running this command in T_SQL logging in as administrator then the following message is coming.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 0.
So can u plz tell me what shall i do next???????????????????????????
Thanks
May 28, 2009 at 12:09 am
when I am running this command in T_SQL logging in as administrator then the following message is coming.
I hope you replaced the 'loginname' with actual orphaned ID(a user id for which login is missing).
You can find out the list of orphaned users by executing this statement.
EXEC sp_change_users_login 'Report';
Once you have the list of orphaned users, you can start fixing them as mentioned in earlier post.
May 28, 2009 at 1:00 am
The perticular user is not showing in the restored database.It is showing some different user.And this time when I run the above command it is showing that updating 1 user but when I run the stored procedure which is made from that user is not running.But when I write user.stored_procedure it is running.
So now what can I do now.
Thanks
May 28, 2009 at 5:48 am
Can anybody tell me why this happen?
That means in which situation and how can we avoid it??????????
May 29, 2009 at 9:01 am
You will encounter this anytime you move a database from one server to another. These are called "orphaned users". This is a very common issue that DBAs must resolve. The postings from Glenn D and ps describe what you need to do to find and fix these orphaned users.
If you need further information, and help, I suggest that you search for "orphaned users" on either BOL or Google.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply