Background: I had a user that needed to be able to read, but not execute, stored procedures in the DBO schema. I used these methods below to achieve this task.
There are two methods to granting schema permissions in SQL server.
- One is through T-SQL commands:
GRANT SELECT ON SCHEMA::[dbo] TO [DOMAIN\user]
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [DOMAIN\user]
- The other is through the GUI:
- Right click on the database user you wish to grant permissions to and select Properties:
- Click on Search for securables, then select Specific objects… and click OK.
- Click on Object Types…,scroll down until you find Schemas, select it and then click OK.
- Click on Browse, scroll down until you find the Schema you are looking for (in this case DBO), select it and then click OK.
- Chose the appropriate permission needed for the user (in this case Grant Select and Grant View definition).