Does dbcreator Grant Backup Privileges?

Words: 581

Time to read: ~ 3 minutes

TL;DR: No.

This is a straight #SQLNewBlogger post.

I was working on privileges and permissions for some server roles in the database on a new server the other day.

From talking with the Developers, they wanted the ability to be able to backup and restore databases.

Reading the documentation

I knew from reading the documentation that dbcreator grants permissions to create, alter, drop, and restore databases. My question was does it give permission to backup databases?

It seems to give everything else so is backup databases just missing there? Or is it intentionally left out?

A few tries with different search engine didn’t reveal much for me. Luckily, this is something that we can easily test! Perfect for a #SQLNewBlogger post.

If you have a situation like this one; figure it out and write it up!

Set Up

I’m going to assume that you have a development instance of SQL Server to test things out on.

If not, Grant Fritchey ( blog | twitter ) is currently doing a great series on getting started with Docker.

And thank you Andy Mallon ( blog | twitter ) for the Data Blogger Resource that lets me copy and paste the information over!

On your test instance, we’re going to create a SQL Authentication Login that shall be our test subject.

We are testing dbcreator so we’re going to add it to that role after we create it. We’ll also need a database that we can test this on.

USE [master];
GO

CREATE LOGIN [RestoreNoBackup] WITH PASSWORD = 'WhyOneAndNotOther?';
GO

ALTER SERVER ROLE dbcreator ADD MEMBER RestoreNoBackup;
GO

IF DB_ID(N'TestBackup') IS NOT NULL BEGIN
    ALTER DATABASE TestBackup SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TestBackup;
END;
GO

CREATE DATABASE TestBackup;
ALTER DATABASE TestBackup SET RECOVERY SIMPLE;
GO

USE TestBackup;
GO

CREATE TABLE dbo.TestData (
    TestDataId int IDENTITY(1, 1) NOT NULL
        CONSTRAINT pk_TestData PRIMARY KEY,
    TestDate date NOT NULL
        CONSTRAINT df_TestDate_getdate DEFAULT (GETDATE()),
    TestValue varchar(25) NOT NULL
);
GO

CREATE USER RestoreNoBackup FROM LOGIN RestoreNoBackup;
GO
GRANT SELECT ON dbo.TestData TO RestoreNoBackup;
GO

INSERT INTO dbo.TestData (TestValue)
VALUES
    ('This is important!'),
    ('So is this.');
GO

SELECT * FROM dbo.TestData;
GO

So now we have:

• A database: TestBackup
• A SQL Login: RestoreNoBackup
  • Assigned to dbcreator
• A User on the database linked to the login: RestoreNoBackup
  • Granted SELECT on the table.

Are we missing something? Let’s take a backup as well.

BACKUP DATABASE TestBackup
TO DISK = N'E:\BACKUP\TestBackup_20190530_01.bak'
WITH
    COMPRESSION,
    CHECKSUM;
GO

Now, let’s do our actual test.

Quick Test.

We’re going to connect to the instance as our login; RestoreNoBackup.

Now, let’s test that we can create a database…

CREATE DATABASE Empty01;
GO

SELECT
    DbOwner = SUSER_SNAME(owner_sid),
    DbName  = [name]
FROM sys.databases;
GO

Lovely, can we restore the backup that we took?…

RESTORE DATABASE RestoreTest01
FROM DISK = N'E:\BACKUP\TestBackup_20190530_01.bak'
WITH
    MOVE N'TestBackup' TO N'/var/opt/mssql/data/RestoreTest01.mdf',
    MOVE N'TestBackup_log' TO N'/var/opt/mssql/data/RestoreTest01_log.ldf',
    CHECKSUM,
    STATS = 10;

SELECT * FROM [RestoreTest01].dbo.TestData;

SELECT
    DbOwner = SUSER_SNAME(owner_sid),
    DbName  = [name]
FROM sys.databases;
GO

Great! But can we backup the database that we created?…

SELECT SUSER_SNAME();
BACKUP DATABASE RestoreTest01
TO DISK = N'E:\BACKUP\RestoreTest01_20190530_01.bak'
WITH
    COMPRESSION,
    CHECKSUM;
GO

Okay! So if backup permissions are needed, I need to add the user to the db_backupoperator database role.

Good to know!

#SQLNewBlogger

Great!

I now have confirmation that dbcreator can create and restore (I’m wasn’t testing alter or drop) databases.

This is a lovely case of #SQLNewBlogger, it took me 15 minutes for this blog post and most of that was taking the screenshots and cleaning the code.