locked
How do you find specific user information about a database in SQL 2005? RRS feed

  • Question

  • Hi,

    I am in the process of migrating SQL 2005 databases to SQL 2008 R2. One of the problems I have come across is that the administrator for the majority of the databases does not know what a few of the databases are for. I am relatively new to all of our systems and database management, but need to get this knocked out for our team. He has asked me to find out specifics on each database:

    1. Who is accessing them?
    2. When are they being accessed (how often and last time)?

    I checked the properties for each respective database but the information didn’t seem to answer the questions posed. Also, additional research online didn’t help either. Thank you in advance for the help.


    Tony

    Thursday, May 3, 2012 2:49 PM

Answers

All replies

    • Marked as answer by OC_Tony Friday, May 4, 2012 1:10 PM
    Thursday, May 3, 2012 6:48 PM
  • It looks like the audit specification is for SQL 2008. I am trying to pull the data from SQL 2005 currently. I ran the following query but it doesn't seem like it is showing all sessions or it is just showing a brief period of the sessions:

    SELECT session_id, login_time, host_name, program_name, security_id, login_name, nt_domain, nt_user_name, endpoint_id, last_request_start_time, last_request_end_time, original_security_id, original_login_name, last_successful_logon, last_unsuccessful_logon, unsuccessful_logons

    FROM sys.dm_exec_sessions

    GO

    I am using the results from this query and also the users/roles in the properties of the databases to help decide whether or not to move the databases. What is a bit confusing to me is some databases show when running the sys.dm_exec_sessions query but the user it shows accessed is not located under user/roles in the properties box.

    Thank you for taking the time and help.


    Tony

    Thursday, May 3, 2012 8:40 PM
  • Hello,

    Logins associated with server roles like those logins that have the sysadmin role won’t appear on the security folder of each database.

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

    • Proposed as answer by amber zhang Friday, May 4, 2012 8:11 AM
    Thursday, May 3, 2012 11:27 PM
  • hi you can also ask your problem on the given forum, it may help you.

    http://blog.sqlauthority.com/2007/08/31/sql-server-2005-find-database-status-using-sysdatabases-or-databasepropertyex/

    Friday, May 4, 2012 10:12 AM