Is there a way to use sysconv() built-in function?

If you get the definition of some views using sys.sp_helptext:

```
exec sys.sp_helptext 'sys.columns'

CREATE VIEW sys.columns
AS
SELECT c.id AS object_id,
c.NAME,
c.colid AS column_id,
c.xtype AS system_type_id,
c.utype AS user_type_id,
c.length AS max_length,
c.prec AS PRECISION,
c.scale,
CONVERT(SYSNAME, CollationPropertyFromId(c.collationid, 'name')) AS collation_name,
sysconv(bit, 1 - ( c.status & 1 )) AS is_nullable,-- CPM_NOTNULL
sysconv(bit, c.status & 2) AS is_ansi_padded,-- CPM_NOTRIM
sysconv(bit, c.status & 8) AS is_rowguidcol,-- CPM_ROWGUIDCOL
sysconv(bit, c.status & 4) AS is_identity,-- CPM_IDENTCOL
sysconv(bit, c.status & 16) AS is_computed,-- CPM_COMPUTED
sysconv(bit, c.status & 32) AS is_filestream,-- CPM_FILESTREAM
sysconv(bit, c.status & 0x020000) AS is_replicated,-- CPM_REPLICAT
sysconv(bit, c.status & 0x040000) AS is_non_sql_subscribed,-- CPM_NONSQSSUB
sysconv(bit, c.status & 0x080000) AS is_merge_published,-- CPM_MERGEREPL
sysconv(bit, c.status & 0x100000) AS is_dts_replicated,-- CPM_REPLDTS
sysconv(bit, c.status & 2048) AS is_xml_document,-- CP

The view sys.columns is a system view. This view is provided to you by the sys user in the master database, which is a database user without login.

The sys user has its own schema sys which is then used to link all the sys.* objects.

Permissions to select from the sys. objects are granted to you via membership in the public SQL Server role. The SQL Server role public has SELECT permissions on all sys. schema objects.

This is how you are granted the permission to select * from sys.columns.

If you are not member of the public server role, then you don't have access to any of the sys.* objects.

sys user (script)

USE [master]
GO

/****** Object:  User [sys]    Script Date: 16.12.2016 15:57:08 ******/
CREATE USER [sys]
GO

sys schema (script)

USE [master]
GO

/****** Object:  Schema [sys]    Script Date: 16.12.2016 15:57:35 ******/
CREATE SCHEMA [sys]
GO

public server role (script)

USE [master]
GO

/****** Object:  ServerRole [public]    Script Date: 16.12.2016 16:05:05 ******/
CREATE SERVER ROLE [public]
GO

Public database role

There is also a public database role, which is somehow linked to the server role. If you query the sys.database_principals (view) in the master database, then you will see that there is a public principal with the same id as the server role public which is 0. I am assuming that this is the missing link, between the database_role public and the server_role public.

Edit: Added some information regarding the database role "public"

The public role is contained in every database, which includes system databases. It cannot be dropped and you cannot add or remove users from it. Permissions granted to the public role are inherited by all other users and roles because they belong to the public role by default. Grant public only the permissions you want all users to have.

Quote from Server and Database Roles in SQL Server, Section: "The public Role"

If you retrieve the permissions for the database role public in the master database you will get the following listing:

Query:

SELECT 
   OBJECT_NAME(dp.major_id) AS OBJECT , 
   USER_NAME(dp.grantee_principal_id) AS grantee, 
   USER_NAME(dp.grantor_principal_id) AS grantor, 
  dp.permission_name
FROM sys.database_permissions AS dp 
WHERE dp.grantee_principal_id = 0

Results:

sp_MSalreadyhavegeneration  public dbo EXECUTE
sp_MSwritemergeperfcounter  public dbo EXECUTE
TABLE_PRIVILEGES            public dbo SELECT
sp_replsetsyncstatus        public dbo EXECUTE
sp_replshowcmds             public dbo EXECUTE
sp_publishdb                public dbo EXECUTE
dm_pdw_nodes_os_tasks       public dbo SELECT
...
[truncated]

To come back to your example with the sys.colums view: You have been granted the permissions to SELECT from the view, but you do not have permissions to actually execute the sysconv function directly, which is the definiton of a column in the result set. It has been hidden from your prying eyes.

StackExchange Database Administrators Q#158360, answer score: 2