In order to run server scoped DMV queries, you must have VIEW SERVER STATE permission for the login you are using. Many DBAs just logon with an account that has system administrator rights, so they have no problems running DMV queries.  If you want to write a Windows or Web client application that you can use to run and display the results of DMV queries, should think about creating a login and associated database user that has just enough rights to run DMV queries.

The script below walks you though how to do this. The idea here is to create a SQL Server login with VIEW SERVER STATE rights on one server or instance, then create an identical SQL Server login with the same SID on other servers or instances of SQL Server.

The ServerMonitor database is a little database I like to create to hold a small collection of stored procedures that have server scoped DMV queries for things like page life expectancy. That way I don’t have to worry about connecting to a specific user database for those types of queries.

-- How to create a SystemMonitor SQL Server login with VIEW SERVER STATE access
-- and create a matching database user in user databases, and finally
-- create a SQL Server login with an identical SID on another instance or server.
-- This will allow you to use this login to run DMV queries.
-- Glenn Berry 
-- January 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry
-- Make sure you are in the master database
USE [master];
GO
-- Create SQL Server login for SystemMonitor
CREATE LOGIN SystemMonitor WITH PASSWORD=N'1999StrongPassword#',
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF,
CHECK_POLICY=ON;
GO
-- Give VIEW SERVER STATE permission to login
GRANT VIEW SERVER STATE TO SystemMonitor;
GO
-- Switch to ServerMonitor database (if you have one)
USE ServerMonitor;
GO
-- Create database user to go with login
CREATE USER SystemMonitor FOR LOGIN SystemMonitor;
GO
-- Create matching database users in other user databases if desired
USE [YourUserDatabaseName];
GO
CREATE USER SystemMonitor FOR LOGIN SystemMonitor;
GO
-- Allow System Monitor to view metadata in this database
GRANT VIEW DEFINITION TO SystemMonitor;
GO
USE [master];
GO
-- Get current sid information from existing server 
-- for SystemMonitor SQL Server login
SELECT [name], [sid], type_desc 
FROM sys.server_principals
WHERE type_desc = 'SQL_LOGIN'
AND [name] = 'SystemMonitor'; 
-- **** Switch connection to another SQL instance or server ******
USE [master];
GO
-- Create SQL Server SystemMonitor login on new server with same sid 
-- as existing SystemMonitor login on old server (your sid will be different)
CREATE LOGIN SystemMonitor WITH PASSWORD = '1999StrongPassword#', 
[sid] = 0x0385AA819CA4FC46BF2DC94E19265857, 
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF,
CHECK_POLICY=ON;
-- Give VIEW SERVER STATE permission to login
GRANT VIEW SERVER STATE TO SystemMonitor;
GO
-- Switch to ServerMonitor database
USE ServerMonitor;
GO
-- Create database user to go with login
CREATE USER SystemMonitor FOR LOGIN SystemMonitor;
GO
-- Create matching database users in other user databases if desired
USE [YourUserDatabaseName];
GO
CREATE USER SystemMonitor FOR LOGIN SystemMonitor;
GO
-- Allow System Monitor to view metadata in this database
GRANT VIEW DEFINITION TO SystemMonitor;