How to run a command to identify and revoke permission in SQL Server 2005

I recently came across this solution, but i am very new to SQL Server. How do I run this command?

SQL TO VIEW EXTENDED STORED PROCS FOR WHICH PUBLIC HAS RIGHTS

This script will identify any of the said XPs on a SQL 2005 server which have EXECUTE rights granted to PUBLIC

SNIPPET #1 – Identify extended stored procedures for which PUBLIC has rights

USE MASTER;

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;

OUTPUT #1

xp_regread public
xp_cmdshell public

Now, if you want to revoke the rights, you can modify that code so that it outputs a bunch of REVOKE statements which you can copy and then run from SQL Management Studio

SNIPPET #2 – Create REVOKE statements

USE MASTER;

SELECT
‘REVOKE ALL ON ‘ + OBJECT_NAME(major_id) + ‘ FROM ‘ + USER_NAME(grantee_principal_id)
FROM
sys.database_permissions
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;

OUTPUT #2

REVOKE ALL ON xp_regread FROM PUBLIC
REVOKE ALL ON xp_cmdshell FROM PUBLIC
cgooden01Asked:
Who is Participating?
 
Kent FichtnerInformation Technology Systems SupervisorCommented:
Can you send a link to this post?  I tried to run it and there must be a table that you are supposed to input that i don't have or know about.
0
 
cgooden01Author Commented:
No viable solution was provided and would like this thrown out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.