Systems Engineering and RDBMS

SOX – Tracking properties for the SQL Server Login Accounts

Posted by decipherinfosys on July 21, 2008

Here is a simple SQL to track the information on the login accounts (Windows as well as SQL Server accounts):

select
name as login_name,
type_desc as login_type,
LOGINPROPERTY(name, ‘PasswordLastSetTime’) as Pswd_last_Set,
LOGINPROPERTY(name, ‘IsLocked’) as Locked_Account,
LOGINPROPERTY(name, ‘IsExpired’) as Expired_Login,
LOGINPROPERTY(name, ‘IsMustChange’) as Must_Change_On_Login,
LOGINPROPERTY(name, ‘HistoryLength’) as Account_Tracked_For,
LOGINPROPERTY(name, ‘LockoutTime’) as Date_Account_Locked_Out
from sys.server_principals
where type in (‘S’, ‘U’)
order by type

It uses the function LOGINPROPERTY() and lists out the different login names, their type and then selective properties that you are interested in tracking.  For SOX compliance as well as SAS70 requirements, it is good to have this SQL run every now and then in order to collect the information.  Also, there could be alerts set up based on the data set that is returned in order to alert the admin about expired/locked accounts etc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: