April 13, 2012 at 2:58 am
Hi Guys,
One of our audit finding is to remove unnecessary extended stored procedures permission from public.
All these permission to public is actually granted by default when we install SQL Server.
Is it recommended to do it? Will microsoft still provide support if we do it?
I'm not sure if my application is actually using it. Any advise on how to remove it in a low risk manner?
thanks!
April 14, 2012 at 5:40 am
Hi All,
Anyone can advise or having similar experience? Does Microsoft release any notes to advise customer not to revoke all the default from public?
thanks!
April 14, 2012 at 11:01 am
I cannot say I have seen any official guidance on revoking public's access to system procedures.
I have seen guidance related to public's server-level privileges, specifically the GRANT VIEW ANY DATABASE and GRANT CONNECT to endpoints. Microsoft provides a Policy that checks for those server-level privileges, and recommends revoking them when hardening a SQL Server installation. Read more Monitoring Best Practices using Policy Based Management > Server public Permissions
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 15, 2012 at 9:29 pm
thanks. Seen the link. "Do not grant server permissions to the server public role".
However for my case here all those privileges were granted during default installation. Do u think it's advisable to revoke it?
thanks
April 16, 2012 at 1:33 pm
chewychewy (4/15/2012)
thanks. Seen the link. "Do not grant server permissions to the server public role".However for my case here all those privileges were granted during default installation. Do u think it's advisable to revoke it?
thanks
In your case you are asking about database level permissions, specifically in the master database, not server level permissions which is what the article refers to. And yes, access to the system procedures in master you;re concerned with is setup by default during installation.
The server level permissions Microsoft is referencing in the article have to do with seeing the list of all databases on the instance as well as permissions to the default set of endpoints on the instance.
For what it is worth, even after following the guidance surrounding revoking all server level permissions from public, members of public can still see the master database which is likely so they can access said procedures stored there as well as other metadata related to the instance.
To answer your question more directly, the permissions you're asking about are not referenced in any official guidance I know of. You may get different answers from folks more familiar with specific procedures you can safely revoke access to, but knowing what some of those procedures do, I would not revoke access to any of them unless you have motivation based on a specific use-case you're trying to prevent and know it will do no harm.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 17, 2012 at 6:42 am
I came across this code on http://myitforum.com/cs2/blogs/jnelson/archive/2008/08/16/121273.aspx. Maybe it'll help...
BTW: The page also has a companion REVOKE statement builder query
SELECT
OBJECT_NAME(major_id) AS [Extended Stored Procedure],
USER_NAME(grantee_principal_id) AS [User]
FROM
sys.database_permissions
WHERE
OBJECT_NAME(major_ID) IN ('xp_availablemedia','xp_cmdshell',
'xp_deletemail','xp_dirtree',
'xp_dropwebtask','xp_enumerrorlogs',
'xp_enumgroups','xp_findnextmsg',
'xp_fixeddrives','xp_getnetname',
'xp_logevent','xp_loginconfig',
'xp_makewebtask','xp_regread',
'xp_readerrorlog','xp_readmail',
'xp_runwebtask','xp_sendmail',
'xp_servicecontrol','xp_sprintf',
'xp_sscanf','xp_startmail',
'xp_stopmail','xp_grantlogin',
'xp_revokelogin','xp_logininfo',
'xp_subdirs','xp_regaddmultistring',
'xp_regdeletekey','xp_regdeletevalue',
'xp_regenumkeys','xp_regenumvalues',
'xp_regremovemultistring','xp_regwrite')
AND USER_NAME(grantee_principal_id) LIKE 'PUBLIC'
ORDER BY 1;
Cheers,
Ken
April 17, 2012 at 9:33 am
Select permission on some extended stored procecures and system tables is required for basic connectivity, especially when users are connecting via the ODBC driver. Whatever permissions that SQL Server 2005+ assigned to the Public role by default should be considered safe.
If you have granted additional permissions to the Public role and now want to scale it back, then you may want to query the default permissions assigned to the Public role on a database that hasn't been tinkered with yet, and then use that as a baseline for droping and then re-creating the Public role in your application database.
If the application needs permissions beyond what the default Public role offers, then create a new role specifically for the purpose, and then add the application login account as a member of both roles.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 18, 2012 at 2:34 pm
We had an audit finding similar to this a couple of years ago.
Our action was to discuss the perils of removing default permissions from these objects that are granted by a base installation of SQL Server. Next we identified the extented stored procedures (xp_) and other stored procedures (sp_) in the supplied list that could be executed by a test SQL login with only public access. Once we were were done with that test we opened up a case with MS Support asking if was OK to revoke public permissions from the remaining procedures that were executable.
One other note - there are a number of procedures in the list that are by default only executable by those with 'sysadmin' role on the SQL Server to start with.
The response was basically "no, not if you want to be supported".
Unfortunately I do not have the results of the testing and case mentioned above.
Good Luck !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply