2

How can I find the definitions of below system functions in SQL Server:

  1. has_access
  2. permission_name

I tried with DAC connection but getting below error:

Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54 [Batch Start Line 118]
The object 'has_access' does not exist in database 'master' or is invalid for this operation.

Please let me know if any further details are required. Thanks!

  • 1
    Although some undocumented internal system objects are stored in the mssqlsystemresource database (which can be attached as a user database), I suspect these are implemented in the database engine code. Why are you looking for the source code? – Dan Guzman Dec 23 '18 at 16:27
  • It's probably a mistake to assume there's a T-SQL definition for these functions. – Erik Darling Dec 23 '18 at 16:28
  • @DanGuzman I was trying to create a script for logins and server level permissions from a backup of master database restored as a user database. – SQLPRODDBA Dec 23 '18 at 16:34
  • @sp_BlitzErik Thanks! I didn't know that these functions do not have definitions. – SQLPRODDBA Dec 23 '18 at 16:36
  • Have you considered restoring master to a test instance? – Dan Guzman Dec 23 '18 at 16:38
1

These two functions appear to be internal / built-in functions given that:

  1. they are referenced without a schema-name
  2. SSMS color-codes them the same as all other built-in functions

sys.server_permissions, on Line #7, has:

        p.type, permission_name(p.class, p.type) AS permission_name,

sys.server_principals, on Line # 18, has:

    WHERE has_access('LG', p.id) = 1

When the instance is in single-user mode, I can execute the following:

USE [mssqlsystemresource];
SELECT PERMISSION_NAME(100, 'COSQ');
-- CONNECT SQL

The same statement does not work outside of mssqlsystemresource, nor does it work in that DB when prefixed with sys. or dbo..

Ergo, no, there is no definition that you will be able to get.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.