If you need to allow a role or user to access all tables future and present as well as stored procedures. All you need to do is add them to the db_datareader and db_datawriter roles. (This allows them access to all tables and views) and then GRANT EXECUTE to give access to stored procedures).

Below creates a role with the correct permissions we want. You would then just add your Windows groups or SQL accounts to it.

CREATE ROLE [App_execute] AUTHORIZATION [dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [App_execute]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [App_execute]
GO
GRANT EXECUTE TO [App_execute]
GO

Add a member to the role via:

ALTER ROLE [App_execute] ADD MEMBER [DOMAIN\AppUserGroup]
GO

Advertisements