What is object in the GRANT in SQL Server?

Question

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?

I guess a table or view or database – rekire Mar 14 '14 at 12:37 — rekire, Mar 14 '14 at 12:37

score 1 · Answer 1 · answered Mar 14 '14 at 12:50

I'm not entirely sure if this is what you're asking, but the OBJECT :: keyword here isn't meant to be replaced by some sort of identifier such as TABLE ::, it's meant to be specified literally as OBJECT ::. It's used to indicate that you want to grant permissions to an object as opposed to, say, a schema. According to this page, an object is any schema-level securable, such as a table, view, stored procedure, sequence, etc.

Also according to that page, the OBJECT :: keyword is optional if schema_name is specified. That leads me to believe that the need for specifying OBJECT :: is simply to make sure the database it's what type of entity the permissions are being granted to, since permissions can be granted to objects, schemas, server principles, and more.

So in the sql statement grant execute ON foo.Addresses TO newAddresses - foo.Addresses is a table? — YAKOVM, Mar 14 '14 at 13:16
There's no way to know that without more information. `GRANT` statements aren't specific to tables, only to schema-level objects. It could be a table, a view, a sequence, or many other types of objects. If what you're after is a way to infer the database schema from these `GRANT` statements, I'm afraid that's not enough to go on. — Jeff Rosenberg, Mar 14 '14 at 13:23

score 1 · Accepted Answer · answered Mar 14 '14 at 14:41

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.

What is object in the GRANT in SQL Server?

2 Answers2