Permission Denied

Question

Permission Denied

SkyBVI

SSCoach

Points: 15128
More actions
July 27, 2010 at 12:01 pm

#142122

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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

SkyBVI SSCoach Points: 15128 More actions · Answer 2

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

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

ssismaddy SSCarpal Tunnel Points: 4850 More actions · Answer 4

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.

SkyBVI SSCoach Points: 15128 More actions · Answer 5

@ssismaddy

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

sushantkumar1984 (7/27/2010)
@ssismaddy
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

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://www.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

SkyBVI SSCoach Points: 15128 More actions · Answer 7

@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://www.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

SkyBVI SSCoach Points: 15128 More actions · Answer 8

@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

ssismaddy SSCarpal Tunnel Points: 4850 More actions · Answer 9

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!!

SkyBVI SSCoach Points: 15128 More actions · Answer 10

@ssismaddy

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