script to Identify and revoke Public access on SP’s

Question

script to Identify and revoke Public access on SP’s

sreedevi_c

SSC Veteran

Points: 213
More actions
April 8, 2011 at 4:18 am

#238162

Hi all,
I want to remove access granted to the public role on SP's in sqlserver 2005 and 2008.
please suggest.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I had that same problem; someone used some script that granted all objects to the public role via a cursor that was included as some 3rd party app; yuck. some peopels understanding of SQL permissions make me shake my head.

this is what i used to generate the REVOKE statements:

--CREATE DATABASE [SandBox]

GO

USE [SandBox]

SELECT

'REVOKE ' + convert(varchar(50),x.[Action])

+ ' on ' + x.[Schema]

+ '.' + convert(varchar(50),x.[Object])

+ ' TO ' + convert(varchar(50),x.[User]) COLLATE Latin1_General_CI_AS

FROM (

SELECT

u.name COLLATE Latin1_General_CI_AS AS 'User',

schema_name(o.schema_id) As 'Schema',

o.name COLLATE Latin1_General_CI_AS AS 'Object' ,

p.permission_name COLLATE Latin1_General_CI_AS AS 'Action'

--into tmp

FROM sys.database_permissions p, sys.database_principals u, sys.all_objects o

WHERE o.object_id = p.major_id

AND p.grantee_principal_id = u.principal_id

AND p.grantee_principal_id IN (0, 2)

) X

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

I'll give the general caution not to undo the permissions Microsoft defines in their initial install of these two versions. Otherwise, you could result in an unsupported configuration.

K. Brian Kelley
@kbriankelley

sreeraj_19 SSC Rookie Points: 31 More actions · Answer 3

Hi Brian,

Thank u for ur caution.My intention is to identify and revoke public access to user stored procedures on a particular database.

Please help.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

This is pretty easy, then. Create a new blank database. Identify what objects the public role has access to, specifically the stored procedures, since that's what you're targeting. Take Lowell's script and specifically exclude those objects for the database you're modifying.

K. Brian Kelley
@kbriankelley