Technical Article

Check missing exec permission on Stored proc

,

Stored procedure usp_CheckMissingPermission_for_User checks missing exec permission for given user or group on all the stored procedures within the database. It not only detects missing exec permission, but also generates a scripts for you to fix the problem.

create procedure usp_CheckMissingPermission_for_User 
@UserorGroupName varchar(100)
as
set nocount on

DECLARE @objNameVARCHAR(50),
@cmdvarchar(1000)

SET @objName = ''

WHILE @objName IS NOT NULL
    BEGIN
select @objName = MIN(object_name(id))
from sysobjects
where object_name(id) not like 'dt_%'
and type = 'p'
and object_name(id) not in 
(select object_name(sp.id)
from sysprotects sp,
sysobjects so,
sysusers su
where so.type = 'p'
and so.id = sp.id
and object_name(sp.id) not like 'dt_%'
and sp.uid = su.uid
and su.name = @UserorGroupName)
and object_name(id) > @objName


IF @objName IS NOT NULL
    BEGIN
SET @cmd = 'grant exec on ' + @objName + ' to ' + @UserorGroupName 
PRINT @cmd
EXEC (@cmd)
    END
    END

Rate

Share

Share

Rate