July 27, 2010 at 12:01 pm
When a user 'abc' is trying to access the database'xyz' on the server, the error he gets is :
{Microsoft} {odbc sql server driver} the server principal 'abc' is not able to access the database 'xyz' under the current security context.
I checked the xyz-->security --> users-->'abc' -- he is added there and he has schema added to his name and has database role membership as DYNGRP...
Also i checked in hte server-->security-->logins-->'abc', he is added there also...
What could be the problem
Thanks,
Sushant
Regards
Sushant Kumar
MCTS,MCP
July 27, 2010 at 12:11 pm
sushantkumar1984 (7/27/2010)
When a user 'abc' is trying to access the database'xyz' on the server, the error he gets is :{Microsoft} {odbc sql server driver} the server principal 'abc' is not able to access the database 'xyz' under the current security context.
I checked the xyz-->security --> users-->'abc' -- he is added there and he has schema added to his name and has database role membership as DYNGRP...
Also i checked in hte server-->security-->logins-->'abc', he is added there also...
What could be the problem
Thanks,
Sushant
Does this only happen for one specific user?
This appears to be happening in an application. Have you verified the connection string to be correct?
What is the default database for the user?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 27, 2010 at 12:59 pm
Does this only happen for one specific user?
This appears to be happening in an application. Have you verified the connection string to be correct?
What is the default database for the user?
Yes, one specific user.
Ya, it happens when he tries to log into microsoft dynamics GP 'xyz' database.
Other user can log into that, that means the string would be correct i guess..
default database is master.
Regards
Sushant Kumar
MCTS,MCP
July 27, 2010 at 1:03 pm
Try changing the default database to the dynamics database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 27, 2010 at 1:16 pm
If it is SQL Login, may be the login might be out of sync. you can try
EXEC sp_change_users_login 'Update_One', 'abc', 'abc'
If this doesn't work, you may have to open the ODBC sources in ControlPanel and check if you are able to connect to the server from there.
July 27, 2010 at 2:43 pm
I think that did the trick...
but when I executed
Exec sp_change_users_login 'REPORT'
to see which all other users became orphan, there were 300 more...:(
so.. Is there a way to bring all users back in a script or do i have to implement 300 times the same
command..
Thanks,
Sushant
Regards
Sushant Kumar
MCTS,MCP
July 27, 2010 at 2:46 pm
sushantkumar1984 (7/27/2010)
@ssismaddyI think that did the trick...
but when I executed
Exec sp_change_users_login 'REPORT'
to see which all other users became orphan, there were 300 more...:(
so.. Is there a way to bring all users back in a script or do i have to implement 300 times the same
command..
Thanks,
Sushant
Did you restore this database to a different server?
There are scripts here at SSC and on the web that will help you fix all users at once.
Here is one such script (I have not used it - just as an example)
http://qa.sqlservercentral.com/scripts/Miscellaneous/31308/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 27, 2010 at 3:03 pm
@cirqued
Did you restore this database to a different server?
There are scripts here at SSC and on the web that will help you fix all users at once.
Here is one such script (I have not used it - just as an example)
http://qa.sqlservercentral.com/scripts/Miscellaneous/31308/
Yes, I restored the database to a different server.
ok, i will use and let u know.
Thanks,
Sushant
Regards
Sushant Kumar
MCTS,MCP
July 28, 2010 at 8:47 am
@cirquedeSQL
When I ran that script, out of 300 orphaned users around 250 got syncronized.
I came to know about the 50 by running the--
exec sp_change_users_login 'REPORT'
When I tried to syncronize those 50 individually by--
exec sp_change_users_login 'update_one', @login, @login
then it throwed error:-
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 131
Terminating this procedure. The Login name 'ann' is absent or invalid.
I checked those in database-->security-->users--> those(50) are present, then why it says absent or invalid??
How could those 50 be brought back in sync??
Thanks,
Sushant
Regards
Sushant Kumar
MCTS,MCP
July 28, 2010 at 8:54 am
Did you check if those logins exist in SQL Server Security? Probably, they might be existing under database logins but not under sql server security!!
July 28, 2010 at 9:19 am
Did you check if those logins exist in SQL Server Security? Probably, they might be existing under database logins but not under sql server security!!
Ya, right maddy....They exist in database logins but are absent from sql server security..
then why did they come in the list of orphaned users?
thanks,
Sushant
Regards
Sushant Kumar
MCTS,MCP
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply