Privilege Verification Query

From DB Optimizer
Jump to: navigation, search

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 1 'aduser'
WHERE
SYSTEM_USER LIKE '%\%' AND
NOT EXISTS (select '1' FROM ::fn_get_sql(0)))


-- 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


-- 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