how to script server side permissions - specifically EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER?

Question

I could not script the following server side permissions:

GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe]

but thanks to the question below I found a solution to that:

How to script out the grant view on login permission?

Now I have a similar problem:

I have a process that grant the following server side permission to a login:

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [my_company\my_login]

that is working fine, however, I need to script all the permissions of the my_company\my_login but I cannot find a way to script this permission.

How can I do that?

Answer 1

Unlike sys.server_principals and sys.database_permissions holds information about server level access/permissions, information similar to that sys.database_principals' andsys.database_permission` holds information about database level access/permissions.

Joining these two objects with UNION of sys.objects and sys.sysobjects will give you desired information about user defined and system objects, which can be converted into script.

Note: To generate script for object level permission we need to execute the script in respective database. Like sys.xp_prop_oledb_provide is an object of master database, so we'll execute the SELECT statement in master database.

Example:-

CREATE LOGIN Radhe WITH PASSWORD='HareKrishna001!', DEFAULT_DATABASE=master;
GO

CREATE USER Radhe FOR LOGIN Radhe;
GO

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [Radhe];
GO

USE master;
GO
select
        case when DBPerm.state_desc='GRANT_WITH_GRANT_OPTION' then 'GRANT' else DBPerm.state_desc end COLLATE SQL_Latin1_General_CP1_CI_AS+
        ' '+DBPerm.permission_name  COLLATE SQL_Latin1_General_CP1_CI_AS+' ON '+o.objectschema+'.'+o.name+' TO ['+DBPrin.name+']'+
         case when DBPerm.state_desc='GRANT_WITH_GRANT_OPTION' then 'WITH GRANT OPTION;' else ';' end COLLATE SQL_Latin1_General_CP1_CI_AS AS [Script]
from
            sys.database_permissions DBPerm
inner join  (
            select name,object_id,schema_name(schema_id) objectschema from sys.objects
            union all
            select name,id,'sys' as objectschema from sys.sysobjects
            ) o
on          DBPerm.major_id=o.object_id
inner join  sys.database_principals DBPrin
on          DBPerm.grantee_principal_id=DBPrin.principal_id
where
            DBPrin.name NOT LIKE '##%##'
        AND DBPrin.name NOT LIKE 'NT AUTHORITY%'
        AND DBPrin.name NOT LIKE 'NT SERVICE%'
        AND DBPrin.name not in ('sa','public');

Results to:

Script
-------------------------------------------------------------
GRANT EXECUTE ON sys.xp_prop_oledb_provider TO [Radhe]

Hare Krishna!