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 @objName	VARCHAR(50),
	@cmd		varchar(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