Developer database permissions

Here’s a suggestion on how you can grant developers plenty of permissions on a dev or test database without just adding them to the db_owner role (which is going too far).

I found a post by Brent Ozar talking about podcasts he likes. Thanks for the tips, Brent. I checked them out and I particularly like the SQL Data Partners podcast. Thanks for the excellent listening, Carlos. I’m working through the episodes and episode 8 ‘The Principal of Least Privilege’ with Robert Verrell (aka SQL Cowbell) really got me thinking.

Robert talked about why adding developers to the db_owner role is a bad idea, even on a dev server. Then he proposed adding them to a custom high-powered database role, db_developer, instead. This way they have enough rights to do things they need to but hopefully they can’t destroy the server. He blogged about it here.

I like the idea but we regularly refresh our our test databases by restoring from production backups and sanitising sensitive data. This process would undo the db_developer role so I wrapped it up in a stored procedure to make it easy to re-run on each relevant database at the end of the refresh job:

if exists (	select 	1
		from 	dbo.sysobjects
		where 	id = object_id(N'dbo.dba_AddDBDeveloperRole')
			and objectproperty(id, N'IsProcedure') = 1    )
	drop proc dbo.dba_AddDBDeveloperRole;
go

set ansi_nulls on;
set quoted_identifier on;
go

create proc dbo.dba_AddDBDeveloperRole
(
	@DBName		sysname
	, @Login	sysname
	, @DebugMode	bit	= 0
)
as

/*
---------------------------------------------------------------------------------------------------------------------------------------------------
Version : 1.01
Date    : 22/12/2015

Grant developers lots of permissions without the really dangerous ones like db_owner or sysadmin.
Based on this blog post: http://sqlcowbell.com/wordpress/why-nobody-ever-needs-the-db_owner-role/.

Original script from that post:

CREATE ROLE [db_developer] AUTHORIZATION [dbo]
 GRANT ALTER ANY APPLICATION ROLE TO [db_developer]
 GRANT ALTER ANY ASSEMBLY TO [db_developer]
 GRANT ALTER ANY DATABASE DDL TRIGGER TO [db_developer]
 GRANT ALTER ANY DATASPACE TO [db_developer]
 GRANT ALTER ANY FULLTEXT CATALOG TO [db_developer]
 GRANT ALTER ANY MESSAGE TYPE TO [db_developer]
 GRANT ALTER ANY SCHEMA TO [db_developer]
 GRANT CREATE AGGREGATE TO [db_developer]
 GRANT CREATE ASSEMBLY TO [db_developer]
 GRANT CREATE DATABASE DDL EVENT NOTIFICATION TO [db_developer]
 GRANT CREATE DEFAULT TO [db_developer]
 GRANT CREATE FULLTEXT CATALOG TO [db_developer]
 GRANT CREATE FUNCTION TO [db_developer]
 GRANT CREATE PROCEDURE TO [db_developer]
 GRANT CREATE ROLE TO [db_developer]
 GRANT CREATE RULE TO [db_developer]
 GRANT CREATE SCHEMA TO [db_developer]
 GRANT CREATE SERVICE TO [db_developer]
 GRANT CREATE SYNONYM TO [db_developer]
 GRANT CREATE TABLE TO [db_developer]
 GRANT CREATE TYPE TO [db_developer]
 GRANT CREATE VIEW TO [db_developer]
 GRANT CREATE XML SCHEMA COLLECTION TO [db_developer]
 GRANT DELETE TO [db_developer]
 GRANT EXECUTE TO [db_developer]
 GRANT INSERT TO [db_developer]
 GRANT REFERENCES TO [db_developer]
 GRANT SELECT TO [db_developer]
 GRANT SHOWPLAN TO [db_developer]
 GRANT UPDATE TO [db_developer]
 GRANT VIEW DATABASE STATE TO [db_developer]
 GRANT VIEW DEFINITION TO [db_developer]
---------------------------------------------------------------------------------------------------------------------------------------------------
*/

set nocount on;

declare @SQL nvarchar(4000);

print 'Database: ' + @DBName;

print 'Create role';
set @SQL =	'use [' + @DBName + ']'
		+ ' if not exists (select 1 from sys.database_principals where type_desc = ''DATABASE_ROLE'' and name = ''db_developer'')'
		+ ' create role [db_developer] authorization [dbo]';
if @DebugMode = 1
	print @SQL;
else
	exec sp_executesql @SQL;

