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
I had one of my developers ask me why he keeps seeing the following warning when he tries to modify a table:
Well just like it says he doesn’t have dbo access so he might not be able to save his changes.
Let’s start by creating a user with the… Read more
When developing in SQL Server you are eventually going to have to modify a column or two (at least). And if you use T-SQL to make the modifications (and I would recommend it most of the time) there is something you need to watch out for.
First remember that when… Read more
The other day I made a somewhat flip coment on twitter. (I know, everyone is shocked right?)
It started a rather amusing conversation over using varchar(1) vs char(1) and I thought it might be helpful to go over… Read more
Clearing out a full transaction log is a common problem. A quick search will find you dozens of forum entries and blog posts. Because of that I’m not going to talk about the correct methods of dealing with a transaction log full error. What I want to discuss is why… Read more
I use impersonation all the time to let me see additional permissions information I can only get that way and, sometimes to help me see what permissions error someone is getting.
For those of you who aren’t aware, with the right permissions you can impersonate another principal (server or database)… Read more
I have a moderately popular blog. In part because I syndicate but largely (in my opinion) because I write a lot of posts (twice a week for > 3 years). Over 340 of them in fact. Because of this a friend asked me what I do if I write a… Read more
Every now and again you need to run code when your instance starts up. For example you might want to check if the SQL Agent is running.
The first thing you have to do is make sure that the scan for startup procs configuration is set to 1. Read more
The CONNECT permission exists at the instance and database levels. Note: as of SQL 2012 it is CONNECT SQL at the instance level, but prior to that it was just CONNECT. It does basically the same thing in both cases. The CONNECT permission allows you to connect to the associated… Read more
I’m a big fan of automation. You get it written, tested, and scheduled, then you just sit back and monitor for unexpected problems (because if they are expected you fixed them right?). One common place for us SQL people to do our automation is SQL Agent. It’s built into SQL… Read more
tl;dr; Re-start the instance in safe mode (-f startup parameter) and move tempdb. Then re-start without the parameter.
To the page
It’s not one of those things you have to do frequently but every now and again you need to know what page a particular row is on. It’s not terribly difficult. There is a virtual column called %%physloc%%, but unfortunately it’s not terribly useful on it’s… Read more
I was reading some code the other day and it included the statement RESULT SETS. I’d never seen it before so it seemed worth a quick look. I’ll warn you in advance this is somewhat of a BOL blog post. I’m basically taking what you can find in BOL… Read more