Я хочу скопировать все разрешения, которые я установил на хранимые процедуры и другие материалы из моей базы данных разработки, в мою производственную базу данных. Это невероятно громоздко, не говоря уже о склонности к ошибкам, сделать это вручную вручную с помощью инструмента SSMS GUI.
Итак, я ищу способ, которым я могу напрямую сбрасывать разрешения, установленные в одной базе данных, и применять те же разрешения к отдельной базе данных (предположительно, имея ту же схему).
Представления встроенных каталогов базы данных предоставляют информацию для этого. Попробуйте этот запрос:
SELECT
(
dp.state_desc + ' ' +
dp.permission_name collate latin1_general_cs_as +
' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
' TO ' + '[' + dpr.name + ']'
) AS GRANT_STMT
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name NOT IN ('public','guest')
-- AND o.name IN ('My_Procedure') -- Uncomment to filter to specific object(s)
-- AND dp.permission_name='EXECUTE' -- Uncomment to filter to just the EXECUTEs
Это приведет к выпуску группы команд (GRANT/DENY) для каждого из разрешений в базе данных. Из этого вы можете скопировать и вставить их в другое окно запроса и выполнить, чтобы создать те же разрешения, которые были установлены на оригинале. Например:
GRANT EXECUTE ON [Exposed].[EmployeePunchoutReservationRetrieve] TO [CustomerAgentRole]
GRANT EXECUTE ON [Exposed].[EmployeePunchoutReservationStore] TO [CustomerAgentRole]
GRANT EXECUTE ON [Exposed].[EmployeePunchoutSendOrderLogStore] TO [CustomerAgentRole]
GRANT EXECUTE ON [Exposed].[EmployeeReportSubscriptions] TO [CustomerAgentRole]
Обратите внимание на нижнюю строку, прокомментированную, что фильтрация на имя_домена. Не комментируя эту строку, запрос будет вызывать только разрешения EXECUTE (т.е. Для хранимых процедур).
Вы можете заставить SQL Server Management Studio сделать это за вас:
Это создаст script для установки разрешений для всех выбранных объектов, но подавляет сами скрипты объектов.
Это основано на диалоговом окне для MS SQL 2008 со всеми другими параметрами сценариев без изменений по умолчанию.
Да, вы можете использовать script, чтобы создать еще один script
SET NOCOUNT ON;
DECLARE @NewRole varchar(100), @SourceRole varchar(100);
-- Change as needed
SELECT @SourceRole = 'Giver', @NewRole = 'Taker';
SELECT
state_desc + ' ' + permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + @NewRole
FROM
sys.database_permissions
WHERE
grantee_principal_id = DATABASE_PRINCIPAL_ID(@SourceRole) AND
-- 0 = DB, 1 = object/column, 3 = schema. 1 is normally enough
class <= 3
Расширяя ответ, предоставленный на fooobar.com/questions/172519/... который не подходит для прав доступа к базе данных/схеме и для типов пользователей базы данных, которые вы можете использовать:
SELECT
CASE
WHEN dp.class_desc = 'OBJECT_OR_COLUMN' THEN
dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +
' ON ' + '[' + obj_sch.name + ']' + '.' + '[' + o.name + ']' +
' TO ' + '[' + dpr.name + ']'
WHEN dp.class_desc = 'DATABASE' THEN
dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +
' TO ' + '[' + dpr.name + ']'
WHEN dp.class_desc = 'SCHEMA' THEN
dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +
' ON SCHEMA :: ' + '[' + SCHEMA_NAME(dp.major_id) + ']' +
' TO ' + '[' + dpr.name + ']'
WHEN dp.class_desc = 'TYPE' THEN
dp.state_desc + ' ' + dp.permission_name collate Latin1_General_CS_AS +
' ON TYPE :: [' + s_types.name + '].[' + t.name + ']'
+ ' TO [' + dpr.name + ']'
WHEN dp.class_desc = 'CERTIFICATE' THEN
dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +
' TO ' + '[' + dpr.name + ']'
WHEN dp.class_desc = 'SYMMETRIC_KEYS' THEN
dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +
' TO ' + '[' + dpr.name + ']'
ELSE
'ERROR: Unhandled class_desc: ' + dp.class_desc
END
AS GRANT_STMT
FROM sys.database_permissions AS dp
JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
LEFT JOIN sys.objects AS o ON dp.major_id=o.object_id
LEFT JOIN sys.schemas AS obj_sch ON o.schema_id = obj_sch.schema_id
LEFT JOIN sys.types AS t ON dp.major_id = t.user_type_id
LEFT JOIN sys.schemas AS s_types ON t.schema_id = s_types.schema_id
WHERE
dpr.name NOT IN ('public','guest')
-- AND o.name IN ('My_Procedure') -- Uncomment to filter to specific object(s)
-- AND (o.name NOT IN ('My_Procedure') or o.name is null) -- Uncomment to filter out specific object(s), but include rows with no o.name (VIEW DEFINITION etc.)
-- AND dp.permission_name='EXECUTE' -- Uncomment to filter to just the EXECUTEs
-- AND dpr.name LIKE '%user_name%' -- Uncomment to filter to just matching users
ORDER BY dpr.name, dp.class_desc, dp.permission_name
Благодаря Крису за его удивительный ответ я сделал это еще на один шаг и автоматизировал процесс выполнения этих утверждений (у моей таблицы было более 8000 разрешений)
if object_id('dbo.tempPermissions') is not null
Drop table dbo.tempPermissions
Create table tempPermissions(ID int identity , Queries Varchar(255))
Insert into tempPermissions(Queries)
select 'GRANT ' + dp.permission_name collate latin1_general_cs_as
+ ' ON ' + s.name + '.' + o.name + ' TO ' + dpr.name
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name NOT IN ('public','guest')
declare @count int, @max int, @query Varchar(255)
set @count =1
set @max = (Select max(ID) from tempPermissions)
set @query = (Select Queries from tempPermissions where ID = @count)
while(@count < @max)
begin
exec(@query)
set @count += 1
set @query = (Select Queries from tempPermissions where ID = @count)
end
select * from tempPermissions
drop table tempPermissions
дополнительно для ограничения его на одну таблицу:
and o.name = 'tablename'
после WHERE dpr.name NOT IN ('public', 'guest') и не забудьте отредактировать оператор select, чтобы он генерировал операторы для таблицы, в которой вы хотите предоставить разрешения "TO". Не та таблица, на которую идут разрешения "FROM" (что делает script).
ВЫ МОЖЕТЕ СКАЧАТЬ СОДЕРЖАНИЕ КОДА В НИЖЕ ЛИНИИ И ВИДЕТЬ КАК ЭТО РАБОТАЕТ
https://gallery.technet.microsoft.com/Extract-Database-dfa53d5a
ЭТО КАК ВЫ СМОЖЕТЕ ВЫВОДЫ ЭТОГО ЧАСА
set nocount off
IF OBJECT_ID(N'tempdb..##temp1') IS NOT NULL
DROP TABLE ##temp1
create table ##temp1(query varchar(1000))
insert into ##temp1
select 'use '+db_name() +';'
insert into ##temp1
select 'go'
/*creating database roles*/
insert into ##temp1
select 'if DATABASE_PRINCIPAL_ID('''+name+''') is null
exec sp_addrole '''+name+'''' from sysusers
where issqlrole = 1 and (sid is not null and sid <> 0x0)
/*creating application roles*/
insert into ##temp1
select 'if DATABASE_PRINCIPAL_ID('+char(39)+name+char(39)+')
is null CREATE APPLICATION ROLE ['+name+'] WITH DEFAULT_SCHEMA = ['+
default_schema_name+'], Password='+char(39)+'Pass$w0rd123'+char(39)+' ;'
from sys.database_principals
where type_desc='APPLICATION_ROLE'
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' to '+'['+USER_NAME(grantee_principal_id)+']'+' WITH GRANT OPTION ;'
else
state_desc+' '+permission_name+' to '+'['+USER_NAME(grantee_principal_id)+']'+' ;'
END
from sys.database_permissions
where class=0 and USER_NAME(grantee_principal_id) not in ('dbo','guest','sys','information_schema')
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' on '+OBJECT_SCHEMA_NAME(major_id)+'.['+OBJECT_NAME(major_id)
+'] to '+'['+USER_NAME(grantee_principal_id)+']'+' with grant option ;'
else
state_desc+' '+permission_name+' on '+OBJECT_SCHEMA_NAME(major_id)+'.['+OBJECT_NAME(major_id)
+'] to '+'['+USER_NAME(grantee_principal_id)+']'+' ;'
end
from sys.database_permissions where class=1 and USER_NAME(grantee_principal_id) not in ('public');
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON schema::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON schema::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.schemas sa on
sa.schema_id = dp.major_id where dp.class=3
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON APPLICATION ROLE::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON APPLICATION ROLE::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.database_principals sa on
sa.principal_id = dp.major_id where dp.class=4 and sa.type='A'
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON ROLE::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON ROLE::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join
sys.database_principals sa on sa.principal_id = dp.major_id
where dp.class=4 and sa.type='R'
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON ASSEMBLY::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON ASSEMBLY::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.assemblies sa on
sa.assembly_id = dp.major_id
where dp.class=5
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON type::['
+SCHEMA_NAME(schema_id)+'].['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON type::['
+SCHEMA_NAME(schema_id)+'].['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.types sa on
sa.user_type_id = dp.major_id
where dp.class=6
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON XML SCHEMA COLLECTION::['+
SCHEMA_NAME(SCHEMA_ID)+'].['+sa.name+'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON XML SCHEMA COLLECTION::['+
SCHEMA_NAME(SCHEMA_ID)+'].['+sa.name+'] to ['+user_name(dp.grantee_principal_id)+'];'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.xml_schema_collections sa on
sa.xml_collection_id = dp.major_id
where dp.class=10
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON message type::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON message type::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.service_message_types sa on
sa.message_type_id = dp.major_id
where dp.class=15
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON contract::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON contract::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.service_contracts sa on
sa.service_contract_id = dp.major_id
where dp.class=16
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON SERVICE::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON SERVICE::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.services sa on
sa.service_id = dp.major_id
where dp.class=17
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON REMOTE SERVICE BINDING::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON REMOTE SERVICE BINDING::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.remote_service_bindings sa on
sa.remote_service_binding_id = dp.major_id
where dp.class=18
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON route::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON route::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.routes sa on
sa.route_id = dp.major_id
where dp.class=19
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON FULLTEXT CATALOG::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON FULLTEXT CATALOG::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.fulltext_catalogs sa on
sa.fulltext_catalog_id = dp.major_id
where dp.class=23
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON SYMMETRIC KEY::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON SYMMETRIC KEY::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.symmetric_keys sa on
sa.symmetric_key_id = dp.major_id
where dp.class=24
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON certificate::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON certificate::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.certificates sa on
sa.certificate_id = dp.major_id
where dp.class=25
insert into ##temp1
select
case
when state_desc='GRANT_WITH_GRANT_OPTION'
then
substring (state_desc,0,6)+' '+permission_name+' ON ASYMMETRIC KEY::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
else
state_desc+' '+permission_name+' ON ASYMMETRIC KEY::['+sa.name+
'] to ['+user_name(dp.grantee_principal_id)+'] ;'
COLLATE LATIN1_General_CI_AS
end
from sys.database_permissions dp inner join sys.asymmetric_keys sa on
sa.asymmetric_key_id = dp.major_id
where dp.class=26
insert into ##temp1
select 'exec sp_addrolemember ''' +p.NAME+''','+'['+m.NAME+']'+' ;'
FROM sys.database_role_members rm
JOIN sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id
where m.name not like 'dbo';
select * from ##temp1
Наша версия:
SET NOCOUNT ON
DECLARE @message NVARCHAR(MAX)
-- GENERATE LOGINS CREATE SCRIPT
USE [master]
-- creating accessory procedure
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'sp_hexadecimal') AND type IN ( N'P', N'PC' ))
DROP PROCEDURE [dbo].[sp_hexadecimal]
EXEC('
CREATE PROCEDURE [dbo].[sp_hexadecimal]
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ''0x''
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ''0123456789ABCDEF''
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue')
SET @message = '-- CREATE LOGINS' + CHAR(13) + CHAR(13) +'USE [master]' + CHAR(13)
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr NVARCHAR(MAX)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
END
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = ''' + @name + ''' AND dbname = ''' + @defaultdb + ''')' + CHAR(13) +
'BEGIN TRY' + CHAR(13) +
' CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = ''' + @name + ''' AND dbname = ''' + @defaultdb + ''')' + CHAR(13) +
'BEGIN TRY' + CHAR(13) +
' CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
SET @tmpstr = @tmpstr + CHAR(13) + 'END TRY' + CHAR(13) + 'BEGIN CATCH' + CHAR(13) + 'END CATCH'
SET @message = @message + CHAR(13) + @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
--removing accessory procedure
DROP PROCEDURE [dbo].[sp_hexadecimal]
-- GENERATE SERVER PERMISSIONS
USE [master]
DECLARE @ServerPrincipal SYSNAME
DECLARE @PrincipalType SYSNAME
DECLARE @PermissionName SYSNAME
DECLARE @StateDesc SYSNAME
SET @message = @message + CHAR(13) + CHAR(13) + '-- CREATE SERVER PERMISSIONS' + CHAR(13) + CHAR(13) +'USE [master]' + CHAR(13)
DECLARE server_permissions_curs CURSOR FOR
SELECT
[srvprin].[name] [server_principal],
[srvprin].[type_desc] [principal_type],
[srvperm].[permission_name],
[srvperm].[state_desc]
FROM [sys].[server_permissions] srvperm
INNER JOIN [sys].[server_principals] srvprin
ON [srvperm].[grantee_principal_id] = [srvprin].[principal_id]
WHERE [srvprin].[type] IN ('S', 'U', 'G') AND [srvprin].name NOT IN ('sa', 'dbo', 'information_schema', 'sys')
ORDER BY [server_principal], [permission_name];
OPEN server_permissions_curs
FETCH NEXT FROM server_permissions_curs INTO @ServerPrincipal, @PrincipalType, @PermissionName, @StateDesc
WHILE (@@fetch_status <> -1)
BEGIN
SET @message = @message + CHAR(13) + 'BEGIN TRY' + CHAR(13) +
@StateDesc + N' ' + @PermissionName + N' TO ' + QUOTENAME(@ServerPrincipal) +
+ CHAR(13) + 'END TRY' + CHAR(13) + 'BEGIN CATCH' + CHAR(13) + 'END CATCH'
FETCH NEXT FROM server_permissions_curs INTO @ServerPrincipal, @PrincipalType, @PermissionName, @StateDesc
END
CLOSE server_permissions_curs
DEALLOCATE server_permissions_curs
--GENERATE USERS AND PERMISSION SCRIPT FOR EVERY DATABASE
SET @message = @message + CHAR(13) + CHAR(13) + N'--ENUMERATE DATABASES'
DECLARE @databases TABLE (
DatabaseName SYSNAME,
DatabaseSize INT,
Remarks SYSNAME NULL
)
INSERT INTO
@databases EXEC sp_databases
DECLARE @DatabaseName SYSNAME
DECLARE database_curs CURSOR FOR
SELECT DatabaseName FROM @databases WHERE DatabaseName IN (N'${DatabaseName}')
OPEN database_curs
FETCH NEXT FROM database_curs INTO @DatabaseName
WHILE (@@fetch_status <> -1)
BEGIN
SET @tmpStr =
N'USE ' + QUOTENAME(@DatabaseName) + '
DECLARE @tmpstr NVARCHAR(MAX)
SET @messageOut = CHAR(13) + CHAR(13) + ''USE ' + QUOTENAME(@DatabaseName) + ''' + CHAR(13)
-- GENERATE USERS SCRIPT
SET @messageOut = @messageOut + CHAR(13) + ''-- CREATE USERS '' + CHAR(13)
DECLARE @users TABLE (
UserName SYSNAME Null,
RoleName SYSNAME Null,
LoginName SYSNAME Null,
DefDBName SYSNAME Null,
DefSchemaName SYSNAME Null,
UserID INT Null,
[SID] varbinary(85) Null
)
INSERT INTO
@users EXEC sp_helpuser
DECLARE @UserName SYSNAME
DECLARE @LoginName SYSNAME
DECLARE @DefSchemaName SYSNAME
DECLARE user_curs CURSOR FOR
SELECT UserName, LoginName, DefSchemaName FROM @users
OPEN user_curs
FETCH NEXT FROM user_curs INTO @UserName, @LoginName, @DefSchemaName
WHILE (@@fetch_status <> -1)
BEGIN
SET @messageOut = @messageOut + CHAR(13) +
''IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''''''+ @UserName +'''''')''
+ CHAR(13) + ''BEGIN TRY'' + CHAR(13) +
'' CREATE USER '' + QUOTENAME(@UserName)
IF (@LoginName IS NOT NULL)
SET @messageOut = @messageOut + '' FOR LOGIN '' + QUOTENAME(@LoginName)
ELSE
SET @messageOut = @messageOut + '' WITHOUT LOGIN''
IF (@DefSchemaName IS NOT NULL)
SET @messageOut = @messageOut + '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(@DefSchemaName)
SET @messageOut = @messageOut + CHAR(13) + ''END TRY'' + CHAR(13) + ''BEGIN CATCH'' + CHAR(13) + ''END CATCH''
FETCH NEXT FROM user_curs INTO @UserName, @LoginName, @DefSchemaName
END
CLOSE user_curs
DEALLOCATE user_curs
-- GENERATE ROLES
SET @messageOut = @messageOut + CHAR(13) + CHAR(13) + ''-- CREATE ROLES '' + CHAR(13)
SELECT @messageOut = @messageOut + CHAR(13) + ''BEGIN TRY'' + CHAR(13) +
N''EXEC sp_addrolemember N''''''+ rp.name +'''''', N''''''+ mp.name +''''''''
+ CHAR(13) + ''END TRY'' + CHAR(13) + ''BEGIN CATCH'' + CHAR(13) + ''END CATCH''
FROM sys.database_role_members drm
join sys.database_principals rp ON (drm.role_principal_id = rp.principal_id)
join sys.database_principals mp ON (drm.member_principal_id = mp.principal_id)
WHERE mp.name NOT IN (N''dbo'')
-- GENERATE PERMISSIONS
SET @messageOut = @messageOut + CHAR(13) + CHAR(13) + ''-- CREATE PERMISSIONS '' + CHAR(13)
SELECT @messageOut = @messageOut + CHAR(13) + ''BEGIN TRY'' + CHAR(13) +
'' GRANT '' + dp.permission_name collate latin1_general_cs_as +
'' ON '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(o.name) + '' TO '' + QUOTENAME(dpr.name) +
+ CHAR(13) + ''END TRY'' + CHAR(13) + ''BEGIN CATCH'' + CHAR(13) + ''END CATCH''
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name NOT IN (''public'',''guest'')'
EXECUTE sp_executesql @tmpStr, N'@messageOut NVARCHAR(MAX) OUTPUT', @messageOut = @tmpstr OUTPUT
SET @message = @message + @tmpStr
FETCH NEXT FROM database_curs INTO @DatabaseName
END
CLOSE database_curs
DEALLOCATE database_curs
SELECT @message
SELECT
dp.state_desc + ' '
+ dp.permission_name collate latin1_general_cs_as
+ ISNULL((' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name)),'')
+ ' TO ' + QUOTENAME(dpr.name)
FROM sys.database_permissions AS dp
LEFT JOIN sys.objects AS o ON dp.major_id=o.object_id
LEFT JOIN sys.schemas AS s ON o.schema_id = s.schema_id
LEFT JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name NOT IN ('public','guest')
Незначительное изменение принятого ответа, если вы хотите захватить разрешения, которые применяются на уровне базы данных в дополнение к уровню объекта. В основном переключитесь на LEFT JOIN и не забудьте обработать NULL для object и schema names.
declare @DBRoleName varchar(40) = 'yourUserName'
SELECT 'GRANT ' + dbprm.permission_name + ' ON ' + OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id) + ' TO ' + dbrol.name + char(13) COLLATE Latin1_General_CI_AS
from sys.database_permissions dbprm
join sys.database_principals dbrol on
dbprm.grantee_principal_id = dbrol.principal_id
where dbrol.name = @DBRoleName
http://www.sqlserver-dba.com/2014/10/how-to-script-database-role-permissions-and-securables.html
Я нашел это отличным решением для создания script для репликации доступа между средами