0

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?

2

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!

|improve this answer|||||
  • 1
    Hare Krishna! That's great, from now on I will remember the permissions on master and msdb when referring to server side permissions – Marcello Miorelli Jul 11 '19 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.