Solved

Script to list all user accounts and which objects they created on SQL Server 2000 and 2005

Posted on 2007-11-29
2
1,840 Views
Last Modified: 2008-08-25
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
0
Comment
Question by:rss2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20372542
In SQL 2000 you cannot see who created an object, only who is the owner of the object but is not necessarily the one who created it! Also in SQL 2000 you cannot see when an object was modified.
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 ...
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20372640
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.)

In SQL 2000, the creation data of a SQL object (table/view etc) is logged in

select crdate, objecT_name(id) from sysobjects

To list users, go to Enterprise Manager and look up user list to check their permissions. The level of customizability of permissions allow/deny is so complex that no single query can tell you the full extent of a users's allowances.  As far as system roles, this probably comes close

select * from master..syslogins

There is no db system I am aware of that can tell you when a user last "touched" an object, not even 2005.

Selects are never logged, so you can't use the log for that. but for create db, update, delete etc (assuming you have an appropriate log level), you can use log viewing tools to check the last activity, to a certain period of time.

2005 security is more tightened from 2000, and allows more roles, as well as more customizability.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question