By: Eric Blinn
Overview
In this section we will cover questions like: who has access to the master database and permissions.
Who has access to the SQL Server master database and are there different levels of permissions?
Everyone with access to the instance has access to the master database via the public profile. The permissions for the objects within the master database (using TSQL commands) will be exactly the same as a user using the SSMS GUI or wizards. For instance, if a user cannot grant membership to the sysadmin server role using the SSMS GUI, they will not be able to short circuit that permission by executing the underlying stored procedure directly.
One special permission needed to query some of the instance-wide views in master is called "VIEW SERVER STATE". That permission is automatically included with membership in the sysadmin role. A user without those permissions will be unable to query many DMVs. The sys.dm_os_performance_counters view is among those.
Most other views will be able to be queried and most procedures able to be executed for all users. For some of them the difference is going to be the output of the queries. For instance, a sysadmin will see every spid when running sp_who2, but a regular user will only see 1 row, their own.
Last Update: 10/8/2019
