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]
as
---------------------------------------------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
BEGIN
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
END
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
