SQL Server – Password & Privilege Checking

SQL Queries to Check Passwords & Privilege Assignments

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.