SP Permissions 1

[evaleah]

Is there a way to automatically go through all the stored procs in a DB and ensure that they have public permissions?

Thanks!
Eva

 

[LogicalmanUS]

evaleah,

Here you go, this will return all users having permissions on all User SProcs.
If you want it to include DB Sprocs, just take the :
AND (NOT (sysobjects.name LIKE N'dt_%')) line out..

SELECT sysobjects.name, sysobjects.id,
sysobjects.xtype, syspermissions.grantee,
syspermissions.grantor, sysusers.name AS UID
FROM sysusers RIGHT OUTER JOIN
syspermissions ON sysusers.uid = syspermissions.grantee
RIGHT OUTER JOIN sysobjects ON syspermissions.id = sysobjects.id
WHERE (sysobjects.xtype = 'P')
AND (NOT (sysobjects.name LIKE N'dt_%'))

You can copy and paste this code into a view on the database and run it.

If you then need it assign such permissions, you will need it in a SProc, but remember you must have eithet dbo, sa or db_securityadmin priveledges to run it.

If you want this as a SProc, to run on any database I can send you that also tomorrow.

Logicalman

 

[evaleah]

Thanks. That is actually more than what I was looking for.

Nope, I can put it into a stored proc, thanks.