error when re-applying the permissions script into the original database - sql server

Question

when I run the following script:

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

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

I get this error message:

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

However:

when I run this:

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

My original script comes from here:

-- 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

How is it possible I am getting that error?

I want to save all these permissions, so that when I restore the database - I can re-apply those permissions.

Answer 1

You need to exclude internal tables using is_ms_shipped = 0.

• You also need to quote names correctly using QUOTENAME.
• You need to handle WITH GRANT OPTION correctly.
• You probably want to add AS SomeGrantor so that the permissions are chained from the correct original grantor.

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;

Answer 2

Charlieface's anwer is the one I am going to accept, it is very good.

The only reason I am posting here, it is because his query did not work on my environment mostly due to COLLATE issues,and I love and use this site as a reference, but as I said his is the answer, with thanks from me.

                        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

also from the comments sepupic mentions that "Invalid object name sys.sysrowsets" very useful, recommending DAC:

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