2012-05-25

Describe database role

When you script out a database role in SQL Server Management Studio (SSMS) or another tool like Red-Gate SQL Compare, you most likely will get just the creation but the rights.
Yesterday I needed the rights for a database role on tables and other database objects. This I got by a small T-SQL script:

:Setvar _RoleName "sqlanchor_user"

DECLARE @script nvarchar(MAX) = N'USE [' + DB_NAME() + N'];' + CHAR(13) + N'GO' + CHAR(13);

SELECT @script += N'CREATE ROLE [' + [db_role].[name] + N'] AUTHORIZATION [' + [owner].[name] + N'];' + CHAR(13)
FROM [sys].[database_principals] AS [db_role]
INNER JOIN [sys].[database_principals] AS [owner]
  ON [db_role].[owning_principal_id] = [owner].[principal_id]
WHERE [db_role].[type] = N'R' AND [db_role].[name] = N'$(_RoleName)';

-- Database object rights
SELECT @script += 'GRANT ' + [db_perm].[permission_name] + ' ON [' + USER_NAME(OBJECTPROPERTY([db_perm].[major_id], 'OwnerId')) + '].[' + OBJECT_NAME([db_perm].[major_id]) + '] TO [' + [db_role].[name] + N'];' + CHAR(13) COLLATE Latin1_General_CI_AS
FROM [sys].[database_permissions] AS [db_perm]
INNER JOIN [sys].[database_principals] AS [db_role]
  ON [db_perm].[grantee_principal_id] = [db_role].[principal_id]
WHERE db_perm.[class] = 1  -- class 1 : Db Object or Column
  AND [db_role].[name] = N'$(_RoleName)';

PRINT @script;


The script only show how to create the database role and assign rights on database objects.
Rights on schemas for example is not by this script. Consider the script as a spike solution, that can be extended.
Take a look in the documentation for "sys.database_permissions" on the possibilities.

No comments: