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.
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
Very Important Data
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
Backed up
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
He creates!
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
He restores!
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.
• Present a puzzle to be solved in SQL and challenge your readers to solve it.
• Or give us a puzzle or quiz about SQL or databases.
• Show the SQL solution to a classic puzzle or game.
• Provide a method for solving a classic sort of querying puzzle people face.
• Show how newer features in SQL can be used to solve old puzzles in new ways.
• Tell us about a time you solved a problem or overcame a technical challenge that was a real puzzle.
• Or just make your own interpretation of “puzzle” and go for it!
Matthew McGiffen
I didn’t have much in the idea department for most of them so I’m thankful that Matthew left us with the last own interpretation option. So here is my attempt at a puzzle.
Invoke-LollerCoaster!
Should I Cheat?
It’s a slightly strange story but I had already done a slight “puzzle” or “trick” in PowerShell where I created a Loller Coaster– Don’t ask me why, blame Andy Mallon ( blog | twitter ).
Now, that implementation used PowerShell but I thought to myself…
You know what…it uses System.Data.DataTable…that’s nearly the same thing as querying from a database. Could I get away with posting that?
Me
In the end, I decided against using the PowerShell version and said is there anyway that I could port it over to SQL Server?
Let’s create our table in tempdb and populate it. Could this have been made more compact? Perhaps with some fancy string manipulation and some such?
Oh yeah! But this was a quick job done during my lunch break and I wanted it done more than I wanted it perfect.
Plus, if you don’t like the below, you’re really not going to like what I use in the actual script…
USE tempdb;
GO
IF OBJECT_ID(N'dbo.LollerCoaster', N'U') IS NOT NULL BEGIN
DROP TABLE dbo.LollerCoaster;
END;
GO
CREATE TABLE dbo.LollerCoaster (
loller_coaster_id tinyint IDENTITY(1, 1) NOT NULL,
loller_coaster_stage varchar(4000) NOT NULL
);
GO
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
__)
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
__)
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
__)
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL\
O\)
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O\
L\) LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L\ LOL LOL
O\) O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O\ O O O O
L\) L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L\ L L L
O\) O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O\_)O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O__)O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L )L L L
O _/O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O )O O O O
L /L L L
O /O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L )LOL LOL
O /O O O O
L /L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O )
L /LOL LOL
O /O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL )
O /
L /LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O __)
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O __)
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O __)
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL\ LOL
O O O\) O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O\O O
L L L\) L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L\ L
O O O\) O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O__)O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L )L
O O O _/O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O \O
L L L )L
O O O /O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O \O
L L L )L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O( \O
L L L L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O/ O
L L L() L
O O O O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L( L
O O O\) O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O__)O
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O __)
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O __)
LOL LOL
O
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL\
O\)
L LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O\
L\) LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L\ LOL
O\) O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O\_)O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L LOL
O__)O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O
L )LOL
O _/O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL )
O /
L /LOL
O O
LOL')
INSERT INTO dbo.LollerCoaster (loller_coaster_stage) VALUES ('
LOL
O
L LOL LOL
O O O O O
L L L L
O O O O
LOL LOL
O __)
L LOL
O O
LOL')
GO
Next we’ll need to set up some things and create a few variables
SET NOCOUNT ON;
-- Set Ctrl + T to send results to text!
DECLARE @Counter tinyint;
DECLARE @End tinyint;
DECLARE @Stage varchar(4000); -- Horribly oversized, I know and apologise.
DECLARE @Pause varchar(12);
DECLARE @Clear varchar(4000); -- again, apologies...
Then we can set the variables that we have
SET @Counter = 1;
-- I could move this after the insert and use @@ROWCOUNT
-- but I created/inserted into the table in a different session.
SET @End = (SELECT COUNT(*) FROM dbo.LollerCoaster);
-- Just 15 line feeds
SET @Clear = REPLICATE(CHAR(13), 15);
Then… the rest is a WHILE loop. 🙁
RAISERROR(@Clear, 0, 1) WITH NOWAIT;
WHILE @Counter <= @End BEGIN
SET @Stage = (SELECT loller_coaster_stage FROM dbo.LollerCoaster WHERE loller_coaster_id = @Counter);
RAISERROR(@Stage, 0, 1) WITH NOWAIT;
IF @Counter >= 21 AND @Counter <= 29 BEGIN
SET @Pause = '00:00:00.100';
END; ELSE BEGIN
SET @Pause = '00:00:00.200';
END;
WAITFOR DELAY @Pause;
RAISERROR(@Clear, 0, 1) WITH NOWAIT;
SET @Counter += 1;
END;
There’s only a few things to mention here.
I’m clearing the screen at the start just for cleanliness.
There’s a tight loop between counters 21 and 29 where we want to simulate it speeding up.
We’re setting a pause between each one since we want the users to actually see the differences.
It can be improved so much!
USE [tempdb];
GO
SET NOCOUNT ON;
DECLARE @Counter tinyint;
DECLARE @End tinyint;
DECLARE @Stage varchar(4000);
DECLARE @Pause varchar(12);
DECLARE @Clear varchar(4000);
SET @Counter = 1;
SET @End = (SELECT COUNT(*) FROM dbo.LollerCoaster);
SET @Clear = REPLICATE(CHAR(10), 15);
RAISERROR(@Clear, 0, 1) WITH NOWAIT;
WHILE @Counter <= @End BEGIN
SET @Stage = (SELECT loller_coaster_stage FROM dbo.LollerCoaster WHERE loller_coaster_id = @Counter);
RAISERROR(@Stage, 0, 1) WITH NOWAIT;
IF @Counter >= 21 AND @Counter <= 29 BEGIN
SET @Pause = '00:00:00.100';
END; ELSE BEGIN
SET @Pause = '00:00:00.200';
END;
WAITFOR DELAY @Pause;
RAISERROR(@Clear, 0, 1) WITH NOWAIT;
SET @Counter += 1;
END;
No, I don’t know why the sizes change half-way through.
Let’s talk blog numbers for a moment… They say a picture paints a thousand words so I’m going to keep the word count down.
That’s 42,680 views across 34,920 visitors. Now seeing as last year it was 11,079 views across 9,061 visitors and that’s a 285% increase…yeah I’m pretty damn happy with that!
2018 was the year that I started to get more and more into the PowerShell community so I’m happy that 3 out of the top 4 posts were about topics that merges both the SQL Server and PowerShell communities.
I’m not going to read into the fact that my post with the highest views of 2018 was about me being a fan-boy of the work of somebody else. Mainly because it’s awesome and I’m a fan-boy of the work of a lot of people.
As for the Passive voice post, well it annoyed me and I was so happy to find the answer that I had to blog about it. Thank you to a certain German Fashion magazine forum for continuously sending traffic my way because of it!
Travel
SQLBits, SQL Saturday Oslo, SQL Saturday Reading, PSDay UK, etc.
It’s probably been my busiest year with regard to travel. Combine this with my moving back to Ireland from London in June this year and I’ve now reached the age stage where I can’t fully remember where I’ve been.
To combat this, I’ve enabled Google Timelines to help keep track of where I’ve been. Here is what it’s shown me since July 2018.
The amount of information that I’ve been taught, the people that I’ve finally met face to face, the beauty of the places I’ve been definitely makes me want to keep the travel going into 2019!
Shout out to Drew Furgiuele ( blog | twitter ) who I didn’t manage to meet when we were both in SQL Saturday Oslo. We’ll have our coffee/alcohol/workout/handshake eventually!
Learnings
While it has been a busy year for blogging, travelling, and meeting people it has come at a cost. One that I’ve only felt lately when I sat down and tried to a brief forward plan of the upcoming year.
I’ve been receiving an enormous amount of PowerShell help from the PowerShell community. They are extremely welcoming, open, and willing to aid knowledge seekers (so much so that I’m having a fun little argument with them about which community is more welcoming: PowerShell or SQL 🙂 ).
Catching up with some SQL webinars and reading some of the SQL blogging community’s posts has shown me that I haven’t been keeping up to date with the SQL Server announcements and new technologies.
This is something that I am going to have to actively aim to rectify in 2019.
Ideas
2018 was amazing for seeing a small subset of what someone can achieve with the technology out there in the world. To say that I’m bursting with ideas wouldn’t be far from the truth, thanks to a combination of procrastination and under-used time-management skills.
I’m going to be aiming to see if I can turn 2018, the year of ideas, into 2019, the year of actions.
Reflections
Hard to believe that I’ve been blogging for 3 years but this will be the first reflection that I have done.
Overall I’m pleased with what I’ve accomplished, learned, and realised with 2018. Yes, even the realisation that I need to increase my effort on SQL Server learning.
What get’s measured gets done!
Looking forward, I’m not quite sure what’s next. I’ve a couple of SQL Server and PowerShell days/conferences that I’m looking forward to. I suppose it’s time to stop reflecting and start planning.
To everyone who I’ve encountered in 2018, thank you.
To everyone who has encountered me in 2018, I’m sorry. I need more caffeine at the time.
To everyone I’m going to meet in 2019, looking forward to it!
…since I’ve written a blog post and there’s no better way to get back into something than to just start doing. Even if it’s just a throwaway, little post.
So here’s mine. Hope you enjoy it.
Don’t ask why…
… but recently I was trying in PowerShell to split a string up into its individual characters. So, as an example, ‘SQL Server’ would become the ('S', 'Q', 'L', ' ', 'S', 'e', 'r', 'v', 'e', 'r') collection of characters.
I also wanted the character before it and after it as well…
This Char
Prev Char
Next Char
S
r
Q
Q
S
L
L
Q
L
S
S
e
e
S
r
r
e
v
v
r
e
e
v
r
r
e
S
The Easiest Way…
…that I found was to simply ask for the character a certain position (or index) in the string.
As you can see we had to add a little got’cha to our code: ($i +1) % $String.Length
This is because, without the modulo operator (what remains when we divide the numbers), PowerShell looks for the next index (10) and returns nothing since there is essentially nothing in index 10.
So we ask PowerShell what 10 modulo the length of the string is ( 10 % 10) and the remainder is 0. This way we can wrap back around to the start again!
sifb [Nov 12th at 10:01 PM] @Shane O’Neill I think the $array[-1] loop-around is a powershell convenience, and doesn’t exist in C# / the lower level .Net libraries
So there we go…
…even though we may be used to SubString, it doesn’t mean that it is the best way for us to go. This is technically a new language and there are going to be tips and tricks for doing things that we don’t yet know about!
Half the fun is rooting them out, finding them, and slowly, slowly watching your code improve and knowing why.
I would have written a shorter [post], but I did not have the time.
