Permissions of Fixed Database Roles

Hopefully you've found your way here for the right reasons. You're the type of developer or DBA that doesn't blindly create all SQL Server logins with membership in the [sysadmin] fixed server role. And you don't normally give database users membership in the [db_owner] fixed database role, either. Right? RIGHT??? Ok, then. Let's continue...

Not too long ago, I was working with a software vendor that was receptive to my suggestions for the SQL security model of their app. The vendor had been using a database user with membership in [db_owner]. I was thinking one way that might make it easier for the vendor to make the switch off of [db_owner] was to send them a list of all the permissions that role has by default. They could review the list and "cherry pick" the permissions their app needed. From there, it should be simple to script out a bunch of GRANT permissions *This could possibly be pared down by adding the database user to another, more restrictive fixed database role. MSDN and Technet documentation was a little sparse. I found good descriptions of each database role and the permissions conveyed to it. But I didn't see an exhaustive list. Hmm. Perhaps it's there and I just can't find it. No matter. I'll figure it out with sys.fn_my_permissions.

The script below has been working well for me. And I think it works on any version of SQL Server starting with SQL 2005. I've found it to be more useful than maintaining a version-specific static list. Don't run the script all at once. Start by creating the temp user, then run each block of statements one at a time--one block for each fixed database role. I'm sure some of the database role permissions are pretty obvious ([db_datareader] and [db_datawriter]). Others, not so much.

USE tempdb
GO

--Create a temporary database user.
IF NOT EXISTS (
    SELECT *
    FROM sys.database_principals  p
    WHERE p.name = 'TempUser'
)
BEGIN
    CREATE USER TempUser
    WITHOUT LOGIN
END
GO

--View default permissions that correspond to [public]
EXECUTE AS USER = 'TempUser';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') 
ORDER BY subentity_name, permission_name ;  
REVERT;
GO

EXEC sp_addrolemember N'db_accessadmin', N'TempUser';
EXECUTE AS USER = 'TempUser';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') 
ORDER BY subentity_name, permission_name ;  
REVERT;
EXEC sp_droprolemember N'db_accessadmin', N'TempUser';
GO

EXEC sp_addrolemember N'db_backupoperator', N'TempUser';
EXECUTE AS USER = 'TempUser';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') 
ORDER BY subentity_name, permission_name ;  
REVERT;
EXEC sp_droprolemember N'db_backupoperator', N'TempUser';
GO

EXEC sp_addrolemember N'db_datareader', N'TempUser';
EXECUTE AS USER = 'TempUser';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') 
ORDER BY subentity_name, permission_name ;  
REVERT;
EXEC sp_droprolemember N'db_datareader', N'TempUser';
GO

EXEC sp_addrolemember N'db_datawriter', N'TempUser';
EXECUTE AS USER = 'TempUser';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') 
ORDER BY subentity_name, permission_name ;  
REVERT;
EXEC sp_droprolemember N'db_datawriter', N'TempUser';
GO

EXEC sp_addrolemember N'db_ddladmin', N'TempUser';
EXECUTE AS USER = 'TempUser';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') 
ORDER BY subentity_name, permission_name ;  
REVERT;
EXEC sp_droprolemember N'db_ddladmin', N'TempUser';
GO

EXEC sp_addrolemember N'db_denydatareader', N'TempUser';
EXECUTE AS USER = 'TempUser';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') 
ORDER BY subentity_name, permission_name ;  
REVERT;
EXEC sp_droprolemember N'db_denydatareader', N'TempUser';
GO

EXEC sp_addrolemember N'db_denydatawriter', N'TempUser';
EXECUTE AS USER = 'TempUser';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') 
ORDER BY subentity_name, permission_name ;  
REVERT;
EXEC sp_droprolemember N'db_denydatawriter', N'TempUser';
GO

EXEC sp_addrolemember N'db_owner', N'TempUser';
EXECUTE AS USER = 'TempUser';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') 
ORDER BY subentity_name, permission_name ;  
REVERT;
EXEC sp_droprolemember N'db_owner', N'TempUser';
GO

EXEC sp_addrolemember N'db_securityadmin', N'TempUser';
EXECUTE AS USER = 'TempUser';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') 
ORDER BY subentity_name, permission_name ;  
REVERT;
EXEC sp_droprolemember N'db_securityadmin', N'TempUser';
GO

--Cleanup
IF EXISTS (
    SELECT *
    FROM sys.database_principals  p
    WHERE p.name = 'TempUser'
)
BEGIN
    DROP USER TempUser
END
GO