Search code examples
sql-servert-sqlviewsql-grant

What is object in the GRANT <permission> in SQL Server?


What could be [ OBJECT :: ][ schema_name ]. object_name in the

GRANT <permission> [ ,...n ] ON 
        [ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ]
        TO <database_principal> [ ,...n ] 
        [ WITH GRANT OPTION ]
        [ AS <database_principal> ]

Could it be a table or view?


Solution

  • OBJECT here refers to any of the things that exist in sys.objects. From the documentation for sys.objects, that could be any of

    • AGGREGATE_FUNCTION
    • CHECK_CONSTRAINT
    • CLR_SCALAR_FUNCTION
    • CLR_STORED_PROCEDURE
    • CLR_TABLE_VALUED_FUNCTION
    • CLR_TRIGGER
    • DEFAULT_CONSTRAINT
    • EXTENDED_STORED_PROCEDURE
    • FOREIGN_KEY_CONSTRAINT
    • INTERNAL_TABLE
    • PLAN_GUIDE
    • PRIMARY_KEY_CONSTRAINT
    • REPLICATION_FILTER_PROCEDURE
    • RULE
    • SEQUENCE_OBJECT
    • SERVICE_QUEUE
    • SQL_INLINE_TABLE_VALUED_FUNCTION
    • SQL_SCALAR_FUNCTION
    • SQL_STORED_PROCEDURE
    • SQL_TABLE_VALUED_FUNCTION
    • SQL_TRIGGER
    • SYNONYM
    • SYSTEM_TABLE
    • TABLE_TYPE
    • UNIQUE_CONSTRAINT
    • USER_TABLE
    • VIEW

    Mind you, not every permission makes sense for every type of object. For instance, you can't grant execute permission to a table. Indeed, not every object type can be the target of a grant (primary keys, for instance). The documentation for grant has a nice list near the bottom of each type of securable and link to a documentation page for what permissions can be granted to it.