December 21, 2009 at 4:07 pm
I've searched and searched on SQL Server Auditing (2005) and there are many various posts and scripts but I have yet to find what I'm looking for and maybe its because it doesn't exists in whcih case I'd just like for a SQL Guru to confirm this.
Within the SECURITY table of SQL Server Properties within SSMS there is an option for Login Auditing and the choices are
None
Failed Logins Only
Sucessful logins only
Both failed and sucessful logins
Assuming any of these other then the first (none) is checked, SQL Server must be storing this information somewhere; where?
As a followup to this...
Can I get verification from a SQL Guru that theres no way, at least in SQL Server 2005 to retrieve DB level access audit info, to find out for example when the last access to a particular DB occurred? We are cleaning up some OLD SQL Servers & DB's and would like to at least backup and then drop any DB's that are not in use anymore. I can't simpluy look at the file properties for the mdf/ldf files because of how drives are managed. For example our network guys recently made some drive changes and now many of the DB's on one SQL Server have this network persons domain acct as the dbo simply because the drive changes were made by him and so ownership was moved to his name.
It really stinks to because now I have no way that I know of to verify who the real DBO was for one of these DB's.
Thanks
Kindest Regards,
Just say No to Facebook!December 21, 2009 at 4:58 pm
First, I don't believe SQL Server actually stores login attempts. You can find that information in the event log and/or SQL Server log.
Second, the only way the owner of the database was changed in your scenario is when the databases were detached and then attached. The user that attached the databases is now the owner of those databases.
And finally, SQL Server does not track audit information on access to a database/table/objects unless you specifically create an auditing mechanism yourself. You can implement a login trigger to capture who is logging in - but I am not sure this will tell you what database is being accessed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 22, 2009 at 12:14 pm
Jeffrey Williams-493691 (12/21/2009)
First, I don't believe SQL Server actually stores login attempts. You can find that information in the event log and/or SQL Server log.Second, the only way the owner of the database was changed in your scenario is when the databases were detached and then attached. The user that attached the databases is now the owner of those databases.
And finally, SQL Server does not track audit information on access to a database/table/objects unless you specifically create an auditing mechanism yourself. You can implement a login trigger to capture who is logging in - but I am not sure this will tell you what database is being accessed.
I do appreciate your replying but let me say this; I don't know if you intended to come off sounding a bit harsh in your response but you did. If SQL Server doesn't store login attempts then why does it even have the option to audit successful logins or failed logins or both? And how then can you get that info in the SQL Log if it doesn;t store it somewhere?
As for the DB owenrr I can tell you that the dbo can change by doing more then simply detaching and attaching the DB's. I don't know why it did this but it did. I neglected to mention this before and perhapos thats my faulty as it may make all the difference in the world but the SQL Server instance I'm referiing to is on a VM instance managed by VMWare. The Network guy who handles the VM setup would not have detached and re-attached the DB's; I don;t even think he knows how to even if he wanted to. What did happen was that he changed around some of the drives in the Virtual setup and after wards the Owner on every DB changed to his Domain acct.
Lastly, since I posted my orginal question I have found my answerr and yes SQL Server does store not only logins but also what DB the user was connecting to.
Thanks for replying.
Kindest Regards,
Just say No to Facebook!December 22, 2009 at 12:29 pm
YSLGuru (12/22/2009)
Jeffrey Williams-493691 (12/21/2009)
First, I don't believe SQL Server actually stores login attempts. You can find that information in the event log and/or SQL Server log.Second, the only way the owner of the database was changed in your scenario is when the databases were detached and then attached. The user that attached the databases is now the owner of those databases.
And finally, SQL Server does not track audit information on access to a database/table/objects unless you specifically create an auditing mechanism yourself. You can implement a login trigger to capture who is logging in - but I am not sure this will tell you what database is being accessed.
I do appreciate your replying but let me say this; I don't know if you intended to come off sounding a bit harsh in your response but you did. If SQL Server doesn't store login attempts then why does it even have the option to audit successful logins or failed logins or both? And how then can you get that info in the SQL Log if it doesn;t store it somewhere?
As for the DB owenrr I can tell you that the dbo can change by doing more then simply detaching and attaching the DB's. I don't know why it did this but it did. I neglected to mention this before and perhapos thats my faulty as it may make all the difference in the world but the SQL Server instance I'm referiing to is on a VM instance managed by VMWare. The Network guy who handles the VM setup would not have detached and re-attached the DB's; I don;t even think he knows how to even if he wanted to. What did happen was that he changed around some of the drives in the Virtual setup and after wards the Owner on every DB changed to his Domain acct.
Lastly, since I posted my orginal question I have found my answerr and yes SQL Server does store not only logins but also what DB the user was connecting to.
Thanks for replying.
Care to share? Others may be interested as well.
December 22, 2009 at 12:32 pm
If you turn on logging successful/failed logins, it will go in the log. Same one you get under the Management tab in SSMS. It's off by default because it's useless data more often than not. Since the majority of login attempts to SQL Server these days are by shared connections from a web server, it doesn't do much good to log all of those, and it just wastes space.
If, on the other hand, you are tracking more specific logins, through SSMS for example, it will do that for you.
The way I detect access to and activity in databases is with server-side traces. It's the most accurate, and it's quite easy to set up. Retired three databases this year after running traces on them for a month and finding that the only activity was automatic backups.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 22, 2009 at 12:46 pm
We've used serverside triggers and database triggers to track logons. It helped us to locate users abusing a userid and password that they should not have been using. Altered the trigger to only allow that login from one specific server, and wow did it create an uproar with the dev staff. They adapted though.
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply