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:

https://www.sqlmatters.com/Articles/Adding%20a%20db_executor%20role.aspx

https://kimconnect.com/how-to-add-domain-admins-to-sql-server/

https://help.genesys.com/pureconnect/mergedprojects/wh_tr/mergedprojects/wh_tr_active_directory_sql/desktop/run_sql_server_script_to_add_ad_accounts.htm