login – No Column Name

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

But most importantly, he does NOT backup!

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.

Random Blog Challenge: Grant (Fritchey) Permissions to Revoke Permissions.

REVOKE Permissions doesn’t work…but GRANT & DENY does…

Challenge Accepted:

Grant Fritchey ( b | t ), in his infinite wisdom, has created a challenge; a random blog post challenge all to do with the featured image at the top of this post:

Actually, from his blog post, his actual reason was…

for no particular reason at all or maybe because I had too much food at lunch or just because Jason Hall gave me the idea

Now to me, that is a cheeky response, which is fitting because this seems like a cheeky gif… as in it is saying “oh hey!” in response to someone asking “Who did this?!”.

So taking this into account, I’ve decided to document some cheeky behaviour with permissions in SQL Server.

Setting Ourselves Up for Cheekiness:

Let us create a super secret table, with a super secret column containing a super secret value that only we should be able to update.


USE Pantheon;
GO

CREATE TABLE dbo.SuperSecretContent
(
ID int IDENTITY(1, 1)
,SecretColumn varchar(50)
);
GO

INSERT INTO dbo.SuperSecretContent
(SecretColumn)
SELECT RIGHT(NEWID(), 50);
GO

Yeah…I know, my super secret value is a NEWID()…I’m not sorry.

Enter Stage Left:

We also have a random LOGIN and USER that we didn’t create but we somehow have the create scripts for…


USE [master];
GO
CREATE LOGIN [PhysicianWhom] WITH PASSWORD = 'A_Rapid_Hedgehogs_Wrench';
GO

USE [Pantheon];
GO
CREATE USER [PhysicianWhom] FROM LOGIN PhysicianWhom;
GO

Fade To:

One day we check out our super secret value and we see:

SELECT SecretColumn
FROM dbo.SuperSecretContent;
GO

Dammit! He must have access to db_datawriter so he can update all user tables. I’ll do a quick check just to make sure!

SELECT Users.[name] AS UserName
, DBRoles.[name] AS RoleName
FROM sys.database_principals AS Users
INNER JOIN sys.database_role_members AS RoleMembers
ON Users.principal_id = RoleMembers.member_principal_id
INNER JOIN sys.database_principals AS DBRoles
ON DBRoles.principal_id = RoleMembers.role_principal_id
WHERE Users.[name] = N'PhysicianWhom';
GO

Ha! I know how you did it now. You’re not going to be in that database role for long! If he needs update access to other tables then we can grant UPDATE permissions on those tables individually.

Let’s remove him and then refresh our table.

-- Role
ALTER ROLE db_datawriter DROP MEMBER PhysicianWhom;
GO

TRUNCATE TABLE dbo.SuperSecretContent;
GO

INSERT INTO dbo.SuperSecretContent
(SecretColumn)
SELECT RIGHT(NEWID(), 50);
GO

SecondValue — Back to semi-random again!

Happily Ever After?

Time passes and we check our value again:

Arrgh!!! How? Wait, do you have UPDATE permissions on my table?

SELECT Users.[name] AS UserName
, Perms.[permission_name] AS PermissionGranted
, Perms.class_desc AS PermissionOn
, Obj.[name] AS ObjectName
, Perms.state_desc AS PermissionState
FROM sys.database_principals AS Users
INNER JOIN sys.database_permissions AS Perms
ON Perms.grantee_principal_id = Users.principal_id
INNER JOIN sys.objects AS Obj
ON Obj.[object_id] = Perms.major_id
WHERE Users.[name] = N'PhysicianWhom';
GO

This is no one’s fault but my own. Removing them from the db_datawriter role was a shotgun approach, I need to be more precise. So I’m going to DENY him update access to my table and then the problem will be over.

DENY UPDATE ON dbo.SuperSecretContent TO PhysicianWhom;
GO

TRUNCATE TABLE dbo.SuperSecretContent;
GO

INSERT INTO dbo.SuperSecretContent
 (SecretColumn)
SELECT RIGHT(NEWID(), 50);
GO

Paranoia Kicks In:

He can’t have updated it…right?

I mean, he doesn’t have db_datawriter permission; I’ve denied him update permission on my table. I’m safe!…right?

[It’s at this point I went and got a cup of coffee..]

But I denied him! I denied him!

MutuallyExclusive — That…that shouldn’t be possible…

No! He will not win! I am determined…but lost? In my desperation, I scour Books Online and chance upon this little nugget from BOL…

A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for the sake of backward compatibility. It will be removed in a future release.

…and a small ray of light shines through.

I make a quick modification to my permissions script…

SELECT Users.[name] AS UserName
, Perms.[permission_name] AS PermissionGranted
, Perms.class_desc AS PermissionOn
, Obj.[name] AS ObjectName
, Perms.state_desc AS PermissionState
, ( SELECT [name]
 FROM sys.columns AS c
 WHERE c.[object_id] = Obj.[object_id]
 AND c.column_id = Perms.minor_id) AS ColumnPermission
FROM sys.database_principals AS Users
INNER JOIN sys.database_permissions AS Perms
ON Perms.grantee_principal_id = Users.principal_id
INNER JOIN sys.objects AS Obj
ON Obj.[object_id] = Perms.major_id
WHERE Users.[name] = N'PhysicianWhom';
GO

ColumnPermRevealed — Now, I, Zoidberg…I mean Shane…has the upper hand!

Righting A Terrible Wrong

How do I fix this?

A deny is not going to work; we’ve already tried that.

