Grant access to all objects (with a few exceptions) to a role

Question

In SQL Server 2014, I want to create a role that will have access (select, delete, update, insert, execute) to all the tables, views, stored procedures, except a certain list of tables that contain sensitive data that only the "sa" user should have access to.

I tried the following:

Using SQL Server Management studio, in the database (TESTCOMPANY) -> Security -> Roles -> Database roles, create the database role "CompanyAdmin".

Run the following query:

GRANT  SELECT,INSERT, UPDATE, DELETE, EXEC ON DATABASE::TESTCOMPANY TO CompanyAdmin

Then:

DENY SELECT, INSERT, UPDATE, DELETE ON TestTable1 TO CompanyAdmin; 
--Repeat for all tables to secure

In Management Studio, server -> Security -> Logins, create a new login "testuser". Server Roles = "public" only. Then go to TESTCOMPANY -> Security -> Users -> New user:

• User Type: SQL user with login
• User name: testuser
• Login name: testuser
• Default schema: dbo
• Membership: CompanyAdmin

Once logged in as testuser, all seems fine, I see all tables except the ones in the DENY permissions. BUT:

• If, in the future, the "sa" user creates a view on the secured tables, the testuser user has access to it and can view the data it returns

• In SQL Server Management Studio, I can go to Server -> Security -> Logins and I see the "sa" login properties

Am I taking the right approach or is there a better way to accomplish what I want to do?

It's the database for the accounting application of our company and the software is designed to have everything in the same database.

Answer 1

The sad truth is that you cannot do anything computerwise about the 'sa' activities. This is because the 'sa' (or a member of the sysadmin server role) are defined to be able to do pretty much anything.

So, of course, you should only have sysadmin's that you can trust to do their best to adhere to the standards for the database. But even the best sysadmin is imperfect. So you might set up some auditing to report on changes made in the database.

(Or, more low tech, periodically check your views for a restricted table name in a view.)

So it depends on what you are concerned about and how much effort you need to put into protecting the secure resources.

In terms of views that expose secure information, you can DENY permissions on a view. Even if a sysadmin created the view, the DENY of the view for those without approval will prevent their access.

Perhaps any high security views could be named something like SecureViewXYZZY so as to emphasize the reason for those views and the restriction of who can use them.