How to get logins info in sql server

we had group of members, every one having their own logins. In our production few user defined stored procedures and application config files.
How to know who, when & how these SP and the config files has been deleted?
And is there any way to audit in future.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

First, get the administrative trace file.

select * from fn_trace_getinfo(NULL)
where property=2
and traceid = 1

Then look into the administrative trace for events of class 47 Object:Deleted Event Class on object types 8727 Stored Procedure:

select * from fn_trace_gettable('x:\path\filename.trc', -1)
where EventClass = 47
and ObjectType=8727

That will give you who deleted the stored procedures.  As for the application config files.  I'm not sure what you're referring to.  Do you have configuration data stored in a table?

As for auditing this, I would highly suggest setting the security on your users to only be able to exec the stored procedures.  They don't need more access than that.  You experience is one of my top reasons for disallowing everyone from having sa privileges.

Let me know if I can be of any further assistance!

Shannon Lowder
Database Engineer
krishna_harikAuthor Commented:
Thanks Shannon,

i could not get the required information from 5 default trace files with 20MB,becoz it consists of last two days information. i want information for last 7 days, but the old data might have been overwritten by default trace files.

I want few things to be known

1.Is it best practice to create and maintain our own trace files (other than default trace files) for Auditing sql server? If yes?
Best practices to maintain user defined trace files (data stored in database tables or flat files,max size,location)?

2.can we take the backup of trace files?

I wouldn't consider it best practices to maintain your own trace files, that would be duplicating a process that already exists.  Backing up the .trc files to a new location shouldn't be too difficult.  You could accomplish that with a dos cmd file, or something more advanced (achronis, etc).

Now, if you want to address the problem of users deleting things they aren't supposed to, I think that would bring you a greater reward.  Since you mentioned everyone has their own logins, do you have all those members set to the  db_datareader role, or do they have a greater level of access than that?

Shannon Lowder
Database Engineer
krishna_harikAuthor Commented:
Thank you very much shannon,

In our environment
We created two different user logins(one is db_owner, second is ddl,read_only,write_only) on each database, i have no idea how the app's developement team using those logins.

How to perform below mentioned process:

I want to create a custom trace with specific maxsize 15mb (create new once it reached 15mb), location. Schedule a job to run the custom trace daily (maintain one week data) and Schedule another job to delete the trace file older than one week. Is it possible?how?



krishna_harikAuthor Commented:
Here is the link below,  where you can some on how to create a custom trace

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.