Many times it is required to grant any permission for all objects in a database. Or we can say that it is required to grant a database level permission. Consider following tasks for examples
- To assign EXECUTE permission on all stored procedures and functions to a user.
- To assign SELECT permission for all tables and views in a database
- To VIEW DEFINITIONS of all objects in a database
- To UPDATE all tables and views in a database
- Top INSERT data in any table or view
- To DELETE data from any table or view
All these permissions may easily be granted through SQL Server Management Studio (SSMS). It is notable that object level permissions will over ride these database level permissions.
Suppose we have to grant select permissions on all tables and views to User1
- Open SSMS
- Right click on database folder on which permissions are required to grant
- Click properties
- Click permissions in left panel of frame and also select the user to which permissions are to be granted
Here in lower panel you will find about 60 different permissions. Most of these are with self explanatory names. As we are required to grant select permission here on all tables and views to User1. So Click grant for Select and click OK. Task is over and now User1 can select data from any table or view.
If we deny SELECT for any individual table to User1 then deny on object level will have precedence on database level permissions and User1 will not be able to select data from that restricted object.
So you may go through all permissions provided here and use them according to scenario.