Monday, 22 July 2013

Extracting Users from Windows Groups on Report Server

There may be occasions when using windows active security is not ideal so you may need to create and maintain your own windows groups for security on reports. One of the downfalls is to be able to extract a list of the windows groups you have on the server and presenting it to management. It is quite frustrating to be asked about who the users are for which groups and the recipients for report subscriptions.
To get round this issue I wrote some script to extract the groups and users from a windows server, once extracted a report can be easily extracted so you can see who has access.

I have used an SSIS package to do this:

The package runs a SP on the master database on the SSRS server and then inserts the data to another server which is used for reporting. The reason for creating a package was due to not being able to insert the data to another database on a different server.

--SP extracts the groups and users
proc [dbo].[my_sp_servergroups]

---------------------------------------------windows groups
if object_id ('my_tbl_groups') is not null
drop table my_tbl_groups
if object_id ('my_tbl_groups1') is not null
drop table my_tbl_groups1
create table my_tbl_groups (groupname varchar(100))
create table my_tbl_groups1 (groupname varchar(100),group_name varchar(100), RowNumber int)

insert into my_tbl_groups
exec xp_cmdshell 'net localgroup'

delete from my_tbl_groups where left(isnull(rtrim(groupname),'MpTI'),4) <> '*ssr'
insert into my_tbl_groups1
select *,right(rtrim(groupname),len(rtrim(groupname))-1) as group_name,
(select count(*) from my_tbl_groups as b where b.groupname <= a.groupname)   AS RowNumber
 from my_tbl_groups a
--select * from my_tbl_groups1

------------------------------group members
if object_id ('my_tbl_groupsmembers') is not null
drop table my_tbl_groupsmembers

create table my_tbl_groupsmembers (members char(200))

if object_id ('my_tbl_servergroups') is not null
drop table my_tbl_servergroups

create table my_tbl_servergroups (members char(200), group_name char(200))

DECLARE @number_of_groups INT  set  @number_of_groups  = (select count( distinct groupname) from my_tbl_groups1 )
DECLARE @counter int set @counter= 1
declare @text char(200)

truncate table my_tbl_servergroups
truncate table my_tbl_groupsmembers

WHILE @counter <= @number_of_groups
  set @text = (select 'net localgroup '+group_name from my_tbl_groups1 where rownumber = @counter )
  insert into my_tbl_groupsmembers exec xp_cmdshell @text

  insert into my_tbl_servergroups
   select *, (select group_name from my_tbl_groups1 where rownumber = @counter) as group_name  from my_tbl_groupsmembers
    truncate table my_tbl_groupsmembers
   SET @counter = @counter + 1


delete my_tbl_servergroups where left(isnull(members,'Empty'),6)<> ''

--The script below copies the data to another server

if object_id ('my_tbl_servergroups') is not null
drop table dbo.my_tbl_servergroups
create table my_tbl_servergroups (members char(200), group_name char(200))

insert into my_tbl_servergroups
select *   from [SERVERNAME].[master].[dbo].my_tbl_servergroups

Keep SSRS (SSRS2016) report manager awake

When running a report for the first time in report manager it takes a while to run, after this initial run reports run fine.  There are a ...