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
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.