rss2
asked on
Script to list all user accounts and which objects they created on SQL Server 2000 and 2005
Hello,
I would like to list all the user accounts on a SQL 2000 database server, which databases they have access to, their privileges, their last activitiy (datetime), and which objects they created/modified/touched in some manner (i.e. selected from which tables, etc.)
Is is possible to glean this information from SQL 2000 in the form of a simple query? If not, what parts of the above question can come out of a simple query of system tables and what no, and what parts of the above question would require looking in the logs? (I'm assuming the bit about their recent activity would come from the logs only..) And what is the easiest way to read the logs for this information?
Lastly, can I get this information more easily from a 2005 database? How does 2005 compare to 2000 in terms of security?
Your earliest response would be very much appreciated! :)
Thank you,
rss2
I would like to list all the user accounts on a SQL 2000 database server, which databases they have access to, their privileges, their last activitiy (datetime), and which objects they created/modified/touched in some manner (i.e. selected from which tables, etc.)
Is is possible to glean this information from SQL 2000 in the form of a simple query? If not, what parts of the above question can come out of a simple query of system tables and what no, and what parts of the above question would require looking in the logs? (I'm assuming the bit about their recent activity would come from the logs only..) And what is the easiest way to read the logs for this information?
Lastly, can I get this information more easily from a 2005 database? How does 2005 compare to 2000 in terms of security?
Your earliest response would be very much appreciated! :)
Thank you,
rss2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In SQL 2005 you can see who created an object and what was the last modification date.
In both SQL Server versions it is not possible (out of the box) to see who touched which table ... you have to setup something for this kind of monitoring yourself.
Hope this helps ...