Sql-server – Prevent Privileged User from Denying Access to SA

permissionsrolesql serversql-server-2016

I have to set up a system where UserA has almost full control over a database (not the server), except for one table that's used by sa, and so sa will always need access to this table.

So far I have:

-- executing as sa
CREATE SCHEMA [sa_schema];
CREATE TABLE [sa_schema].[table1] ( ... );
GRANT SELECT , REFERENCES ON OBJECT::[sa_schema].[table1] TO PUBLIC

CREATE USER [UserA] WITHOUT LOGIN;
GRANT CONTROL TO [UserA] WITH GRANT OPTION;
DENY ALTER , DELETE , INSERT , TAKE OWNERSHIP , VIEW CHANGE TRACKING , UPDATE
  ON SCHEMA::[sa_schema] TO [UserA];
DENY ALTER ON USER::[sa] TO [UserA];

I thought this would be sufficient, but it turns out that it's not. It's perfectly legal for UserA to run:

-- executing as UserA
CREATE ROLE [Denied]
DENY SELECT ON OBJECT::[sa_schema].[table1] TO [Denied]
ALTER ROLE [Denied] ADD MEMBER [sa]

And now sa can no longer read from this table.

-- executing as sa
SELECT * FROM [sa_schema].[table1]

Msg 229, Level 14, State 5, Line 2

The SELECT permission was denied on the object 'table1', database 'mydb', schema 'sa_schema'.

Of course, sa could query the database to find out roles it has been added to and remove itself from them, but that seems like a kludge and a lot of work just to perform a select. I think I could avoid this problem by making sure UserA doesn't have the ability to add sa to any database roles or by ensuring UserA cannot modify the permissions on this object, so no matter what roles sa would be added to there's never going to be a DENY on this object.

Does anybody have any ideas on how to make sure sa will always have access to this table regardless of what other changes UserA makes?

Best Answer

SA is a login just like any other. You may want to do some research on best practices regarding the SA account. It does not need to have superpowers.

Members of the sysadmin role, however, cannot be locked out of anything and members of this role bypass security checks. If SA is a member of sysadmin, there shouldn't be anything you need to do.

A programmatic approach could be to create a SQL Agent job that restores the correct (according to you) memberships for administrator accounts. Set this to run on a schedule and you're guaranteed to have the permissions restored (unless UserA decided to mess with the job).

However, this is really more of a personnel issue. The people that have this level of permission on a server should be experienced and trustworthy enough not to mess with the administrative level permissions. If they don't know what they are doing, they should not be able to do it. If they know what they are doing and do it any way, you have a different problem. This may best be handled by a business policy indicating that anyone that messes with administrative level permissions may be subject to disciplinary action.

Related Question