SQL Server – Password & Privilege Checking

SQL Queries to Check Passwords & Privilege Assignments

This article introduces a few SQL queries to gather information about user passwords and privileges and permissions assigned on SQL Server systems.

• Question 1: Is there a way of checking for user accounts that have a blank password?

• Question 2: How do you check if a password is set to a common string?

• Question 3: How do you check if a password is set to a user's login name?

• Question 4: Can you run a query to find out what server privileges a login has been granted?

• Question 5: Can you run a query to find out what database privileges a user has been granted?

• Question 6: Is it possible to find out what permissions a user has on a table through a query?

Checking for blank SQL Server user passwords

The sys.sql_logins view provides a password_hash column and the PWDCOMPARE function can be used to check for an empty string value.

The first WHERE clause checks for passwords with the format implemented since SQL Server 2000. The second WHERE clause checks for passwords with the format implemented prior to SQL Server 2000.

 Checking for Common passwords

To check for common passwords simply place the password to test for as the first argument of the PWDCOMPARE function.

Checking for password matching login name

For this one simply use the name column from sys.sql_logins as the first argument to PWDCOMPARE.

Retrieving information about login/user privileges and permissions

The following views are available that will assist with retrieving information about privilege and permission assignments:

• sys.server_principals

• sys.database_principals

• sys.server_permissions

• sys.database_permissions

It can be quite long winded to check for a user's relative permissions and privileges so SQL Server provides a really useful function called fn_my_permissions which can help.

 Server Privileges granted to a login

Database Privileges granted to a database user

Permissions on an object

In the following example student4 has been granted SELECT privilege on a table called Person.Person, and a role called Training, which student4 belongs to, has been granted SELECT privilege on a table called Production.Product.

You can learn more about the audit and security of SQL Server from SQL Server Audit & Security Training Course.