0

MS SQL-SERVER 2016

I am currently using row level security on a database that has restrictions based on user name. This security protects rows on a table that stores application level settings - i.e. if you have bought it you can see it, if you are head office you can see everything.

The problem I have is that if you log in as SA, a simple "EXECUTE AS USER" call allows impersonation of the head office account. Is there any way to block SA from impersonating a particular user? Even if its password protected or similar.

REVOKE IMPERSONATE ON USER:: HeadOffice TO SA

Will not work. Since our clients host the SQL themselves they will always have the SA log in details, and anyone with a copy of SSMS and a bit of know how can work around the security quite easily. Either by impersonation or even just switching the security policy off.

I think the fact that clients will have SA access makes this idea a non-starter but would love to hear any suggestions that anyone has for security work arounds.

Many thanks.

Matthew Baker
  • 2,637
  • 4
  • 24
  • 49
  • 1
    Change the password, so people stop logging in as `sa`. – Aaron Bertrand Jan 16 '18 at 14:11
  • 1
    You can't revoke permissions to a `sysadmin`. They have permissions to do anything and everything on a server, regardless of any other `GRANT`, `DENY` and `REVOKE` permissions that have been set. Like @AaronBertrand said, if you don't want people to be accessing data they shouldn't be, don't allow them to have `sa` access. – Thom A Jan 16 '18 at 14:16

1 Answers1

0

Looks like the answer is what I expected - you can't do it.

The solution I've actually ended up using was outside the database - i.e. the database provides info to a web site. I'm going to build a level of system checks into the vb services. can't be done at database level when the clients have sa access.

Thanks for the help guys.

Matthew Baker
  • 2,637
  • 4
  • 24
  • 49