Finding top 5 analysts with the most privileges available

Version 1
    Question

    << INTERNAL ONLY>>

    Very useful timesaver script to run once customers database has been restored for finding users that have the most privileges.

    Answer

    --=================================

    -- *** Author     : MSR          ***

    -- *** Created     : 27/02/2006     ***

    --=================================

     

    -- This script identifies the 5 analysts with the most privileges in helpdesk

    /*****************************************************************************************/

     

         declare @OPERID nvarchar (255)

         declare @PRVGMASK nvarchar (255)

         declare @PRVG INT

         declare @sql1 varchar (255)

         CREATE TABLE #PRV_TMP (OPERID VARCHAR(255) PRIMARY KEY, PRVG INT)

         set nocount on

     

         declare Cur_sysobjname scroll cursor for select o.OPERID_HDW, pr.PRVGMASK_HDW from opertr_hdw o, opprvlg_hdw pr

              where o.operid_hdw=pr.oprgpid_hdw

         open Cur_sysobjname

         fetch next from Cur_sysobjname into @OPERID, @PRVGMASK

         while @@fetch_status = 0

         begin

                   select @PRVG=len(replace(@PRVGMASK,'N', ''))

                   select @sql1='Insert into #PRV_TMP (OPERID, PRVG) values (''' + @OPERID + ''', ' + convert(varchar(4), @PRVG) + ')'

                   exec (@sql1)

                   fetch next from Cur_sysobjname into @OPERID, @PRVGMASK

         end

         print '

         Analysts which have the most privileges:'

         select top 5 operid from #PRV_TMP order by prvg desc

         drop table #PRV_TMP

         close Cur_sysobjname

         deallocate Cur_sysobjname

    Environment

    Helpdesk