Grant Exec to all SPs to user permission

set nocount on
DECLARE @curObj VARCHAR(100)
DECLARE @grant_state VARCHAR(100)
SELECT A.name
INTO #objList
FROM sysobjects A
WHERE XTYPE = 'P' and name not like 'dt_%'

WHILE (SELECT COUNT(*) FROM #objList) > 0
  BEGIN
    SET @curObj = (SELECT TOP 1 name FROM #objList)
    SET @grant_state= 'Grant exec on ' + @curObj + ' to [CORP\NBS5UNM]'
select @grant_state
exec (@grant_state)
DELETE FROM #objList WHERE name = @curObj

    END

DROP TABLE #objList
GO

To find out all permissions for database

Declare @@dbname varchar(10)
declare @@xx nvarchar(4000)

select @@dbname='dm_audit'

--select @xx='select * from '+@dbname+'..sysobjects'
--exec sp_executesql @xx
select @@xx='SELECT  usu.name ,case  when (usg.uid is null) then '+'''public'''+'
                else usg.name end  as' +'''GroupName'''+',lo.loginname,'+''''+@@dbname+''''+' as Dbname
                  ,usu.uid 
                --,usu.sid 
        into groupname from '+@@dbname+'..sysusers usu left outer join 
     ('+@@dbname+'..sysmembers mem inner join '+@@dbname+'..sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid 
      left outer join master.dbo.syslogins  lo on usu.sid = lo.sid 
         where 
       (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1) and 
       (usg.issqlrole = 1 or usg.uid is null) order by usu.name'

--print @xx

exec sp_executesql @@xx
go
alter table groupname add AssignedRoles varchar(100)
go

declare @uid        int,
    @uid_inner int
declare @groupname varchar(100),
    @groupname1 varchar(50)
set @groupname=''
declare groupname cursor for
select uid,groupname from groupname
Open groupname
    fetch next from groupname into @uid,@groupname1
    while @@fetch_status=0
    begin
        --update groupname set groupname_multiple = groupname from groupname where uid=@uid
        declare inner_groupname cursor for
        select uid from groupname where uid=@uid and groupname=@groupname1
       
        open inner_groupname
        fetch next from inner_groupname into @uid_inner
        WHILE @@FETCH_STATUS = 0
        BEGIN
            select @groupname=@groupname + ','+groupname from groupname where uid=@uid --and groupname=@groupname1
            fetch next from inner_groupname into @uid_inner
            update groupname set AssignedRoles = @groupname from groupname
                            where uid=@uid and groupname=@groupname1
        end
        close inner_groupname
        deallocate inner_groupname
        set @groupname=''
        fetch next from groupname into @uid,@groupname1
    end
close groupname
deallocate groupname
go

update groupname set Assignedroles= substring(assignedroles,2,len(assignedroles))
go

Declare @@dbname varchar(10)
declare @@xx nvarchar(200)
select @@dbname='dm_audit'
select @@xx='select distinct name as Usernamein_DB,Loginname as Mapped_login,AssignedRoles,dbname into '+@@dbname+'_Users from groupname'
exec sp_executesql @@xx
go
drop table groupname
--drop table dm_audit_users
--select * from dm_audit_users