Problem
I have been tasked with auditing security on my SQL Server. However, this needs to be a somewhat automated process as I don’t want to have to rely on taking screenshots every month to satisfy our auditors. What tables and/or views should I be using and what’s the best way to extract the information out of them?
Solution
The first thing you’ll want to audit is the login information to your SQL Server so that’s what we’ll focus on for this tip. There is one particular table if we’re talking about SQL Server 2000 and there are two catalog views (views that contain information like logins, permissions, etc.) within SQL Server 2005 and 2008 that we’ll target. First, let’s start with SQL Server 2000.
Using syslogins (SQL Server 2000):
The syslogins table contains information on every login that has the potential to access the SQL Server in question. There are a few pieces of information we’re always interested in:
- The name of the login
- When the login was created
- When the login was last modified (either the password was changed or one of the settings on the login was)
- Whether or not the login is a Windows-based user account
- Whether or not the login is a Windows-based security group
A simple query like the one below will get this information:
SELECT
[name]
, createdate
, updatedate
, isntuser
, isntgroup
FROM sysloginsNow keep in mind that I did not include when the login was last used. SQL Server does not keep this information stored. If you’re auditing for successful logins you’ll find it in the SQL Server log or the Application event log for the operating system. That means you’ll need to extract it. We see this question fairly frequently and unfortunately, SQL Server, regardless of version, doesn’t keep track of it. You’ll also see nothing about password enforcement. This wasn’t added as a feature to SQL Server until SQL Server 2005. Therefore, a login can attempt to connect to SQL Server as many times as it wants without being locked out. You won’t notice something is up unless you are at least logging for audit failures and checking the logs periodically.
There is one other bit of information we’re interested in and that’s the server roles a particular login may be a member of. Again, we can go back to syslogins or we can use the stored procedure sp_helpsrvrolemember. An example of both is given to detect who are members of the sysadmin fixed server role.
SELECT
[name]
, sysadmin
FROM syslogins
WHERE sysadmin = 1And the use of sp_helpsrvrolemember:
EXEC sp_helpsrvrolemember ‘sysadmin’If you need to audit more than just for sysadmin role members, then both syslogins and sp_helpsrvrolemember will do. They will provide information on all fixed server roles.
Using sys.server_principals and sys.sql_logins (SQL Server 2005/2008):
With SQL Server 2005 came better dynamic management and catalog views which provide more information (in general) than in previous versions. In the place of syslogins, we can use sys.server_principals or sys.sql_logins. There is a difference between the two, so let’s talk about that. The catalog view sys.server_principals roughly corresponds to syslogins from SQL Server 2000. The catalog view sys.sql_logins provides more information for SQL Server specific logins because of new features in SQL Server 2005 (also included in 2008) such as password expiration and password lockout. I won’t go into details here about what those features are, as they were covered in a previous tip.
If we want to return the same information as we did from syslogins, we can by querying sys.server_principals like so:
SELECT
[name]
, create_date
, modify_date
, CASE [type]
WHEN ‘U’ THEN 1
ELSE 0
END AS ‘isntuser’
, CASE [type]
WHEN ‘G’ THEN 1
ELSE 0
END AS ‘isntgroup’
FROM sys.server_principals Because SQL Server 2005/2008 support password policy enforcement, there is more information to be gained from sys.sql_logins. A follow-on query from a previous tip is this:
SELECT
[name]
, is_policy_checked
, is_expiration_checked
FROM sys.sql_logins Now, with respect to determining server role membership, we can still use sp_helpsrvrolemember, like with SQL Server 2000, but we can’t just use sys.server_principals like we could use just syslogins with SQL Server 2000. We’ll also have to add in sys.server_role_members. The sp_helpsrvrolemember syntax is the same, so let’s just look at the new syntax due to the catalog views:
SELECT
sp1.[name] AS ‘login’
, sp2.[name] AS ‘role’
FROM sys.server_principals sp1
JOIN sys.server_role_members srm
ON sp1.principal_id = srm.member_principal_id
JOIN sys.server_principals sp2
ON srm.role_principal_id = sp2.principal_id
WHERE sp2.[name] = ‘sysadmin’In a follow-on tip we’ll look at how to take this a step further and look in each database to determine which ones a particular login has access to.
Next Steps
- Review the tips related to security
- Take a look at this previous tip related to this topic How to configure password enforcement options for standard SQL Server logins
