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:
Post a Comment