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