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

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.

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…”

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!

1. CONNECT ANY DATABASE, and
2. 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

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! 🙂