Adding him to db_denydatawriter is not going to work. What happens if he needs to update other tables in our database?

But if we GRANT him access on our entire table then that will overwrite our column level GRANT. Then we can DENY him on our table and all is ~~write~~…I mean right with the world!

GRANT UPDATE ON OBJECT::dbo.SuperSecretContent TO [PhysicianWhom]
DENY UPDATE ON dbo.SuperSecretContent TO PhysicianWhom;
GO

Don’t catch your chickens before they’ve hatched…


EXECUTE AS LOGIN = 'PhysicianWhom';

SELECT USER_NAME() AS WhoooAreYou;

UPDATE dbo.SuperSecretContent
SET SecretColumn = 'Are you listening to me?'
WHERE 1 = 1;
GO

SELECT SecretColumn AS UpdatedTo
FROM dbo.SuperSecretContent;
GO

REVERT;
GO

A Little Parting Gift:

A little present for all you “deprecated means nothing” people out there.

I’m just going to highlight a section here of relevance:

A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for the sake of backward compatibility. It will be removed in a future release.

I aimed to get this blog post out yesterday but when I ran this on…

Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)
Oct 28 2016 18:17:30
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: )

…then my table-level DENY DID take precedence over my column-level GRANT.

When did they introduce that? Was this mentioned and I just missed it? Seriously give it a go and see for yourself!

A Little Parting Gif:

Granting Read Permissions on Everything! T-SQL Tuesday #87 – Fixing Old Problems with Shiny New Toy

Read this and don’t bother me for permissions again…I’m talking to the user, not you.

Intro:

Matt Gordon ( b | t ) has the unfortunate luck of hosting this month’s T-SQL Tuesday. I say “unfortunate” because I don’t think many of the other halfs out there are going to be happy with the last minute writing and checking that bloggers are going to do for this.

He makes up for it though by asking us to think of the positive aspects of SQL Server. What old problems can we fix with the shiny new toys available to us since SQL Server 2014+.

Old Problem:

Have you ever had to give someone uniform access to all the tables in all the databases in your instance of SQL Server, prior to SQL Server 2014?

Now seeing as this is a blog post designed to highlight the benefits of the new way and shine a light on the ugliness of the old way. I’m going to show you how I would do this with the GUI.

Is it GUI or ewwee?

Here we have a lovely little 2014 Developer edition of SQL Server, with a couple of random databases.

instance_databases — Yes, I am reading T-SQL Querying.

We need to create a LOGIN for this instance so that we can grant the needed permission to it. So opening the “Security” node on the instance and right-clicking the “Logins” node gives us a nice, little option called “New Login…”. Click that!

Great! Step 1 of the multi-step process complete! Now for the first database which in our case is “DMODisabled”.

A LOGIN needs a USER, right? Well let’s just open up the nodes “Databases | DMODisabled | Security” and right-click “Users”. Now let’s create a “New User…”

dmodisabled_newuser — Like Father, Like Son…

We have a choice now.

We could connect to every table, one-by-one, and grant SELECT permissions to our User. Or we could be a bit more clever and grant SELECT permissions on the different schemas themselves.

It’s up to you to be honest. We have to ask ourselves the question though:

What happens if a new database, table, or schema is created?

Answer: We have to do this again for them.

Also remember all this was for one single database! By my count, I still have four more databases to do after this.

I’m going to do none of those things because it’s already tedious, I’m not the fondest of the GUI, and it is making the urge to add coffee to my coffee increase.

Scripting ALL the things:

I’m not sure how you would script this out but for me it involved cursors. Cursors that spanned all the databases and all the tables/schemas in the database. Not the best of solutions also because if any new databases, tables, or schemas got created after it was ran, and the need to connect to those was there as well, then you had to run the damn thing again!

New Solution:

Much like Bonnie Tyler, you need a hero! Well I present to you two heroes!

CONNECT ANY DATABASE, and
SELECT ALL USER SECURABLES.

Check it out:

Now I’ve dropped my User and Login so I can show you the magic of these guys.

Lets create the Login again, T-SQL-style:

CREATE LOGIN [GiveMeALLTheData]
WITH PASSWORD = 'UpUpDownDownLeftRightLeftRightBAStart'
, CHECK_POLICY = OFF;
GO

Now what server permission does it have?

SELECT sp.name
, perm.permission_name
, perm.state_desc
FROM sys.server_principals AS sp
INNER JOIN sys.server_permissions AS perm
ON perm.grantee_principal_id = sp.principal_id
WHERE sp.name = 'GiveMeALLTheData';
GO

Where’s our table at?

USE [DMODisabled];
GO

SELECT *
FROM dbo.HideAndSeek;
GO

instanceerror — Can’t pass Chiron at the first gate

Can’t access the databases huh? We’ll see about that. Enter our first hero!

USE [master];
GO
GRANT CONNECT ANY DATABASE TO [GiveMeALLTheData];
GO

Let’s re-run our SELECT statement again and…

The table tries to stop us? Too bad. Our second hero steps up to the plate.

USE [master];
GO
GRANT SELECT ALL USER SECURABLES TO [GiveMeALLTheData];
GO

Final bit:

You can now access any database on your instance. Go ahead, give it a try out and see, but you may ask yourself:

Wait, what about new databases, new schemas, or new tables, like you said?

Fear not! Any new databases have their permissions automatically added, and new schemas have their permissions automatically added, and as for new tables; you better believe that they have their permissions automatically added.

Much better than a script that is needed to be run repeatedly, right?
Keep the shiny new toys coming I say! 🙂