Solved

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

Posted on 2014-01-16
3
487 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

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…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now