CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Andi
Andi

Reputation: 507

How to grant execute permissions to the stored procedures in a specific schema?

Is there a way in SQL Server 2012 to grant execute all stored procedures in one schema? For additional info, these stored procedures are doing just a select.

Upvotes: 15

Views: 45451

Answers (3)

Kenneth Hampton
Kenneth Hampton

Reputation: 705

I’d recommend you do what Szymon suggested since it’s more elegant solution but it you want to avoid creating roles you can execute query like this, copy results into separate query editor and execute all at once.

This will generate GRANT EXECUTE statements for all stored procedures you have in specific schema

DECLARE @SchemaName varchar(20)
DECLARE @UserName varchar(20)

SET @SchemaName = 'dbo'
SET @UserName = 'user_name'

select 'GRANT EXECUTE ON OBJECT::' + @SchemaName + '.' + P.name  + ' to ' + @UserName
from sys.procedures P
inner join sys.schemas S on P.schema_id = S.schema_id
where S.name = @SchemaName

Upvotes: 1

Abhishek Chakraborty
Abhishek Chakraborty

Reputation: 31

For granting execute permission for all of the stored procedures in one schema , the query by @szymon is enough.

The below query will grant execute permission for the procedure to the user selected. Provided the user already exists.

GRANT EXECUTE ON OBJECT::[schema].[procedurename] TO [user] AS [schema]; GO

Upvotes: 3

Szymon
Szymon

Reputation: 43023

Try something like that. It creates a new role and grants execute permission to a schema.

CREATE ROLE db_executor
GRANT EXECUTE ON SCHEMA::schema_name TO db_executor
exec sp_addrolemember 'db_executor', 'Username'

Replace schema_name with your schema and 'Username' with your user.

Upvotes: 29

Related Questions