主页 / dba / 问题 / 340720
Accepted
Marcello Miorelli
Asked: 2024-07-04 19:47:33 +0800 CST

将权限脚本重新应用到原始数据库时出错 - sql server

  • 772

当我运行以下脚本时：

-- ——— SCRIPT GRANTS for Object Privileges—————

IF OBJECT_ID('[sys].[sysrowsets]') IS NOT NULL   GRANT CONTROL on [sys].[sysrowsets] to [db_myrole_BA]

我收到此错误信息：

Msg 15151, Level 16, State 1, Line 271
Cannot find the object 'sysrowsets', because it does not exist or you do not have permission.

然而：

当我运行这个时：

select radhe=OBJECT_ID('[sys].[sysrowsets]')

在此处输入图片描述

我的原始脚本来自这里：

-- SCRIPT GRANTS for Objects Level Privilegs
PRINT '-- ——— SCRIPT GRANTS for Object Privileges—————'
SELECT
[GRANTS for Object Privileges]='IF OBJECT_ID(''['+ sys.schemas.name + '].[' + sys.objects.name + ']''' + ') IS NOT NULL ' + CHAR(13) + SPACE(1) +
state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE SQL_Latin1_General_CP1_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id = 
sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id = 
sys.database_principals.principal_id
where sys.database_principals.name not in ( 'public', 'guest')
--order by 1, 2, 3, 5

我怎么可能会收到这个错误？

我想保存所有这些权限，以便当我恢复数据库时，我可以重新应用这些权限。

sql-server

2 个回答

  1. Best Answer

    您需要使用排除内部表is_ms_shipped = 0。

    • 您还需要使用 正确引用名称QUOTENAME。
    • 你需要 WITH GRANT OPTION正确处理。
    • 您可能想要添加 AS SomeGrantor，以便权限与正确的原始授予者链接在一起。
    SELECT
  [GRANTS for Object Privileges] =
    'IF OBJECT_ID(' COLLATE DATABASE_DEFAULT + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ') IS NOT NULL
  ' + IIF(perm.state = 'W', 'GRANT', perm.state_desc) + ' '
    + perm.permission_name + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name
    + ' TO ' + QUOTENAME(dp.name)
    + IIF(perm.state = 'W', ' WITH GRANT OPTION', '')
    + ISNULL(' AS ' + QUOTENAME(grantor.name), '')
from sys.database_permissions perm
join sys.objects on perm.major_id = o.object_id
join sys.schemas s on o.schema_id = s.schema_id
join sys.database_principals dp on perm.grantee_principal_id = dp.principal_id
left join sys.database_principals grantor on perm.grantor_principal_id = grantor.principal_id
where dp.name COLLATE DATABASE_DEFAULT not in ('public', 'guest')
  and o.is_ms_shipped = 0;
    • 2

  2. 我将接受Charlieface 的答案，因为它非常好。

    我在这里发帖的唯一原因是因为他的查询在我的环境中不起作用，主要是由于 COLLATE 问题，我喜欢并使用这个网站作为参考，但正如我所说，他的答案是肯定的，我表示感谢。

                            print @@servername + '.' + db_name()
                        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
                        SET NOCOUNT ON



              SELECT [GRANTS for Object Privileges] =
    'IF OBJECT_ID(' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ') IS NOT NULL ' COLLATE SQL_Latin1_General_CP1_CI_AS 
    + IIF(perm.state = 'W', 'GRANT', perm.state_desc) + ' '
    + perm.permission_name + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
    + ' TO ' + QUOTENAME(dp.name)
    + IIF(perm.state = 'W', ' WITH GRANT OPTION', '')
    + ISNULL(' AS ' + QUOTENAME(grantor.name), '')
                 FROM sys.database_permissions perm         WITH(NOLOCK)
           INNER JOIN  sys.objects o                        WITH(NOLOCK) ON perm.major_id             = o.object_id
           INNER JOIN  sys.schemas s                        WITH(NOLOCK) ON    o.schema_id            = s.schema_id
           INNER JOIN  sys.database_principals dp           WITH(NOLOCK) ON perm.grantee_principal_id = dp.principal_id
      LEFT OUTER JOIN sys.database_principals grantor       WITH(NOLOCK) ON perm.grantor_principal_id = grantor.principal_id
                WHERE dp.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN ('public', 'guest') 
                  AND o.is_ms_shipped = 0

    另外， sepupic在评论中提到，“无效的对象名称 sys.sysrowsets”非常有用，推荐 DAC：

    exec sp_configure 'remote admin connections', 1
go
reconfigure
go
    • 0

相关问题