Minimum SQL login permissions for an AD domain group

Question

I've created a web application that uses a SQL Server 2008 R2 database in the background. Application uses integrated security SSPI connection to database so users need granted access to database server.

All users that should be using the app are members of a particular AD domain group.

This is what I've done

1. I've added a new Security Login on SQL Server level and provided AD domain group name which gets displayed as a group (based on icon)

   

2. Then I've opened this server login properties dialog and opened User Mapping settings where I checked my particular database that I want users of this AD domain group to access (see below in the image)

3. In the list below are displayed Database role memberships where public is checked.

4. I created all objects in my database as a separate SQL Server user with securityadmin permission and provided schema dbo with every object as in:

   create table dbo.SomeTable ...
   create procedure dbo.SomeProcedure ...
   

5. All database manipulation is done via stored procedures only. No table is directly accessed by the application.

Based on these facts (dbo schema with my objects, AD domain group security login, stored procedure execution only) I wonder which role membership should be set on AD domain group server login to use my database.

Users should have permission to execute stored procedures. These on the other hand do everything else. CRUD operations on tables, execute user defined functions and call each other as well.

Question

Setting db_datareader and db_datawriter (as seen below) isn't sufficient as implied by the error screen my users get (stored procedures supposedly don't exists)?

How do I set security permissions on my AD domain group so it will be able to see dbo objects and execute those stored procedures?

Answer 1

Solution

Applying db_datareader and db_datawriter isn't enough, since they're none of them is granted stored procedure execution right. You should either grant execute permission for each stored procedure in your database or... Rather tedious.

If your database is SQL Server 2005 or later (mine is SQL Server 2008 R2 so I'm covered) you can do it with much less effort:

1. create a new database role called db_executor

   create role db_executor
   

2. grant stored procedure execution rights for all stored procedures in the database

   grant execute to db_executor
   

If you would like to grant execution rights only to schema specific stored procedures then this last call should be executed as (adjust schema name - dbo - as required):

grant execute on schema::dbo to db_executor

And that's it. Then all you have to do is set your user (or in my case it was a group) to be member of this role and things work as expected.