Granting permission on tables - individual tables or schema?

When there is a requirement for granting permission on tables for database users, common or known way of doing it is, granting explicit permission on individual objects for users. But, if database objects are schema-based objects and properly organized, then most appropriate way of managing permission is, granting permission on schema rather than granting permission on tables (or any other objects). The main reason for this is, any granted permission on a schema will by implicitly applied to all objects defined under the schema.

Example, if there are two tables called Product and ProductCategory and they have been created under Production schema, granting SELECT permission on Production schema is totally enough for granting SELECT permission to Product and ProductCategory tables.

Here is a test code;

view plainprint?

1. /*  
2. Creating a login and user called Jack  
3. */  
4.   
5. USE master;  
6. GO  
7.   
8. CREATE LOGIN Jack WITH PASSWORD = 'Pa$$w0rd'  
9.  , CHECK_POLICY = OFF;  
10. GO  
11.   
12. USE AdventureWorks2014;  
13. GO  
14.   
15. CREATE USER Jack FOR LOGIN Jack;  
16. GO  
17.   
18. -- Check and see whether Jack has permission on HumanResources schema  
19. -- Login as Jack and see permissions  
20. EXECUTE AS USER = 'Jack';  
21. GO  
22.   
23. SELECT * FROM HumanResources.Department;  
24. GO  
25.   
26. REVERT;  
27. GO  
28.   
29. -- Granting permission to the schema  
30. GRANT SELECT ON SCHEMA::HumanResources TO Jack;  
31. GO  
32.   
33. -- Deny permisson on one table for testing  
34. DENY SELECT ON HumanResources.Employee TO Jack;  
35. GO  
36.   
37. -- Login as Jack and see permissions  
38. EXECUTE AS USER = 'Jack';  
39. GO  
40.   
41. -- Jack can executes this  
42. SELECT * FROM HumanResources.Department;  
43. GO  
44.   
45. -- And this too  
46. SELECT * FROM HumanResources.JobCandidate;  
47. GO  
48.   
49. -- But not this as we have explicity denied permission  
50. SELECT * FROM HumanResources.Employee;  
51. GO  
52.   
53. REVERT;  
54. GO  
55.   
56. -- Cleaning up  
57. DROP USER Jack;  
58. GO  
59. DROP LOGIN Jack;  
60. GO  

/*
Creating a login and user called Jack
*/

USE master;
GO

CREATE LOGIN Jack WITH PASSWORD = 'Pa$$w0rd'
 , CHECK_POLICY = OFF;
GO

USE AdventureWorks2014;
GO

CREATE USER Jack FOR LOGIN Jack;
GO

-- Check and see whether Jack has permission on HumanResources schema
-- Login as Jack and see permissions
EXECUTE AS USER = 'Jack';
GO

SELECT * FROM HumanResources.Department;
GO

REVERT;
GO

-- Granting permission to the schema
GRANT SELECT ON SCHEMA::HumanResources TO Jack;
GO

-- Deny permisson on one table for testing
DENY SELECT ON HumanResources.Employee TO Jack;
GO

-- Login as Jack and see permissions
EXECUTE AS USER = 'Jack';
GO

-- Jack can executes this
SELECT * FROM HumanResources.Department;
GO

-- And this too
SELECT * FROM HumanResources.JobCandidate;
GO

-- But not this as we have explicity denied permission
SELECT * FROM HumanResources.Employee;
GO

REVERT;
GO

-- Cleaning up
DROP USER Jack;
GO
DROP LOGIN Jack;
GO