How only grant to execute SP

Product:

Microsoft SQL server 2016

Issue:

How allow a domain group only to execute all SP in a database?

Solution:

Create a role with only the correct rights:

CREATE ROLE db_executor;

GRANT EXECUTE TO db_executor;

Add a existing database user to that role:

ALTER ROLE db_executor ADD MEMBER [company\username]

To add a domain user to the sysadmin role:

exec sp_addsrvrolemember 'whatDomain\Domain Admins', 'sysadmin';

To add a domain user and add it to a predefined role in the database:

CREATE LOGIN [company\User] 
CREATE USER [company\User]
ALTER ROLE [db_datareader] ADD MEMBER [company\User]

More Information:

CogKnowHow