0

I have a database BugTracker which is accessible from "sa" user.

I have just created new user AdminBugCatcher. I have also mapped "BugTracker" database with this user. But this database should not be accessible through "sa" user.

How can we achieve the same ?

I have already tried to unlink this database from "sa" user but getting an error "Drop failed for the User "dbo" ".

Gaurav123
  • 103
  • 2
  • You can use LOGON TRIGGER to block sa connection to that database. I will later provide you with some code to do that. – Kin Shah Jun 24 '14 at 13:12
  • Refer to the answer given by Kin in an earlier post here:

    http://dba.stackexchange.com/questions/37379/limit-connection-rights-on-sql-server/37382#37382

     – Aaron Hurst Jun 24 '14 at 13:59
  • 1
    @Kin But a logon trigger only fires at login time. If they connect to master and then issue USE <database>; the trigger isn't going to fire again. This is another case where auditing is a more feasible approach than prevention - you can see when certain things happen even if you can't prevent them. As an aside, if your users can't be trusted with sa privileges, why do they have sa privileges? – Aaron Bertrand Jun 24 '14 at 20:32
  • @AaronBertrand Fair point and agreed that sa should be used very very carefully. I am on a big favor of auditing especially using server side trace when required and if your company has policy to an audit policy. – Kin Shah Jun 24 '14 at 20:55

2 Answers2

3

There is no way to prevent the "sa" login from accessing every database on the server. If the "sa" login isn't needed (which it shouldn't ever be needed) then disable it. If it is needed, then there's nothing you can do as anything that you put in place to block it, the "sa" login can remove.

mrdenny
  • 26,988
  • 2
  • 42
  • 81
2

What you can do is disable the "sa" login

enter image description here

However, I suggest you check who has the "sa" login privileges and whether these are the right persons. A monitoring tool can be an answer, as it will show you everything that the "sa" login did on your instance.

Milena Petrovic
  • 1,809
  • 12
  • 9