locked
Analysing User Accounts for Security RRS feed

  • Question

  • I have been asked to investigate and produce analysis of all accounts on a SQL cluster. I need to produce information regarding level of authority, usage history, create date, etc etc. I'm running MS SQL Server Management Studio. Am I able to achieve what's required using that or do I need additional tools?

    Monday, March 16, 2015 11:42 AM

Answers

All replies

  • I think we'll need more details about "etc, etc" in order to answer your question.  What SQL Server version and edition are you using?

    There's a bit of information available on accounts and permissions available in the security catalog views but limited history is available unless you capture it with a trace or audit.  Do you need a history of just each the time each account logged in or was changed, or more granular information like database object accessed?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, March 16, 2015 12:23 PM
  • The etc etc just tripped off my fingers. My task is to find out exactly what user accounts have been set up on the server and what authorities they have to each database. To know when they last logged in would possibly be useful to help me determine if the account is still needed. This isn't a task to track a culprit but rather an audit requirement to justify the accounts that are already there. 
    Monday, March 16, 2015 1:09 PM
  • There is no default mechanism in SQL Server which will tell you the last login date. For that you need to do some kind of auditing.

    to find the created date run the below query

    select name, createdate from master.sys.syslogins

    the below will help you with the permissions a login has

    http://www.sqlservercentral.com/Forums/Topic1226870-359-1.aspx#bm1230153


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Proposed as answer by Michelle Li Tuesday, March 17, 2015 1:14 PM
    • Marked as answer by Michelle Li Tuesday, March 24, 2015 2:47 AM
    Monday, March 16, 2015 1:53 PM
  • You might be interested in the scripts that I have posted on the SQL Server wiki at Database Engine Effective Permissions http://social.technet.microsoft.com/wiki/contents/articles/15180.effective-database-engine-permissions.aspx

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Michelle Li Tuesday, March 17, 2015 1:13 PM
    • Marked as answer by Michelle Li Tuesday, March 24, 2015 2:47 AM
    Monday, March 16, 2015 3:14 PM