A while back I was testing attempting to access SQL Server using a defined port number.
To do that, you have to access SQL Server Configuration Manager to specify the port number, or at least I think you do (If you don’t have to do it this way, please, oh please, let me know!).
So, since my laptop is running on Windows10, I open up the Start menu, type in “config” and…nothing! No SQL Server Configuration Manager! The closest I got was the configuration manager for Reporting Server.
I’m shocked by this especially because when I type it into the Start menu now, I get…
Well it turns out that, for me, the default path is now in…
C:\Windows\sysWOW64\SQLServerManager”<nn>”.msc
I say “<nn>” because it’s a number dependant on what version of SQL Server that you are running.
For example, I have a 2012, a 2014 and a 2016 version on my laptop so I have 3 versions of SQL Server Configuration Manager.
Seriously, is that it?
Nope, that ain’t it.
Opening up Windows Explorer, going all the way down to that level just to get the config manager? Ain’t nobody got time for that!
They say “imitation is the greatest form of flattery” so taking ideas garnered from dbatools and the fact that I’m just finished a pre-con for SQL Saturday Boston in PowerShell from Mike Fal ( b | t ), thank you Andy Mallon ( b | t ) for pushing me out of my comfort zone even if it was only to just sign up!, I’ve created a little PowerShell script to let me choose and open a SQL Server Configuration Manager.
LIKE a function… now the song is stuck in your head!
… I’m not sorry…
Start: (‘abc%’)
Ever heard of “Osmosis”? You know, the…
process of gradual or unconscious assimilation of ideas, knowledge, etc.
For the longest time, that was how I thought people learned in SQL Server. You hang around a technology for long enough and the information about it slowly trickles into your brain.
I would hazard that the vast majority of people learn like this. They work with something long enough and slowly they develop, if not a mastery of the subject, then a familiarity with it.
That’s how I learned Transact-SQL anyway.
Working in a help desk, trouble-shooting stored procedures and ad hoc statements; cranking out reports left, right and center, slowly absorbing the differences between INNER, LEFT, RIGHT, and FULL joins. Realizing that there is a vast difference between excluding results with a WHERE clause and with a HAVING clause.
Ahh good times!
However, now I’m in the mindset that if you really want to learn something then study it; purposefully and deliberately.
And with all the new features being released for SQL Server 2016, you would be amazed at what I can learn about features that were released in SQL Server 2008.
So here’s some little known facts I learned about LIKE
Middle: (‘%lmnop%’)
Safe to say, that we’ve all used LIKE, we’ve all seen LIKE, and we’re probably all going to continue to use LIKE.
But are we using it to the best of our ability?
I wasn’t.
So let’s test out this bad boy using the WideWorldImporters database, see if we can find everyone with the first name of Leyla.
Simple right? And because [Sales].[Customers] uses the full name, we have to use LIKE.
SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Leyla%';
GO
Now a developer comes along and says “Wait a second, my sister is Leila”. So we try to cheat and add a wildcard in there.
SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'le%a%';
GO
Leonardo!? Well I suppose he does count in this situation, but there’s 2 characters between the ‘e’ and the ‘a’ and I only wanted one.
Well you can specify only 1 wildcard with the LIKE function by using the underscore (‘_’), so let’s try that.
SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Le_la%';
GO
Yes, I cheated and inserted that extra name ‘Lejla’.
Call it Poetic Licence but I only used it to show that we still have options if this is not the results that we want. We are only interested in ‘Leyla’ and ‘Leila’.
‘Lejla’, while a lovely name I’m sure, is not what we require right this second. So what are we to do?
Well, did you know that LIKE has the range function as well? What’s range got to do with it? Well, what happens if we only put in a range of 2 characters?
SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Le[iy]la%';
GO
There we go! Only the results that we want and none of that Lejla, Leonardo business.
Now you could argue with me (I encourage it actually. How else am I to learn?) and say that you would never do it this way. That it is much easier to do something along the lines of this:
SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Leyla%'
OR CustomerName LIKE 'Leila%';
GO
I’ll admit that the above reads a lot easier, but it doesn’t scale very well though. What happens if we want to include the Leala, Lebla, Lecla,….all the way to Lenla’s? Are you going to write out 15 different clauses? 1 for each different character?
SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Leyla%'
OR CustomerName LIKE 'Leila%'
OR ....
OR ...
OR ..
OR .
GO
Or are you going to go back to ranges and do a clean, efficient, single range?
SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Le[a-ny]la%';
GO
Now I’d argue that that is a lot more readable than an endless list of OR clauses tacked on to the end of a script.
End: (‘%xyz’)
There is a lot more you can do with the LIKE function. Just because you may encounter this little guy every day does not mean that you know it.
Don’t shy away from the fundamentals. Every little bit that you learn can and more than likely will be used to improve your skills and make you better.
Hopefully these little tidbits of knowledge will sink in…just like osmosis 🙂
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.
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!
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
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! 🙂