0

Is there a way to create a sysadmin like account with only read privilege's. I know that you can give a lot of grants to an account like view server state, view any definition, view any database etc, etc.. but still than this user will not always return the same info as the real SA user.

In some system procedures there are checks implemented to see if you are sysadmin. It would be handy to have an account that can do all what you can do with the SA account except modifying anything. Is that possible?

Aleksey Vitsko
  • 6,148
  • 5
  • 39
  • 70
Ludo
  • 66
  • 3

1 Answers1

5

Exactly because of all the built-in hard-coded checks for the role name "sysadmin" this is not possible. The security team at Microsoft will have to keep working on fixing all those permission checks and come up with individual permissions. While I was working on that we managed to change quite a bunch of DBCC commands for proper checks, but there is still a lot left. However, those commands are normally not the every day required ones. Therefore you should be able to go a long way with VIEW SERVER STATE, VIEW ANY DEFINITION and VIEW ANY USER SECURABLES.

Andreas Wolter
  • 653
  • 3
  • 10