SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--- =============================================
--$Create: isa$ $Create date: $
--$Modify: isa$ $Modify date: 28.07.2015$
--$Description: Возвращает права роли $
-- =============================================
CREATE PROCEDURE [dbo].[GetPermissionRole]
@NameRole VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
declare @v1 varchar(100)
declare @v2 varchar(100)
declare @v3 varchar(100)
declare @v4 varchar(100)
declare @v5 varchar(100)
declare @v6 varchar(100)
--DECLARE @NameRole varchar(100) = 'ExecuteAllProcRole'
--DECLARE @NameRole varchar(100) = 'APSelectRole'
DECLARE PermCur CURSOR
STATIC READ_ONLY
FOR
select
sys.database_permissions.STATE_DESC, sys.database_permissions.permission_name ,sys.objects.name AS OBJnAME,sys.database_principals.Name AS ROLENAME ,sys.objects.type_desc
from sys.database_permissions
inner join sys.objects on sys.database_permissions.major_id=sys.objects.object_id
inner join sys.database_principals on sys.database_permissions.grantee_principal_id=sys.database_principals.principal_id
where sys.database_principals.Name = @NameRole
OPEN PermCur
print 'CREATE ROLE ' + @NameRole + ' AUTHORIZATION [dbo]'
FETCH NEXT FROM PermCur
INTO @v1,@v2,@v3,@v4,@v5
WHILE @@FETCH_STATUS = 0
BEGIN
print @v1 + ' ' + @v2 + ' ON [dbo].[' + @v3 + '] TO [' + @v4 + ']'
FETCH NEXT FROM PermCur
INTO @v1,@v2,@v3,@v4,@v5
END
CLOSE PermCur
DEALLOCATE PermCur
END
GO
