Re: How to disable dangerous extended stored procedures In SQL2005

Fancy,

If you saw the following syntax defined somewhere, could you point out the
location?

exec sp_configure 'xp_dirtree', 0;

The sp_configure stored procedure has a defined list of configurables. For
2005, the list is here:
http://technet.microsoft.com/en-us/library/ms189631.aspx

If you could disable them, you would not want to, since they are used by SQL
Server itself. I see that these by default are granted to the public role.
If you feel safe doing so, you could revoke the permission to public by:

REVOKE EXECUTE ON xp_dirtree TO public

The system itself should still be able to use these procedures (it runs as
sysadmin after all) but the general user will no longer be able to do so.
If you determine that some non-sysadmin users actually need these rights,
you might create a role in 'master' to which you add the needed users. For
that role to work, you would need to GRANT EXECUTE ON xpname TO XPUserRole.

RLF


"Fancy" <FancyFan@xxxxxxxxxxxxxxxx> wrote in message
news:eA7MycDsIHA.3940@xxxxxxxxxxxxxxxxxxxxxxx
Hi,



I have a question about SQL2005 security enhancing.

In SQL2005, I need disable some dangerous extended stored procedures, such
as xp_dirtree, xp_regread .



I got a method from Internet to achieve that:



sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

exec sp_configure 'xp_dirtree', 0;

GO

RECONFIGURE;

GO

sp_configure 'show advanced options', 0;

GO

RECONFIGURE;

GO



But it seems not work. I got an error message:



Message 15123£¬Level 16£¬status 1£¬procedure sp_configure£¬No. 51 line

option 'xp_dirtree' doesn't exist£¬maybe advanced option¡£



So I wanna know how can I disable these dangerous extended stored
procedures? I cannot find any guide from MS sites.

I hope get your help.



Thanks a lot!



Fancy Fan



7th May




.

Relevant Pages

  • Re: noise words, @@ERROR, and stop and resume indexing
    ... EXEC sp_fulltext_catalog 'adsfull', 'stop' ... > 1) check noise words inside stored procedure ... > can be solved by changing the language specific file with noise words ... ...
    (microsoft.public.sqlserver.fulltext)
  • source text file as input parameter from a stored procedure
    ... I am using an active X script in combination with a global ... transform task that loads data from a .txt file to a table. ... I can also tell that the stored procedure is correctly passing in the ... EXEC sp_OAGeterrorinfo @object, @hr ...
    (microsoft.public.sqlserver.dts)
  • Re: Repeating 3 Datapump actions between the same datasource/destinati
    ... How to loop through a global variable Rowset ... > exec p_PullDataForDates1 20050101,20050131 ... > task that configure the SourceSQLStatement for the 2 pumps (to fill in the ... > blanks for the stored procedure parameter). ...
    (microsoft.public.sqlserver.dts)
  • Re: Stored Procedure Fails Only When Called By Agent
    ... Stored Procedure 1: ... EXEC sp_addlinkedserver '10.1.2.10', 'SQL Server' ... LOCALMACHINE\myaccount/pass3, the same account that owns the job. ...
    (microsoft.public.sqlserver.replication)
  • Re: Please Help
    ... Remember, he wants to do this in a stored procedure, thus preventing the use ... exec sp_addlogin @user,@user ... > --create new sql login ... > --add to pubs datareader role ...
    (microsoft.public.sqlserver.security)