Privilege Verification Query
SQL Server Privileges
- In all cases SYSADMIN is sufficient.
- On 2008 'VIEW DATABASE STATE' also should be sufficient.
- On 2005 it looks like SELECT on any database should be sufficient but is not documented.
- On 2000 you have to be SYSADMIN, no other options.
- This might be more info than necessary, but inquiring minds want to know, so here are the test queries run by DBO to see if you have the privileges needed:
- -- SQL Server 2000 test query
- -- the second part of query is checking for SYSADMIN granted at OS level (ie not at databse level)
- SELECT 1
- WHERE
- EXISTS (SELECT sysadmin
- FROM master.dbo.syslogins
- WHERE
- loginname IN (SELECT SYSTEM_USER) AND
- sysadmin = 1) OR
- EXISTS (SELECT sysadmin
- EXISTS (SELECT 1 'aduser'
- WHERE
- SYSTEM_USER LIKE '%\%' AND
- NOT EXISTS (select '1' FROM ::fn_get_sql(0)))
- WHERE
- -- SQL Server 2005 test query
- -- the second half ok users that have SELECT on all databases to profile
- SELECT 1
- WHERE
- (SELECT sysadmin
- FROM master.dbo.syslogins
- WHERE
- loginname IN (SELECT SYSTEM_USER) AND
- sysadmin = 1) IS NOT NULL OR
- (SELECT 1
- FROM fn_my_permissions (NULL, 'DATABASE')
- WHERE permission_name = 'SELECT') IS NOT NULL
- WHERE
- -- SQL Server 2008 test query
- -- second half says any user with 'VIEW DATABASE STATE' can profile
- SELECT 1
- WHERE
- (SELECT sysadmin
- FROM master.dbo.syslogins
- WHERE
- loginname IN (SELECT SYSTEM_USER) AND
- sysadmin = 1) IS NOT NULL OR
- (SELECT 1
- FROM fn_my_permissions (NULL, 'DATABASE')
- WHERE permission_name = 'VIEW DATABASE STATE') IS NOT NULL
- WHERE