?
Solved

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

Posted on 2014-01-16
3
Medium Priority
?
528 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

801 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