CODEWITHSUNDEEP
c#.netsql-server
LCJ
LCJ

Reputation: 22652

Roles needed for executing Stored Procedure in SQL Server Authentication

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.

Upvotes: 3

Views: 22566

Answers (3)

Pondlife
Pondlife

Reputation: 16240

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.

Upvotes: 4

Jan P.
Jan P.

Reputation: 3297

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.

Upvotes: 1

Rahul Rumalla
Rahul Rumalla

Reputation: 333

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.

Upvotes: 2

Related Questions