SQL Server Security Functions
source link: https://www.tuicool.com/articles/m2QN73v
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
By:Joe Gavin | Last Updated: 2019-05-23 | | 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_USERSQL 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 REVERTSQL 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) = 1SQL 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_USERSQL 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_USERSQL 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 2017SQL 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_nameSQL 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:
- Check out the SQL Server Security tips
- Microsoft Security Functions
Last Updated: 2019-05-23
About the author
Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.
View all my tipsRelated Resources
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK