Oh Noes! I have a job that requires me to run an SSIS package, cmdshell script, etc. And I need them to have specific file level permissions! What do I do! I know I need to run my job/job step as a specific AD/Windows user but I don’t see how… Read more
I’m a big fan of certifications as a training exercise. However not everyone has the same interests. With the new MCSA (well new 6-8 years ago) we were required to take the data exam (70-461), the admin exam (70-462) AND the BI exam (70-463). I know a number… Read more
Me: I think I’m going create a new playground instance.
Myself: Make sure you use a case sensitive (CS) collation when you install it!
I: You mean a collation that forces us to pay attention to the case of the variable names (for example)? So that something like this returns… Read more
Everyone makes mistakes right? And sometimes you create an object and decide later that you messed up and need to rename it.
EXEC sp_rename 'OldName','NewName'
I love this somewhat obscure mathematical operator. All it does is return the remainder of a division statement but even though it’s pretty basic most people I talk to have never heard of it. The format is
dividend % divisor = remainder
DECLARE @remainder INT DECLARE @dividend INT = 10…
It’s T-SQL Tuesday again and this month we are hosted by Jens Vestergaard (b/t) who want’s us to talk about our favorite SQL Server feature. Great topic, but a really difficult choice. There are so many great features of SQL Server.
Some of my favorites include: Read more
This is one of those frequently ignored best practices.
When you create an object, specify the schema where it will be created.
I should also point out
When you reference an object (in a query for example), specify it’s schema.
I know I have a tendency to forget this myself.… Read more
Typically it’s considered best to run your queries without an ORDER BY and let the application do any sorting needed. Sometimes though it’s faster and easier to let SQL handle the sorting for you. And that’s fine. SQL is very good at sorting.
Simple sorts are pretty easy. In the… Read more
I’ve playing around with AdventureWorks and managed to found some hidden code! Truly odd stuff. I’m so shocked I couldn’t wait until the time I usually post.
So if you take the x character of the first character column of each table (where x is object_id % len(column) ), merge… Read more
To test or not to test. That is the question. Or is it? Shouldn’t we always test? That was the question posted here. It’s a big question so let’s break it down a bit.
You have a system and are about to upgrade some or all of it.… Read more
I answered an interesting question on SE today (ever notice how many posts come from stuff I read on forums?) and I was pleased with how the answer turned out. In particular one piece (expanded a bit) seemed like it would make a good blog post.
Tomorrow (3/24/2016) at noon CT I’ll be presenting my SQL Server Security Basics session for the Pass Security Virtual Chapter. If you are interested in SQL Server database security and have little to no experience with it then this is the session for you! Heck even if you do… Read more
tl;dr; There is no short version of this. It’s LONG. It’s interesting but you might want to skip it if you are in a hurry. If you have the time however, it’s a great walk through of tuning a query.
I had a great time today. I was handed a… Read more
Ever wondered how to tell what permissions are required to run a statement or perform a task? Well, sometimes it can be a little confusing to be honest. Most of the time though it’s all there in front of you in black and white.
Where you might ask? Books on… Read more
This is one of those things that I found very interesting but figured I’d probably never really use. Of course then I needed it recently and it came in quite handy.
Hey, I need sysadmin access to ServerA.
Ok. Why do you need sysadmin?
Well I need to be able to read and write to all of the tables on DatabaseA.
It’s T-SQL Tuesday time again (Number 76!) and this month we are hosted by Bob Pusateri (b/t) who has invited us to talk about Text. Text. What an interesting subject. So much of our jobs is all about searching for and manipulating text.
When I… Read more
The question of How to grant the ability to create/alter stored procedures and views came up today on dba.stackexchange.com. It’s actually a question I’ve pondered in the past. There are two major methods to do this.
If you’ve looked at the permissions available in SQL Server you’ll have… Read more
What with the #sqlnewblogger effort we’ve been seeing a lot of new bloggers (and some old ones starting up again) and there is one thing that seems to come up over and over again.
How often should I blog?
Let’s start with how often I blog. I post twice a… Read more
As with most of these types of things, I had a need. I want to show how using sys.dm_exec_[requests/sessions/connections] is better than sp_who. Particularly when you have a large number of connections. Well in order to do that I need a large number of… Read more