SQL Server Security Functions

By: Joe Gavin   |   Updated: 2019-05-23   |   Comments   |   Related: > Security

Problem

I am interested in learning more about SQL Server security. Are there any functions that can help me identify logins, database users, permissions and more? Can you provide some simple examples?

Solution

The following is a list of SQL Server Security Functions with some examples tested on SQL Server 2016 and 2017.

For the examples in this tip, the following objects have been setup:

• Server
  • SQL Server Login - 'Joe' with password 'password'
  • Server Roles Assigned to Joe - securityadmin
• Database
  • Database - security-test
  • Database User - JoeUser
  • Database Roles Assigned to JoeUser - db_datareader
  • Default schema for JoeUser is 'dbo'
  • There is a table named Comments in database security-test and JoeUser has been assigned INSERT permissions.

SQL Server Security Functions

Following are different SQL Server functions that can be used to get security related information.  For all of these examples, they are being run using login Joe in database security-test.

SQL Server CURRENT_USER Function

Returns name of the current database user. Note, CURRENT_USER returns the same info as SELECT USER_NAME().

-- should return JoeUser
SELECT CURRENT_USER

SQL Server DATABASE_PRINCIPAL_ID Function

Returns ID of the database principal supplied or the ID of the current database user if no principal supplied. Note, this is the uid in sys.sysuysers corresponding to the user and not the sid that ties it to the login.

SELECT DATABASE_PRINCIPAL_ID('JoeUser')
SELECT DATABASE_PRINCIPAL_ID()

SQL Server HAS_PERMS_BY_NAME Function

Returns a 1 if the current database user has the specified permission and 0 if not. Also, see sys.fn_my_permissions below.

-- JoeUser is in db_datareader database role
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY')

SQL Server IS_MEMBER Function

Returns a 1 if the current database user is a member of a specific database role, 0 if not, or NULL if role is invalid.

-- JoeUser is in db_datareader only
SELECT IS_MEMBER ('db_datareader')
SELECT IS_MEMBER ('db_datawriter')

SQL Server IS_ROLEMEMBER Function

Returns a 1 if the specified database user is a member of a specific database role, 0 if not, or NULL if either user or role is invalid.

-- JoeUser is in db_datareader only
SELECT IS_ROLEMEMBER ('db_datareader','JoeUser')
SELECT IS_ROLEMEMBER ('db_datawriter','JoeUser')

SQL Server IS_SRVROLEMEMBER Function

Returns a 1 if the specified login is a member of a specific server role, 0 if not, or NULL if either login or server role is invalid.

-- login Joe is in securityadmin only
SELECT IS_SRVROLEMEMBER ('securityadmin','Joe')
SELECT IS_SRVROLEMEMBER ('sysadmin','Joe')

SQL Server ORIGINAL_LOGIN Function

Returns the original login that first connected to the session. Even though we are impersonating login Joe2 with the EXECUTE AS, the original login is still Joe.

-- logged in as 'Joe'
EXECUTE AS LOGIN = 'Joe2'
SELECT ORIGINAL_LOGIN() AS OriginalLogin
REVERT

SQL Server PERMISSIONS Function

Returns a bitmap with permissions of the current database user. See fn_my_permissions and has_perms_by_name below. This feature is in maintenance mode and may be removed in future SQL Server versions.

-- current user which is JoeUser has INSERT permissions for Comments table
IF PERMISSIONS(OBJECT_ID('Comments','U'))&8=8    
PRINT 'The current user can insert data into Comments.'

SQL Server PWDCOMPARE Function

Compares plain text password to a hash for a login. Useful for checking for blank or common passwords like 'password', '12345', etc.

-- password for logins Joe is 'password'
SELECT name 
FROM sys.sql_logins   
WHERE PWDCOMPARE('password', password_hash) = 1

SQL Server PWDENCRYPT Function

Displays hash for plain text password passed to it. This function may not be supported in future versions of SQL Server.

SELECT PWDENCRYPT ('BadPassword')

SQL Server SCHEMA_ID Function

Returns ID of schema specified, current database user's schema if no schema specified, or NULL if nonexistent.

SELECT SCHEMA_ID('dbo')
SELECT SCHEMA_ID()

SQL Server SCHEMA_NAME Function

Returns name of a schema of specified, current database user's schema if not specified, or NULL if nonexistent.

SELECT SCHEMA_NAME(1)
SELECT SCHEMA_NAME()

SQL Server SESSION_USER Function

Returns current database user context.

-- connected as JoeUser
SELECT SESSION_USER

SQL Server SUSER_ID Function

Returns principal_id (not SID) of current or specified login.  This data comes from sys.server_principals.

SELECT SUSER_ID()
SELECT SUSER_ID('Joe')

SQL Server SUSER_NAME Function

Returns login name for corresponding principal_id if specified or the current login if not specified.

SELECT SUSER_SNAME()
SELECT SUSER_NAME(309)

SQL Server SUSER_SID Function

Returns SID of current login or specified login.

SELECT SUSER_SID()
SELECT SUSER_SID('Joe')

SQL Server SUSER_SNAME Function

Returns login name of current login or specified login using login SID (which was pulled from above).

SELECT SUSER_SNAME()
SELECT SUSER_SNAME(0x5D20DB6EFFE76641A6295FC4BF8500A1)

SQL Server SYSTEM_USER Function

Returns current login.

-- connected as Joe
SELECT SYSTEM_USER

SQL Server USER_ID Function

Returns ID of current database user if no name specified. This function is in maintenance mode and may be removed, so you can use function DATABASE_PRINCIPAL_ID instead which is listed above.

SELECT USER_ID()
SELECT USER_ID('JoeUser') -- this did not work for 2016, but did for 2017

SQL Server USER_NAME Function

Returns name of specified or current database user using the database user id.

SELECT USER_NAME()
SELECT USER_NAME(5)

Other SQL Server Security Functions

Here are some additional functions you may find useful.

SQL Server sys.fn_builtin_permissions

Returns a list of a principal's login permissions on a securable, this will return several rows. Also, see HAS_PERMS_BY_NAME above.

SELECT DISTINCT permission_name
FROM sys.fn_builtin_permissions(DEFAULT)
ORDER BY permission_name

SQL Server sys.fn_get_audit_file

Returns information from a SQL Server audit file, this will return several rows.

SELECT event_time, action_id 
FROM sys.fn_get_audit_file ('C:\Audit\MyAudit.audit', null, null)

SQL Server sys.fn_my_permissions

Returns a list of the permissions effectively granted to the principal on a securable, this will return several rows. Also, see function HAS_PERMS_BY_NAME above.

SELECT * 
FROM fn_my_permissions(NULL, 'SERVER')

Next Steps

Following are some links with more information on SQL Server security:

• Check out the SQL Server Security tips
• Microsoft Security Functions

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips