How to check if Bulkload permission is assigned to a user in Azure SQL DB

Question

I want to check if any specific user has bulk load permission on Azure SQL DB.

I believe I need to run the following command to assign bulk load permission:-

GRANT ADMINISTER DATABASE BULK OPERATIONS TO userid.

But I want to check if the user already has bulk load permission.

Answer 1

Hi,

You can use the function fn_my_permissions in order to get the permissions of the USER

Here is a full example

-- Connect master
CREATE LOGIN MyBulkLOGIN WITH password='1231!#ASDF!a';
GO

-- Connect database
CREATE USER MyBulkUSER FROM LOGIN MyBulkLOGIN;
GO
-- Check USER permissions before adding any
EXECUTE AS USER = 'MyBulkUSER';  
SELECT entity_name,permission_name FROM fn_my_permissions(NULL, 'DATABASE')
REVERT;
GO -- Only permission to: CONNECT

-- GRANT permissions
GRANT ADMINISTER DATABASE BULK OPERATIONS TO MyBulkUSER
GO
-- Check USER permissions before adding any
EXECUTE AS USER = 'MyBulkUSER';  
SELECT entity_name,permission_name FROM fn_my_permissions(NULL, 'DATABASE')
REVERT;
GO -- Add permission: ADMINISTER DATABASE BULK OPERATIONS