Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

User permissions Related Expand / Collapse
Author
Message
Posted Thursday, July 31, 2014 4:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:11 PM
Points: 26, Visits: 111
Hi friends,

user need access to check which user has executed delte/drop queries.
for this which access we need to give to that user..

need help asap..


Thanks,
SAM


Regards,
SAM
***Share your knowledge.It’s a way to achieve immortality----Dalai Lama***
Post #1598530
Posted Friday, August 1, 2014 12:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:20 AM
Points: 5,231, Visits: 5,106
Unless your tracing all your queries you wont know about deletes, for drops if your quick enough you could look inside the default trace, but that gets overwritten quite quickly so you would need a process to load in the trace files before they get overwritten.

Easiest option is to put a DDL trigger on the databases in question for the DROPs and then put DML triggers on the tables you want to know who's deleting from to farm out the username and a timestamp when a record is deleted. But this will be a going forward audit, not a back in time audit.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1598591
Posted Friday, August 1, 2014 1:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:46 AM
Points: 210, Visits: 638
Create a trace and filter all keywords (insert/update/dete etc) and log to a table....

Cheers
Post #1598607
Posted Friday, August 1, 2014 2:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 5,043, Visits: 10,569
The easiest tool for this job is an audit.
More information can be found here: http://msdn.microsoft.com/it-it/library/cc280386.aspx


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1598615
Posted Monday, September 8, 2014 6:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:19 AM
Points: 315, Visits: 510
You could give the user the ALTER TRACE permission and let them run the Profiler when they need it.

Just be sure to tell them how NOT to use it first though!
Post #1611415
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse