Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Built-in database role in SQL Server 2005 to permit execution of stored procedures?

In SQL Server 2005, there are built in roles:

db_datareader

db_datawriter

etc.

Is there any role that lets a user execute an stored proc?

I don't want to use db_owner, because that will permit deletion and updates, which I don't need. The only permissions I need are:

SELECT

EXECUTE

like image 314
frankadelic Avatar asked Feb 19 '10 17:02

frankadelic


People also ask

What DB role can execute stored procedures?

A user can then be added to the new role, much like the db_datareader and db_datawriter roles. A user added to this role will be able to execute all stored procedures in the database, including ones created in the future.

How do I grant permission to execute a stored procedure in SQL Server?

Use SQL Server Management StudioExpand Stored Procedures, right-click the procedure to grant permissions on, and then select Properties. From Stored Procedure Properties, select the Permissions page. To grant permissions to a user, database role, or application role, select Search.

What SQL Server role gives permissions at the database level?

A member of the dbmanager role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database.

Can you execute a stored procedure in the database?

SQL Stored Procedures for SQL Server So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.


3 Answers

Take a look at this article. It may provide you an interesting idea to do this quickly.

Code used in that article:

/* Create a new role for executing stored procedures */
CREATE ROLE db_executor

/* Grant stored procedure execute rights to the role */
GRANT EXECUTE TO db_executor

/* Add a user to the db_executor role */
EXEC sp_addrolemember 'db_executor', 'AccountName'
like image 172
Aaron Avatar answered Oct 28 '22 22:10

Aaron


CREATE ROLE db_executor

GRANT EXECUTE TO db_executor

Now, if you restart SQL Server Management Studio, when you click on the "User Mapping" page in the Security->Logins section, you'll see "db_executor" appear in the roles list. Just add the user or you can do it manually:

EXEC sp_addrolemember 'db_executor', 'AccountName'
like image 22
Rocklan Avatar answered Oct 28 '22 21:10

Rocklan


No, I don't believe that there is a database or server role - you have to grant the execute permission granularly to the user for the relevant stored procedures.

like image 25
Peter Schofield Avatar answered Oct 28 '22 21:10

Peter Schofield