REVOKE GRANT PERMISSIONS

Question

Post reply

REVOKE GRANT PERMISSIONS

Angelindiego

SSCertifiable

Points: 6048
More actions
August 25, 2015 at 4:38 pm

#319989

I have a large number of stored procs on one of my servers that SOMEHOW got GRANT EXECUTE TO PUBLIC put on them. I want to revoke all of those!! Fast and easy script to loop thru all if exists and remove????? Thank you in advance!! :hehe:
Thank you!!,
Angelindiego

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

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

Ken McKelvey SSCoach Points: 18766 More actions · Answer 1

This should get you started:

SELECT 'REVOKE '

+ P.[permission_name] COLLATE DATABASE_DEFAULT + ' ON '

+ QUOTENAME(S.name COLLATE DATABASE_DEFAULT)

+ '.' + QUOTENAME(C.name COLLATE DATABASE_DEFAULT)

+ ' TO ' + QUOTENAME(U.name COLLATE DATABASE_DEFAULT) + ';'

FROM sys.database_permissions P

JOIN sys.sysusers U

ON P.grantee_principal_id = U.uid

JOIN sys.procedures C

ON P.major_id = C.[object_id]

JOIN sys.schemas S

ON C.[schema_id] = S.[schema_id]

WHERE P.[permission_name] = 'EXECUTE'

AND P.[state_desc] = 'GRANT'

AND U.name = 'PUBLIC';

Angelindiego SSCertifiable Points: 6048 More actions · Answer 2

THANK YOU SO MUCH!!!!!! :w00t:

Thank you!!,

Angelindiego

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

Nice job, Ken.

That's a good example of a great technique. With SQL, you can run queries that generate the SQL you want to execute. Then all you have to do is copy/paste and then run it. There are many applications of this, from permissions to users to anything else. If you can query it, you can probably query the repeated SQL that you want to run.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions