CODEWITHSUNDEEP
sql-server
Roger Far
Roger Far

Reputation: 2385

Checking if current user has db_backupoperator role / permissions

For our backup software I'm trying to add a check if the user has enough permissions to create a backup.

When I create a login Test for a database and give it the db_backupoperator role for that database, how do I check for that role?

I tried:

SELECT name as 'DatabaseName', has_dbaccess(name) as 'HasAccess' FROM sys.databases

This is always 0 for each database (except master, tempdb and msdb).

When I do:

SELECT name as 'DatabaseName', HAS_PERMS_BY_NAME(name, 'DATABASE', 'CREATE BACKUP') as 'HasAccess' FROM sys.databases

It results in NULL for each database.

When I do: SELECT IS_ROLEMEMBER ( 'db_backupoperator')

I get 0 because I probably don't have permissions to view the permission tables even though I know the user has that role assigned.

Any other way?

Upvotes: 1

Views: 1064

Answers (1)

Ben Thul
Ben Thul

Reputation: 32707

Your HAS_PERMS_BY_NAME() approach works for me, but I needed to change the permission name to BACKUP DATABASE. I got the name of the permission from the documentation for database-level GRANTs. Note, there's also a BACKUP LOG permission if you need to check that as well.

TL;DR -

SELECT name as 'DatabaseName', 
    HAS_PERMS_BY_NAME(name, 'DATABASE', 'BACKUP DATABASE') as 'HasAccess' 
FROM sys.databases;

As for the IS_ROLEMEMBER() approach, that works for me unchanged. I see in the comments that you had a misrecollection about the name of the actual role. Is it possible that that's an explanation for the behavior that you were seeing?

All that said, I'd personally prefer the HAS_PERMS_BY_NAME() approach. Regardless of how the permission was granted, that will expose whether or not the current user/login has it.

Upvotes: 2

Related Questions