Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Security Functions


By:   |   Last Updated: 2019-05-23   |   Comments   |   Related Tips: More > 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 T-SQL Code Result
SELECT CURRENT_USER JoeUser

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 T-SQL Code Result
SELECT DATABASE_PRINCIPAL_ID('JoeUser') 5
SELECT DATABASE_PRINCIPAL_ID() 5

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 T-SQL Code Result
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY') 1

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 T-SQL Code Result
SELECT IS_MEMBER ('db_datareader') 1
SELECT IS_MEMBER ('db_datawriter') 0

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 T-SQL Code Result
SELECT IS_ROLEMEMBER ('db_datareader','Joe') 1
SELECT IS_ROLEMEMBER ('db_datawriter','Joe') 0

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 T-SQL Code Result
SELECT IS_SRVROLEMEMBER ('securityadmin','Joe') 1
SELECT IS_SRVROLEMEMBER ('sysadmin','Joe') 0

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 T-SQL Code Result
EXECUTE AS LOGIN = 'Joe2'
SELECT ORIGINAL_LOGIN() AS OriginalLogin
REVERT
Joe

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 T-SQL Code Result
IF PERMISSIONS(OBJECT_ID('Comments','U'))&8=8   
PRINT 'The current user can insert data into Comments.'
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 T-SQL Code Result
SELECT name
FROM sys.sql_logins  
WHERE PWDCOMPARE('password', password_hash) = 1
Joe

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 T-SQL Code Result
SELECT PWDENCRYPT ('BadPassword') 00x020022B032ECF81402550988B1D62FB6BCF944AF3E03A85728BF99C1445A97C373920 3163821D7F1D3966FB1B726F8CFCFF41B51E2B61FE5E0F809ADD9BAD6929FD4184ADC00

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 T-SQL Code Result
SELECT SCHEMA_ID('dbo') 1
SELECT SCHEMA_ID() 1

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 T-SQL Code Result
SELECT SCHEMA_NAME(1) dbo
SELECT SCHEMA_NAME() dbo

SQL Server SESSION_USER Function

Returns current database user context.

-- connected as JoeUser
SELECT SESSION_USER
SQL Server T-SQL Code Result
SELECT SESSION_USER JoeUser

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 T-SQL Code Result
SELECT SUSER_ID() 309
SELECT SUSER_ID('Joe') 309

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 T-SQL Code Result
SELECT SUSER_SNAME()  Joe
SELECT SUSER_NAME(309) Joe

SQL Server SUSER_SID Function

Returns SID of current login or specified login.

SELECT SUSER_SID()
SELECT SUSER_SID('Joe')
SQL Server T-SQL Code Result
SELECT SUSER_SID() 0x5D20DB6EFFE76641A6295FC4BF8500A1
SELECT SUSER_SID('Joe') 0x5D20DB6EFFE76641A6295FC4BF8500A1

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 T-SQL Code Result
SELECT SUSER_SNAME () Joe
SELECT SUSER_SNAME(0x5D20DB6EFFE76641A6295FC4BF8500A1) Joe

SQL Server SYSTEM_USER Function

Returns current login.

-- connected as Joe
SELECT SYSTEM_USER
SQL Server T-SQL Code Result
SELECT SYSTEM_USER Joe

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 T-SQL Code Result
SELECT USER_ID() 2
SELECT USER_ID('JoeUser')  2

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)
SQL Server T-SQL Code Result
SELECT USER_NAME() JoeUser
SELECT USER_NAME(5) JoeUser

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 T-SQL Code Result
SELECT DISTINCT permission_name
FROM sys.fn_builtin_permissions(DEFAULT)
ORDER BY permission_name
ADMINISTER BULK OPERATIONS
ALTER ANY APPLICATION ROLE
...
...

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 T-SQL Code Result
SELECT event_time, action_id
FROM sys.fn_get_audit_file ('C:\Audit\MyAudit.audit', null, null)
2019-05-02 14:46:45.607 SL
...
...

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')
SQL Server T-SQL Code Result
SELECT *
FROM fn_my_permissions(NULL, 'SERVER') 
CONNECT SQL
...
...
VIEW ANY DATABASE
Next Steps

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



Last Updated: 2019-05-23


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools