Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Default database

What?
The default database is one of the options when creating a login in SQL Server. This is the initial database that the login will connect to when logging in. Unless of course you specify a different database in your connection string.
 
How?

-- Creating a login with a…

Read more

0 comments, 74 reads

Posted in SQLStudies on 26 May 2015

Adding new users & groups in windows

I do a lot of testing with security in SQL Server. And of course to do a thorough job of it I need not just SQL Server logins but Windows logins. And that means I need to be able to create and delete windows users and groups. I could do… Read more

1 comments, 199 reads

Posted in SQLStudies on 20 May 2015

Extending your metadata with Extended Properties

Ever wanted to put a comment on a table? Or maybe even a column? How about an expiration date on a object? Well as it happens you can. Extended properties allow you to add a name/value property to a number of the objects in SQL Server. In fact if you… Read more

5 comments, 95 reads

Posted in SQLStudies on 18 May 2015

Test first; Mileage may vary;

I went to a SQL Saturday recently and saw a number of great sessions. If you haven’t been to a SQL Saturday before I highly recommend it. It’s a day of free training and networking put on by Pass. The training is done by members of the community who… Read more

2 comments, 107 reads

Posted in SQLStudies on 14 May 2015

TSQL Tuesday #66: Monitoring

If a user is going to call me about problem, I’d much rather know about it ahead of time. In fact my favorite problems are the ones I’ve already fixed before the users ever notice it. In order to manage this amazing feat you have to monitor your instances, your… Read more

1 comments, 3,121 reads

Posted in SQLStudies on 12 May 2015

Notes on Presenting

As you may know I’m preparing to write my first presentation. I have a great abstract and an outline of the presentation itself. Next step is creating the Powerpoint slides. Then practicing the presentation itself. So while going to my latest SQL Saturday I decided to take the opportunity… Read more

8 comments, 122 reads

Posted in SQLStudies on 6 May 2015

Building a job to monitor other jobs

The other day Tom Roush (b/t) and Tim Radney (b/t) were having a discussion on twitter about using scheduled windows tasks to run SQL Processes. Now why would you ever want to do this? Well if you are running SQL Server Express… Read more

5 comments, 4,149 reads

Posted in SQLStudies on 4 May 2015

Running Powershell scripts remotely.

I’ve only recently started to play with Powershell (PoSH) but even I’ve begun to discover what a huge number of tasks it can perform. Currently I’m working on a task to run SSIS packages remotely by using Invoke-Command to run DTEXEC on a remote machine. I’ll be posting the final… Read more

2 comments, 342 reads

Posted in SQLStudies on 29 April 2015

What SQL version is my SSIS package?

When SSIS first came out there were huge numbers of jokes and posts about all of the problems and how everyone thought DTS was much better and did they really have to switch? Over the years I’ve come to appreciate SSIS. It is a very powerful and useful tool that… Read more

0 comments, 286 reads

Posted in SQLStudies on 27 April 2015

Backup before you restore

You should always be recoverable. If you haven’t heard that before, take note of it. It’s important.

A while back I wrote about Operational Recovery. This is when there isn’t a disaster but you still need to restore some or all of a database. Someone forgot the WHERE clause… Read more

3 comments, 113 reads

Posted in SQLStudies on 22 April 2015

The hidden security of msdb

To be fair it’s not actually hidden, just not so well known.

Have you ever wondered how to grant permissions to create/modify jobs? How about SSIS packages?

Every database has the following built in roles:

  • db_accessadmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_ddladmin
  • db_denydatareader
  • db_denydatawriter
  • db_owner
  • db_securityadmin

 
However the msdb database has… Read more

3 comments, 165 reads

Posted in SQLStudies on 20 April 2015

Adding, altering or removing multiple columns from a table

I was recently asked to do some fairly heavy modifications to a table. It involved adding 6 new columns, removing 4 old ones, renaming 4 and then altering the datatype for those 4. I find these kinds of requests rather entertaining in a simplistic sort of way. I try to… Read more

2 comments, 145 reads

Posted in SQLStudies on 16 April 2015

Remotely shutting down an instance

This month for T-SQL Tuesday Mike Donnelly (b/t) decided to pick a topic to fit in with Ed Leighton-Dick’s (b/t) #SQLNewBlogger challenge. His highly open ended topic is to learn something new and blog about it. A great topic for a… Read more

1 comments, 4,611 reads

Posted in SQLStudies on 14 April 2015

Make sure you back up the Service Master Key

I’m by no means an expert in SQL Server encryption. What I do know however, is that the Service Master Key is the top of the encryption chain on an instance. This means that any certificate or key will be encrypted using, in part, the Service Master Key. So if… Read more

3 comments, 150 reads

Posted in SQLStudies on 8 April 2015

What is a CTE

CTEs (Common Table Expressions) are one of the most interesting and useful tools added to T-SQL in the last decade. But even though they have been around for that decade and are widely used I still find that they confuse people somewhat. Among other things this confusion leads… Read more

1 comments, 138 reads

Posted in SQLStudies on 6 April 2015

Microsoft buys the dictionary

It seems that Microsoft has finally gotten tired of all the jokes about the word “performant” or using “ask” as a noun. Microsoft has decided to put it’s money where it’s mouth is and has announced today that they have purchased both Websters and Dictionary.com. Now why would they do… Read more

3 comments, 139 reads

Posted in SQLStudies on 1 April 2015

Port numbers and SQL Server

I made a mistake the other day. I’ll admit it. I was wrong. Now I know all of you are shocked, but it does happen on rare occasions. So what was I wrong about you might ask? One of the dev groups I support has been referring to their instances… Read more

0 comments, 6,339 reads

Posted in SQLStudies on 30 March 2015

Clean out all bad characters from a string.

I have a customer who is having a problem with a load. He is getting a fair number of bad characters including some unicode characters. The unicode characters in particular are making it so those values won’t go into a varchar column. This isn’t an uncommon problem so when he… Read more

10 comments, 193 reads

Posted in SQLStudies on 25 March 2015

0 to Speaker

Recently the last day to submit a session for the 2015 Pass Summit rolled around. I mention this because I actually submitted a session. This is the first time I’ve ever submitted something for the summit and only the third time I’ve submitted a session anywhere. And no, in neither… Read more

10 comments, 152 reads

Posted in SQLStudies on 23 March 2015

Why not NOLOCK?

Most senior DBAs I’ve met shudder when they hear NOLOCK. Ever wonder why? For the same reason they shudder at shrink, MAXDOP of 1 and even occasionally at UDFs (user defined functions). Because frequently we see cargo cults develop around these technologies. Cases where a group of IT professionals (developers… Read more

4 comments, 243 reads

Posted in SQLStudies on 18 March 2015

Older posts