Overview
In this section, we will cover questions like who has access to the master database and what about permissions.
Explanation
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.
Eric has been a SQL Server DBA and Architect in the legal, software, transportation, and insurance industries for over 10 years. Currently he is the Sr Data Architect for Squire Patton Boggs, a leading provider of legal services with 47 offices in 20 countries.
Eric is a 2018-2019 Idera Ace and has co-authored 2 Idera Whitepapers.
He has been a presenter at PASS Summit, IT/DevConnections, SQLSaturdays, the in.sight transportation conference, and the Ohio North SQL Server User’s Group.
- MSSQLTips Awards: Author of the Year Contender – 2021, 2022 | Trendsetter (25+ tips) – 2021
