September 14, 2009 at 2:16 am
On our test server – the SQL 2005 default instance, somehow every now and then the permission using login 'TestLogin' is getting changed for two databases.
This user should have db_datareader but every now and then the user gets db_datareader and db_denydatareader permissions set for two databases.
Please help, with suggestions
September 14, 2009 at 4:46 am
rinu philip (9/14/2009)
On our test server – the SQL 2005 default instance, somehow every now and then the permission using login 'TestLogin' is getting changed for two databases.This user should have db_datareader but every now and then the user gets db_datareader and db_denydatareader permissions set for two databases.
Please help, with suggestions
hmm, unless you have manual changes going on, could be an auditing issue, do you have any sql jobs that re-evaluate logins/permissions, or do you have any triggers set up, either at the database or server level..
Ah, are these databases being restored from a production server on a periodic basis, where the permissions for that login would be different?
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 14, 2009 at 5:13 am
thanks, i will check with the suggestions specified,however, can you tell me if I run a trace can I find out how this is happening, also what options I need to select in the sql trace. thanks
September 14, 2009 at 5:24 am
rinu philip (9/14/2009)
thanks, i will check with the suggestions specified,however, can you tell me if I run a trace can I find out how this is happening, also what options I need to select in the sql trace. thanks
Ok, not sure that a trace would be the best thing, depends on how long you can leave it running, but to answer your question.
The event group is Security Audit, and for example...
Audit Add member to DB Role Event
depends on what information you want to retrieve, but the events in that group will give you what you need.
to be honest, if this is a test server, you can manually add and remove the db permissions for this login while the trace is running, until you are happy with the information that you are getting.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 14, 2009 at 6:27 am
thanks alot for your replies..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply