Roles needed for executing Stored Procedure in SQL Server Authentication

Question

I need to connect to SQL Server 2008 R2 database from a C# application. I need to use SQL Server authentication for the connection.

What are the roles and schemas needed for the user so that the user will be able to create/execute stored procedures ?

Note: The stored procedures will be in dbo schema.

Solution 1

Permissions for creating and executing procedures are documented under CREATE PROCEDURE and EXECUTE, respectively.

One important consideration is that users do not need to have permissions on the objects referenced in the procedure. You can read this in the documentation, but it's faster to test it yourself:

create table dbo.TestTable (col1 int)
go

create procedure dbo.TestProc
as select col1 from dbo.TestTable
go

grant execute on dbo.TestProc to UserWithNoPermissions
go

execute as user = 'UserWithNoPermissions';

-- this gives error 229 (SELECT permission denied)
select * from dbo.TestTable;

-- this works
execute dbo.TestProc;

revert;

Note that there are some exceptions: dynamic SQL executes in its own scope, so if your procedure uses it then the executing user will indeed need permission on the underlying objects.

Solution 2

IF you want to go more granular on giving your rights to roles, under 'Security' folder ( under the database ), you can configure your execute rights to a given stroed proc.

i.e., in the managements studio's Object explorer, [Database] -> Security -> [YourRole] -> Rightclick for properties -> Securables section

Here you can add specific object types and their permissions et al.

Hope this helps.

Solution 3

It depends on the action performed within your stored procedure.

If you simply excute SELECT statements, the db_datareader role should fit for executing your stored procedures. The db_datawriter is the role, that is eligible to create them.

Comments

[LCJ]

Is it sufficient from a C# application also?

[Jan P.]

It is sufficient whereever the user authenticates. The rights do not depend from where the user is accessing the DB. You allways have the same rights, wether you use the "SQL Server Management Studio", an C#-Application or a JAVA application. The SQL-Server just knows that the user e.g. Lijo is trying to execute something.

[Adir D]

Not quite correct. db_datawriter is about modifying data in tables, not about creating objects like stored procedures. Also db_datareader will only help you if the stored procedure is running only SELECT statements...

[Jan P.]

For shure. If the stored procedures tries to drop a database, it could not be executed by a db_datareader. Edited my post.

[Pondlife]

db_datareader is (possibly) irrelevant: a user who can execute a stored procedure does not need any permissions on the underlying objects. So unless there is dynamic SQL, EXECUTE AS or something else 'unusual' in the procedure, the only permission required is permission to EXECUTE it.

[LCJ]

Thanks. What is the permission to be granted to creating a procedure?

[Pondlife]

Did you read the first sentence of my answer and follow the links?

[LCJ]

Do you mean GRANT CREATE PROCEDURE TO MYUSER?