SQL Server Permissions – Granting Permissions to an Entire Schema vs. Object

Quick answer: For developers, you can GRANT CONTROL on that schema.

Background:

• To see what can be GRANTed: GRANT Schema Permissions
• To see what each permission means: Permissions Naming Conventions

The intersection of these two give the schema permission meanings:

• CONTROL implies the rest and is the highest permissions of any securable
• SELECT, DELETE, INSERT, UPDATE is DML on objects in that schema
• EXECUTE to run scalar UDFs and Stored Procedures in that schema
• ALTER, REFERENCES is DDL (CREATE, ALTER, DROP) on objects in that schema
• VIEW DEFINITION lets folk see the code and definitions

Except for CONTROL, these are mostly orthogonal to each other: folk can EXECUTE code or SELECT, from a view but not see the definition (VIEW DEFINITION) of these.

You also have the database and server permissions which are implied higher up then CONTROL on schemas: see Permissions Hierarchy

The problem is with the difference between logins and users. When you are granting the permissions you are working with a user. Only a login can have server level permissions such as sysadmin. I discussed this here if you are interested.

In the mean time I can tell you how to do what you need, however it's not the best idea in the world.

First create a seperate database, make SA the owner of that database, set the trustworthy property on for that database. Then create your usp_fts_parser stored procedure with EXECUTE AS OWNER. In this case OWNER is dbo. Also in this case dbo is SA. Because you made the database trustworthy it will allow users in it to access server level permissions. All of this means that your stored procedure will be able to act as a sysadmin. Next grant the login that you are using for the web application connect permissions to your new database. Then grant that user execute permissions on the stored procedure.

This is very important. ONLY grant the user execute permissions on the stored procedure. You have created a big security hole by turning on trustworthy with a database owned by a sysadmin so you need to be very very very careful who you give permissions to that database and what you allow them to do.