5

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,-- CPM_XML_DOC   
         c.xmlns                                                          AS xml_collection_id,
         c.dflt                                                           AS default_object_id,
         c.chk                                                            AS rule_object_id,
         sysconv(bit, c.status & 0x1000000)                               AS is_sparse,-- CPM_SPARSE  
         sysconv(bit, c.status & 0x2000000)                               AS is_column_set -- CPM_SPARSECOLUMNSET  
  FROM   sys.syscolpars c
  WHERE  number = 0
         AND has_access('CO', c.id) = 1 

There is a function called sysconv.

The function 'sysconv' is used by these views, and you can select values from this view without getting any error message.

select * from sys.columns;

But if I try something similar to:

select sysconv(bit, 375 & 8);

I receive the error:

sysconv is not a recognized built-in function name

As Martin Smith pointed out, this function can be replaced by convert (bit, 375 & 8), but I wonder why I can select values from this view and I can't use it in my queries, even when SSMS recognizes it as as command, by changing the foreground color?

0
2

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.

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.