print 'Grant permissions to the role';
set @SQL =	'use [' + @DBName + ']'
		+ ' grant alter any application role to [db_developer];'
		+ ' grant alter any assembly to [db_developer];'
		+ ' grant alter any database ddl trigger to [db_developer];'
		+ ' grant alter any dataspace to [db_developer];'
		+ ' grant alter any fulltext catalog to [db_developer];'
		+ ' grant alter any message type to [db_developer];'
		+ ' grant alter any schema to [db_developer];'
		+ ' grant create aggregate to [db_developer];'
		+ ' grant create assembly to [db_developer];'
		+ ' grant create database ddl event notification to [db_developer];'
		+ ' grant create default to [db_developer];'
		+ ' grant create fulltext catalog to [db_developer];'
		+ ' grant create function to [db_developer];'
		+ ' grant create procedure to [db_developer];'
		+ ' grant create role to [db_developer];'
		+ ' grant create rule to [db_developer];'
		+ ' grant create schema to [db_developer];'
		+ ' grant create service to [db_developer];'
		+ ' grant create synonym to [db_developer];'
		+ ' grant create table to [db_developer];'
		+ ' grant create type to [db_developer];'
		+ ' grant create view to [db_developer];'
		+ ' grant create xml schema collection to [db_developer];'
		+ ' grant delete to [db_developer];'
		+ ' grant execute to [db_developer];'
		+ ' grant insert to [db_developer];'
		+ ' grant references to [db_developer];'
		+ ' grant select to [db_developer];'
		+ ' grant showplan to [db_developer];'
		+ ' grant update to [db_developer];'
		+ ' grant view database state to [db_developer];'
		+ ' grant view definition to [db_developer];';
if @DebugMode = 1
	print @SQL;
else
	exec sp_executesql @SQL;

