1, grant the database Role or User the right to access the object with the following syntax
GRANT <Permission> [,... N] on [OBJECT::][schema_name].object_name [(column [,... n] ) ] to [Database_user | Database_role] [,... N] [With GRANT OPTION]
An object was a Schema-level securable contained by the schema, which is the IT parent in the permissions hierarchy. The most specific and limited permissions so can be granted on an object is listed in the following table, together wit H The more general permissions this include them by implication.
The Object Permission is the Permission that can be granted using the statement, and
Object Permission |
implied by object permission |
implied by schema permission |
Alter |
CONTROL |
Alter |
CONTROL |
CONTROL |
CONTROL |
DELETE |
CONTROL |
DELETE |
EXECUTE |
CONTROL |
EXECUTE |
INSERT |
CONTROL |
INSERT |
RECEIVE |
CONTROL |
CONTROL |
REFERENCES |
CONTROL |
REFERENCES |
SELECT |
RECEIVE |
SELECT |
Take OWNERSHIP |
CONTROL |
CONTROL |
UPDATE |
CONTROL |
UPDATE |
VIEW Change TRACKING |
CONTROL |
VIEW Change TRACKING |
VIEW DEFINITION |
CONTROL |
VIEW DEFINITION |
Examples
A. Grant SELECT
permission to user on RosaQdM
tablePerson.Address.
GRANT SELECT on to
B. Grants EXECUTE permission on stored procedure humanresources.uspupdateemployeehireinfo to an database role called Recru Iting11.
GRANT EXECUTE on Object::humanresources.uspupdateemployeehireinfo to
C.grant REFERENCES
permission on column in BusinessEntityID
view to user with HumanResources.vEmployee
Wanida
GRANT OPTION
.
GRANT REFERENCES on object::humanresources.vemployee to withGRANT OPTION;
D.grant EXECUTE
permission on procedureuspGetBillOfMaterials to a database role
GRANT EXECUTE on to Newrole;
Reference doc:
GRANT Object Permissions (Transact-SQL)
Security10:grant object Permission to DB Role or User