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

Question

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

score 22 · Accepted Answer · edited Nov 21 '14 at 19:39

22

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'

edited Nov 21 '14 at 19:39

Jon Schneider

25,758
23
142
170

answered Feb 19 '10 at 17:21

Aaron

7,431
12
35
37

But what @Peter said is essentially correct...you will need to grant a username executing privileges...this site will give you a quick way to do that. – Aaron Feb 19 '10 at 17:22

score 3 · Answer 2 · answered Jun 17 '14 at 05:25

3

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'

answered Jun 17 '14 at 05:25

Rocklan

7,888
3
34
49

2

You don't need to restart SSMS to see the new role. Just right click on the Security->Server Roles container folder and choose "Refresh" – theta-fish Jan 02 '15 at 14:41

score 2 · Answer 3 · answered Feb 19 '10 at 17:20

2

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.

answered Feb 19 '10 at 17:20

Peter Schofield

939
7
13

1

or you could put users into roles and grant the rights to each proc to the role rather than 1000 differnt users. – HLGEM Feb 19 '10 at 22:40

score 0 · Answer 4 · answered Feb 20 '10 at 01:13

To expand on the answer, the general gist is to create a database role and assign permissions to that role. In order to do that, you need some fancy dynamic SQL such as:

Set @Routines = Cursor Fast_Forward For
    Select ROUTINE_SCHEMA + '.' + ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE
    From INFORMATION_SCHEMA.ROUTINES
    Where ROUTINE_NAME NOT LIKE 'dt_%'
        Or ROUTINE_TYPE = 'FUNCTION'

Open @Routines
Fetch Next From @Routines Into @Procname, @RoutineType, @DataType

While @@Fetch_Status = 0
Begin
    Set @Msg = 'Procname: ' + @Procname + ', Type: ' + @RoutineType + ', DataType: ' + Coalesce(@DataType,'')
    Raiserror(@Msg, 10, 1) WITH NOWAIT

    If @RoutineType = 'FUNCTION' And @DataType = 'TABLE'
        Set @SQL = 'GRANT SELECT ON OBJECT::' + @Procname + ' TO StoredProcedureDataReader'
    Else
        Set @SQL = 'GRANT EXECUTE ON OBJECT::' + @Procname + ' TO StoredProcedureDataReader'

    exec(@SQL)

    Fetch Next From @Routines Into @Procname, @RoutineType, @DataType
End

Close @Routines
Deallocate @Routines

This code will grant EXECUTE to stored procedures and scalar functions and SELECT to user-defined functions that return a TABLE type.

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

4 Answers4