We help IT Professionals succeed at work.

Database Trigger help to track broken permission

Favorable
Favorable asked
on
Medium Priority
541 Views
Last Modified: 2012-08-14
How to do we track broken permissions to the masters database at PM daily?

Can somebody provide me with a trigger that will fire if the permission of a particular user is broken from the master database?

This user (…….\.........) is mapped to the master database and granted read/write permissions, but we noticed in the past three nights that this mapping is broken at a certain time every evening, and we want to be able to track what is causing it.
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Hi,

You could see if a Profiler trace can tell you what is happening. Just automate the trace to start say 5 mins before the critical moment and say 5 mins after.

I do wonder why though a user needs specific rights to master db? especially write priveledges.

I have used Windows Schduler to get the job done in the past.
Here is a way to achieve it in SQL Agent.

http://www.mssqltips.com/sqlservertip/1715/scheduling-a-sql-server-profiler-trace/

This could be helpful on the profiler side

http://www.databasejournal.com/features/mssql/article.php/3887996/Determining-Object-Access-Using-SQL-Server-Profiler.htm

Author

Commented:
Thanks
CERTIFIED EXPERT

Commented:
Hey Fav,

Can you let us know what you find, would be interesting to know.

T

Author

Commented:
Nothing.  After monitoring for three days, no security issue surfaced again.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.