So I ran across a pretty interesting requirement when setting up a new reporting database server. The end users who run queries against the databases on this server are pretty good at performance tuning their own queries so they wanted the ability to create indexes on the tables on the reporting server. I figured no problem I’d just grant them the ALTER ANY INDEX right and be done with it. The only problem is that there isn’t any such right.
There’s also no right at the table level which can be granted which will give the user the ability to create indexes without giving them other rights on the objects.
Some Background
So I started doing a little bit of hacking around the SQL Server and I came up with a solution that’ll work.
A little background on how security is setup on the SQL Server to begin with. All the rights on this SQL Server are controlled via Active Directory domain groups. There are four rights which we are concerned with, being a member of the db_owner fixed database role, being able to query any table or view in the database, being able to create stored procedures and being able to create indexes on any table in the database. To handle this there are 4 domain groups for each database on the reporting server name {ServerName}-{Privelege}-{DatabaseName} so for example there is a domain group named “EDW1_CREATE_PROC-optin” which grants the users of the domain group the CREATE PROCEDURE right within the optin database on the SQL Server EDW1.
Like the create procedure domain group I’ve also go groups named CREATE_INDEX, CREATE_VIEW, etc. to grant all the needed rights.
The Workaround
To workaround there not being a right to create any index that you want, I granted the CREATE_INDEX right the right to ALTER any table. This allows the user to change the table, create indexes, and drop the table. In case new tables are added to the system I setup a job which simply loops through all the databases and grants the ALTER right to the table to the user within each database that maps to the specific domain group for that database using this T-SQL code. This job is simply configured to run nightly and the SLA with the business users is that when new tables are added they simply won’t be able to create indexes on the new table until the next day. As this data is simply being replicated over from production there shouldn’t be that many new tables being added very often.
[sql]EXEC sp_MSforeachdb ‘
USE [?]
declare @schema sysname, @table sysname, @database sysname
declare tables CURSOR for select name, schema_name(schema_id) from sys.tables WHERE is_ms_shipped = 0
declare @cmd nvarchar(4000)
open tables
fetch next from tables into @table, @schema
while @@fetch_status = 0
begin
set @cmd = ”GRANT ALTER ON [” + @schema + ”].[” + @table + ”] TO [DomainNameEDW1_CREATE_INDEX_” + db_name() + ”]”
print @cmd
if exists (select * from sys.database_principals WHERE name = ”DomainNameEDW1_CREATE_INDEX_” + db_name())
exec (@cmd)
fetch next from tables into @table, @schema
end
close tables
deallocate tables'[/sql]
You’ll notice within the cursor above that I’m also checking on if the domain group exists or not, so that if it hasn’t been created for some reason the script won’t throw an error message and stop.
Now as the users are being granted the ALTER right on each table, this is giving them way more rights than they actually need on the table. To resolve this I created a DDL trigger for the server which looks to see if the user is a member of the db_owner fixed database role, and if they aren’t it rolls back the command and tells them to call IT as shown in the T-SQL below.
[sql]
CREATE TRIGGER PreventSchemaChange
ON ALL SERVER
FOR ALTER_TABLE, DROP_TABLE
AS
SET NOCOUNT ON
create table #groups (account sysname, type sysname, privilege sysname, mapped sysname, path sysname)
declare @login sysname = suser_sname(), @database sysname, @event_data XML = EVENTDATA()
SET @database = @event_data.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘sysname’)
insert into #groups
exec xp_logininfo @login, ‘all’
if not exists (select * from #groups where [path] like ‘%’ + @database + ‘%dbowner’) or IS_SRVROLEMEMBER(‘sysadmin’, suser_sname()) = 1
begin
rollback
raiserror(‘Access to changing or dropping objects is denied. Please contact IT to make this change.’, 16, 1)
end
GO
[/sql]
You’ll notice that I had to do some hacking around within that DDL trigger to make this work. That’s because DDL triggers that are server scoped (which mine is) always fire in the master database. So because of this I couldn’t use the normal IS_ROLEMEMBER function that I wanted to use. The quickest solution that I could come up with was to get the database name from the EVENTDATE() function and use that in combination with the xp_logininfo system stored procedure to see if the user is a member of the correct domain group. This worked because of my domain group naming convention that I had setup earlier.
Now I’ve put in a connect item to make all of this easier to deal with as I can’t be the only one who has this sort of requirement. Hopefully my request will get some traction as I wouldn’t think that this would be all that hard to implement. Granted even if they do add the feature I’m asking for it probably won’t be until the next major release, and probably won’t be back ported into SQL Server 2012 or SQL Server 2008 R2 (this system is SQL Server 2008 R2) but hopefully we can get it in a future release so complex hacks like this aren’t required.
Denny
