Solved

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

Posted on 2014-01-16
3
514 Views
Last Modified: 2014-01-29
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
0
Comment
Question by:cgooden01
  • 2
3 Comments
 
LVL 4

Expert Comment

by:Kent Fichtner
ID: 39786789
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
 

Accepted Solution

by:
cgooden01 earned 0 total points
ID: 39788414
0
 

Author Closing Comment

by:cgooden01
ID: 39817275
No viable solution was provided and would like this thrown out.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question