print 'Create a user for the login';
set @SQL =	'use [' + @DBName + ']'
		+ ' if not exists (select 1 from sys.database_principals where type_desc = ''SQL_USER'' and name = ''' + @Login + ''')'
		+ ' create user [' + @Login + '] for login [' + @Login + '] with default_schema = [dbo];';
if @DebugMode = 1
	print @SQL;
else
	exec sp_executesql @SQL;

print 'Add user to role';
set @SQL =	'use [' + @DBName + ']'
		+ ' if not exists ('
		+ 'select 1'
		+ ' from sys.database_role_members rm'
		+ ' inner join sys.database_principals r on r.principal_id = rm.role_principal_id'
		+ ' inner join sys.database_principals m on m.principal_id = rm.member_principal_id'
		+ ' where r.type_desc = ''DATABASE_ROLE'''
		+ ' and r.name = ''db_developer'''
		+ ' and m.name = ''' + @Login + ''''
		+ ')'
		+ ' alter role [db_developer] add member [ITDev];';
if @DebugMode = 1
	print @SQL;
else
	exec sp_executesql @SQL;

go

The proc is re-runnable and only does the bits that need to be done. It doesn’t check each permission because you don’t get an error if it’s already granted.

Note that the actual permissions bit is just a list so you could easily comment out any you don’t want to grant. Indeed you really should look at all these permissions and see what they mean.

If you set @DebugMode = 1 it will just print out the SQL instead of executing it. This was very handy during development and you could use it if you change anything or if you want to run the changes past somebody for compliance checking.

You can call it for a specific database like this:

exec master.dbo.dba_AddDBDeveloperRole
	@DBName = 'DatabaseX'
	, @Login = 'LoginY';

Or you could apply it to all user databases like this:

print 'Grant rights to DevTeam'

declare @DBName sysname;

declare db_cursor cursor
local forward_only
for
select	name
from	sys.databases
where	name not in ('master', 'model', 'msdb', 'tempdb')
	and state_desc = 'ONLINE'
	and is_read_only = 0
order by name;

open db_cursor;

	fetch next from db_cursor
	into 	@DBName;

	while @@fetch_status = 0
	begin
		exec master.dbo.dba_AddDBDeveloperRole
			@DBName = @DBName
			, @Login = 'DevTeam';

		fetch next from db_cursor
		into 	@DBName;
	end

close db_cursor;
deallocate db_cursor;

Tweak the WHERE clause to exclude other databases.

You can download the code here.

Reporting security audit

Today I set out to produce a list of who has access to each report so I could get this audited by the business management. I didn’t think it would take long. Download a couple of scripts, evaluate them, pick a good one, execute it, and on with the next job. After all, this is crucial stuff. There’s no point agonising over reducing users’s rights in the database engine to the minimum possible if you then go and give them access to reports that summarise all the data in a readable, saveable, printable form. Clearly then, security auditing of Reporting Services must be a common practice. Or so I thought.

Well, I found bits of the puzzle but I didn’t see anything packaged up and ready to go. I’m sure there are tools you can buy but I kind of resent paying serious money for a product that basically just queries my own data. I prefer source code where I can see what’s going on and learn something new.

So I pulled the pieces together, solved a few problems myself and came up with a stored proc that goes through Reporting’s security records for each report, resolves any domain groups recursively down to lists of users, and lists it all out. If you have a look through, you’ll see Reporting doesn’t make this security data easy to get hold of. Also, I had to add a caching mechanism for domain group membership because it took ages to query the domain that many times.

set quoted_identifier on
go
set ansi_nulls on
go

if exists (    select     1
        from     dbo.sysobjects
        where     id = object_id(N'dbo.rpt_ITReportsSecurity')
            and objectproperty(id, N'IsProcedure') = 1    )
    drop procedure dbo.rpt_ITReportsSecurity
go

create proc dbo.rpt_ITReportsSecurity
as

/*
----------------------------------------------------------------------
Version:    1.0
Date:        01/12/2009
----------------------------------------------------------------------
*/

set nocount on

declare @FolderName        nvarchar(850)
    , @ReportName        nvarchar(850)
    , @ReportPath        nvarchar(850)
    , @idoc            int
    , @xmlfile        varchar(8000)
    , @GroupUserName    varchar(50)
    , @Role            varchar(50)
    , @GroupNoDomain    varchar(50)
    , @i            int
    , @cmd            nvarchar(2000)

create table #perms
(
    GroupUserName    varchar(50)
    , Role        varchar(50)
);

create table #allperms
(
    FolderName    nvarchar(850)
    , ReportName    nvarchar(850)
    , GroupUserName    varchar(50)
    , [Role]    varchar(50)
);

create table #temp_domaingroup
(
    [output] varchar(8000)
);

create table #group_member
(
    [Group]        varchar(50)
    , Member    varchar(100)
    , [Role]    varchar(50)
);

-- Loop through reports
declare report_cursor cursor
local forward_only
for
select    f.Name as Folder
    , r.Name as ReportName
    , r.Path as ReportPath
from    ReportServer.dbo.Catalog r with (nolock)
    inner join ReportServer.dbo.Catalog f with (nolock) on f.ItemID = r.ParentID
where    r.Type = 2 -- Report
order by f.Name
    , r.Name

open report_cursor

    fetch next from report_cursor
    into     @FolderName
        , @ReportName
        , @ReportPath

    while @@fetch_status = 0
    begin

        -- Get the user/groups and their roles from the XML

        truncate table #perms;

        set @xmlfile = (select    sd.XmlDescription
                from    ReportServer.dbo.Catalog c
                    inner join ReportServer.dbo.Policies p on p.PolicyID = c.PolicyID
                    left join ReportServer.dbo.SecData sd on sd.PolicyID = p.PolicyID and AuthType = 1
                where    c.Path = @ReportPath
                    and p.PolicyFlag = 0) --as far as I can tell, this means not a system policy

        exec sp_xml_preparedocument @idoc output, @xmlfile

        insert    #perms
            (GroupUserName
            , [Role])
        select    GroupUsername
            , Role
        from    openxml    (@idoc, N'/Policies/Policy/Roles/Role',2)
            with    (GroupUsername varchar(50) '../../GroupUserName'
                , Role varchar(50) './Name')

        exec sp_xml_removedocument @idoc;

        -- Loop through users/groups
        declare group_cursor cursor
        local forward_only
        for
        select    GroupUserName
            , [Role]
        from    #perms
        where    GroupUserName <> 'BUILTIN\Administrators'
            and [Role] <> 'View Folders Role';

        open group_cursor

            fetch next from group_cursor
            into     @GroupUserName
                , @Role

            while @@fetch_status = 0
            begin

                -- Strip the domain off the user/group

                set @GroupNoDomain = @GroupUserName
                set @i = charindex('\', @GroupNoDomain)

                if @i > 0
                begin
                    set @GroupNoDomain = substring(@GroupNoDomain, @i + 1, 100)
                end

                -- Users in the groups are cached so we only need to query the domain once for each group

                if not exists (select 1 from #group_member where [Group] = @GroupNoDomain)
                begin
                    -- Query the domain

                    truncate table #temp_domaingroup;

                    set @cmd = 'dsquery group -name "' + @GroupNoDomain + '" | dsget group -members -expand';

                    insert    #temp_domaingroup
                    exec xp_cmdshell @cmd;

                    if exists (select 1 from #temp_domaingroup where [output] like 'dsget failed%')
                    begin
                        -- It's a user, not a group, so add it to the cache

                        insert    #group_member
                            ([Group]
                            , Member
                            , [Role])
                        values    (@GroupNoDomain
                            , @GroupUserName
                            , @Role);
                    end
                    else
                    begin
                        -- Add the users in the group to the cache
                        insert    #group_member
                            ([Group]
                            , Member
                            , [Role])
                        select    @GroupNoDomain
                            , substring(x.Member, 5, len(x.Member) - 4)
                            , @Role
                        from    (
                            select    substring([output], 1, charindex(',OU=', [output], 1) - 1) as Member
                            from    #temp_domaingroup
                            where    [output] is not null
                            ) x;
                    end
                end

                -- Add the user or group users and their roles to the permissions list

                insert    #allperms
                    (FolderName
                    , ReportName
                    , GroupUserName
                    , [Role])
                select    @FolderName
                    , @ReportName
                    , Member
                    , [Role]
                from    #group_member
                where    [Group] = @GroupNoDomain;

                -- Get the next cursor row
                fetch next from group_cursor
                into     @GroupUserName
                    , @Role

            end

        close group_cursor
        deallocate group_cursor

        -- Get the next cursor row
        fetch next from report_cursor
        into     @FolderName
            , @ReportName
            , @ReportPath

    end

close report_cursor
deallocate report_cursor

select    *
from    #allperms
order by ReportName;

go