0

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?

0

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.

  • This is a bit of an odd scenario. It's not a personnel issue, really, this server is setup this way by design as a sort of sandbox for another program (operating as UserA). The only restriction is that sa needs to be able to monitor a specific table and delete the database when play time is over. – p.s.w.g Jun 5 '17 at 21:21
0

I think I've figured it out a convoluted way to do it. I have to change the DENY to:

DENY CONTROL ON SCHEMA::[sa_schema] TO [UserA];

This has two effects:

  • It's a lot more difficult for UserA to select from the table despite it being public:

    CREATE USER [foo] WITHOUT LOGIN;
    EXECUTE AS USER = 'foo';
    SELECT * FROM [sa_schema].[table1];
    
  • It's not possible to modify permissions on the table:

    CREATE ROLE [Denied];
    ALTER ROLE [Denied] ADD MEMBER [sa];                     -- fine
    DENY SELECT ON OBJECT::[sa_schema].[table1] TO [Denied]; -- error
    

    Msg 15151, Level 16, State 1, Line 16

    Cannot find the object 'table1', because it does not exist or you do not have permission.

This still feels a bit kludgey, but it's better than making sa reset its permissions constantly.

0

This question is completely crazy starting from attempts to give/deny permissions to sysadmin finishing with manipulations with SA user. There cannot be any SA user in database, you just not permitted to execute

create user sa from login sa

This attempt will always failed with error

Msg 15405, Level 16, State 1, Line 1

Cannot use the special principal 'sa'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.