sql queries for logins and password policy

We need to audit a number of sql server installations, ranging from 2000 - 2008.

I need a query (if different for each release of SQL one per release) to list:

1) details of the password policy per sql login (i.e.password expirty, complexity, lockout values etc)

2) details of all sql server authentication logins and their server level permissions (i.e. sysadmin, securityadmin)

3) guest account status per database (i.e. enabled, not enabled).
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve WalesSenior Database AdministratorCommented:
The below applies for 2005+

Password Policy per SQL Login is only a flag for on or off.  If the Password Policy flag is checked, then the Windows Password Policy from the operating system are enforced.

Check the CREATE LOGIN documentation for the details on what happens when CHECK_POLICY and CHECK_EXPIRATION are set.

You can see the settings per SQL user by checking columns is_policy_checked and is_expiration_checked in sys.sql_logins

Doco for sys.sql_logins from Books Online: http://msdn.microsoft.com/en-us/library/ms174355.aspx

For complexity, lockout values etc, I believe you need to go to the OS for that (and I'm not even 100% certain it will lock out the account in SQL Server

For SQL Server Authentication Logins:

select * from sys.server_principals where type in ('U','G') - will show you the logins and groups that can access a SQL Server via Windows Authentication.

Books Online for server_principals: http://msdn.microsoft.com/en-us/library/ms188786.aspx

If you want to see which login has access to which server roles, use sys.server_role_members - something like this:

select b.name + ' has ' + c.name
from sys.server_role_members a
join sys.server_principals b on a.member_principal_id = b.principal_id
join sys.server_principals c on a.role_principal_id = c.principal_id

Open in new window


Books Online for Server_role_members: http://msdn.microsoft.com/en-us/library/ms190331.aspx

For guest you could query the is_disabled column of sys.server_principals where name = 'guest'.


I don't have a SQL Server 2000 system readily available to me to test on, but I'll see what I can did up.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve WalesSenior Database AdministratorCommented:
For SQL Server 2000, this document looks highly relevant:

http://msdn.microsoft.com/en-us/library/fooa616fce9-b4c1-49da-87a7-9d6f74911d8f.aspx

Try syslogins for information on your logins - here's the BOL page for that: http://msdn.microsoft.com/en-us/library/ms178593.aspx

It has columns indicating if each user is a member of one of the fixed server roles and the column hasaccess should be enough to tell disabled / enabled.

The other columns isntname, isntgroup and isntuser should be able to separate SQL Server user, Windows User and Windows Group apart from each other.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.