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
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)
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)
In the list below are displayed Database role memberships where public is checked.
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 ...
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?