So in an earlier blog post I talked about how I had to grant some users the ability to create indexes in a reporting server. A couple of people have asked me how I created the domain groups and ensured that the permissions were setup correctly. In this case I did this with two SQL Scripts. The first generates some command line commands which can then be run on a domain controller in order to create the domain groups that the users will be put into. This script simply compares the groups which exist as logins and matches that against the list of databases on the SQL Server. In this case I’m filtering the databases down based on a prefix as I only want to deal with databases that start with rpt or secure. There are 5 different domain groups created for each database. I take the output of this first query and run it from the command line window on an Active Directory domain controller and the domain groups are then created. Once they are created I simply drop them into the correct folder in AD and the help desk can start dropping users into the groups.
[sql]
select ‘net group EDW1-‘ + name + ‘ /DOMAIN /ADD’
from sys.databases
where (name like ‘rpt%’
or name like ‘secure%’)
and ‘MyDomainEDW1-‘ + name not in (select name from sys.server_principals)
UNION ALL
select ‘net group EDW1_SHOWPLAN_’ + name + ‘ /DOMAIN /ADD’
from sys.databases
where (name like ‘rpt%’
or name like ‘secure%’)
and ‘MyDomainEDW1_SHOWPLAN_’ + name not in (select name from sys.server_principals)
UNION ALL
select ‘net group EDW1_CREATE_INDEX_’ + name + ‘ /DOMAIN /ADD’
from sys.databases
where (name like ‘rpt%’
or name like ‘secure%’)
and ‘MyDomainEDW1_CREATE_INDEX_’ + name not in (select name from sys.server_principals)
UNION ALL
select ‘net group EDW1_CREATE_VIEW_’ + name + ‘ /DOMAIN /ADD’
from sys.databases
where (name like ‘rpt%’
or name like ‘secure%’)
and ‘MyDomainEDW1_CREATE_VIEW_’ + name not in (select name from sys.server_principals)
UNION ALL
select ‘net group EDW1_CREATE_PROC-‘ + name + ‘ /DOMAIN /ADD’
from sys.databases
where (name like ‘rpt%’
or name like ‘secure%’)
and ‘MyDomainEDW1_CREATE_PROC-‘ + name not in (select name from sys.server_principals)
GO
[/sql]
The second script that I’ve got creates the logins for each of these domain groups, again by simply looking at the ones that don’t exist in sys.server_principals. This script generates T-SQL code which I then just copy and paste into another SQL Query window and then execute the script. The only thing that I have to remember with this script is that there are line breaks in the output so I need to ensure that I run this with the results going to text not to the default grid.
[sql]
select ‘use master
GO
CREATE LOGIN [MyDomainEDW1-‘ + name + ‘] FROM WINDOWS
GO
use ‘ + name + ‘
GO
CREATE USER [MyDomainEDW1-‘ + name + ‘] FROM LOGIN [MyDomainEDW1-‘ + name + ‘]
GO
EXEC sp_addrolemember @rolename=”db_datareader”, @membername=”MyDomainEDW1-‘ + name + ”’
GO’
from sys.databases
where (name like ‘rpt%’
or name like ‘secure%’)
and ‘MyDomainEDW1-‘ + name not in (select name from sys.server_principals)
union all
select ‘use master
GO
CREATE LOGIN [MyDomainEDW1_CREATE_INDEX_’ + name + ‘] FROM WINDOWS
GO
use ‘ + name + ‘
GO
CREATE USER [MyDomainEDW1_CREATE_INDEX_’ + name + ‘] FROM LOGIN [MyDomainEDW1_CREATE_INDEX_’ + name + ‘]
GO’
from sys.databases
where (name like ‘rpt%’
or name like ‘secure%’)
and ‘MyDomainEDW1_CREATE_INDEX_’ + name not in (select name from sys.server_principals)
UNION ALL
select ‘use master
GO
CREATE LOGIN [MyDomainEDW1_CREATE_PROC-‘ + name + ‘] FROM WINDOWS
GO
use ‘ + name + ‘
GO
CREATE USER [MyDomainEDW1_CREATE_PROC-‘ + name + ‘] FROM LOGIN [MyDomainEDW1_CREATE_PROC-‘ + name + ‘]
GO
GRANT SHOW PLAN TO [MyDomainEDW1_CREATE_PROC-‘ + name + ‘]
GO’
from sys.databases
where (name like ‘rpt%’
or name like ‘secure%’)
and ‘MyDomainEDW1_CREATE_PROC-‘ + name not in (select name from sys.server_principals)
union all
select ‘use master
GO
CREATE LOGIN [MyDomainEDW1_CREATE_VIEW_’ + name + ‘] FROM WINDOWS
GO
use ‘ + name + ‘
GO
CREATE USER [MyDomainEDW1_CREATE_VIEW_’ + name + ‘] FROM LOGIN [MyDomainEDW1_CREATE_VIEW_’ + name + ‘]
GO
GRANT SHOWPLAN TO [MyDomainEDW1_CREATE_VIEW_’ + name + ‘]
GO’
from sys.databases
where (name like ‘rpt%’
or name like ‘secure%’)
and ‘MyDomainEDW1_CREATE_VIEW_’ + name not in (select name from sys.server_principals)
union all
select ‘use master
GO
CREATE LOGIN [MyDomainEDW1_SHOWPLAN_’ + name + ‘] FROM WINDOWS
GO
use ‘ + name + ‘
GO
CREATE USER [MyDomainEDW1_SHOWPLAN_’ + name + ‘] FROM LOGIN [MyDomainEDW1_SHOWPLAN_’ + name + ‘]
GO
GRANT SHOWPLAN TO [MyDomainEDW1_SHOWPLAN_’ + name + ‘]
GO’
from sys.databases
where (name like ‘rpt%’
or name like ‘secure%’)
and ‘MyDomainEDW1_SHOWPLAN_’ + name not in (select name from sys.server_principals)
[/sql]
The great thing about these scripts is that they produce a consistent set of domain groups for each new database that is added to the reporting server so that the helpdesk can quickly and easily figure out which groups users need to be placed into. Could I have done this in PowerShell? Yeah, I’m sure that I could have. But I don’t know the PowerShell cmdlets that would have been needed to create the domain accounts, so honestly I didn’t bother even trying. I knew the SQL commands to do this off the top of my head. While someone else probably could have written this in PowerShell in 10 minutes, I did it in T-SQL in 10 minutes and it’s reproduceable.
(Don’t forget for the index domain groups I’ve got a SQL Agent job that grants the rights to those that runs every night.)
If I need to add another set of domain groups into the script, I can simply copy and paste an existing one and make the few needed changes.
Denny
