Block Excel and Access connections to SQL

SQL Server Administration
2

Best way to control access is using the double-abstraction technique.

  1. Create AD Groups by role/function (e.g. ReadOnly, ReadWrite, whatever)
    2, Create database roles that match to match groups in step 1.
  2. Add specific, required permissions to DB roles from step 2.
    a. Use the principle of least privilege here
  3. Add the AD Groups to the database roles from step 2.

With that set up, you control the access by changing users in the AD groups.

BTW there is at least one "missing" role in SQL Server: db_executor. I usually create one

CREATE ROLE [db_executor] AUTHORIZATION [dbo];
GO
GRANT EXECUTE TO db_executor;
GO

Handy when you want to give read and execute permissions, but not owner e.g.