Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lazerath
Constraint Violating Yak Guru
343 Posts |
 Posted - 2006-01-25 : 15:34:51
|
| Hello,My searches haven't turned up much so far, so I thought I'd be able to tap the knowledge of all you super intelligent DBAs.We have these pesky auditors that want to make sure we are regularly reviewing who has access to our SQL servers. I'm seeking any prebuilt reports that show me the logins and authorizations. We can purchase software if it makes our life easier, so if there is some third-party tools it's not out of the question.Any pointers toward my objectives?I appreciate any and all responses.- Laz |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2006-01-25 : 17:09:43
|
| I ran across the SQL Server Health and History Reports, but it seems that what I seek is not included in that suite. They are very interesting and definately will be useful for future audits, though.Still looking for some nice, formated output of logins and authorizations... |
 |
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2006-01-25 : 17:29:24
|
| Try Idera Compliance Manager. I saw their solution awhile back and was impressed.www.idera.comJon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2006-01-27 : 12:05:12
|
| Thanks Jon. I checked out the idera product and it offers some really good compliance information.I am still interested in just pulling authorizations by user. Any help on a query? |
|
|
|
JeffK95z
Starting Member
19 Posts |
Posted - 2006-01-30 : 12:28:23
|
| I'm a fairly new SQL DBA, and I wanted to do what your looking into as well... And learn about how server-side traces work...here's what I did:1) I used SQL Profiler to create the trace I wanted, then copied that output and created it as a server-side trace It captures hostname, user, database, text that was executed etc...2) I have some agent jobs setup to stop/run this trace every 12 hours. As soon as the current trace is stopped, I import it into a security database and re-start the trace. (you can't access the trace log file while its running sadly).3) Each night, I have a summary of the pervious day's active emailed to me.This summary shows me:NTUserNameHostNametotal_readstotal_writestotal_cpu_usage_in_minutesso in a manner of seconds I can get a good idea of who was using the server yesterday, how much they were doing, and where they did it from.It tooks me days and days and days to get this ironed out, but mostly cause i had no idea what i was doing when i started, hehe.but you may want to look into this method. Although there might be a much easier way!! I have found i tend to do things the hard way :)jeff |
|
|
|
|
|
|
|
|
Reporting on Authentication and Authorization