This may seem like a question with a simple answer but there is a bit more to it than you might think. In fact I know of 3 possible reasons (and there may be more I don’t know) for seeing a primary key error. Technically they occur for any unique… Read more
I seem to get a lot of permissions questions these days and one of the more frequent ones goes along these lines “I still don’t have the right permissions on database xyq.” So of course the first thing I do is use my handy dandy sp_dbpermissions stored procedure to check… Read more
When I was in college and taking programming classes (back in the days when “a T-Rex ate my 5.25 inch floppy” was a reasonable excuse) we were taught that since the compiler didn’t really see white space we should use it to format our code. And that is absolutely correct.… Read more
Let’s start by assuming that ANSI_NULLS are ON. If you aren’t sure what ANSI_NULLS are exactly, don’t worry, I’ll be going over that in some detail in a future post. However Microsoft tells us that ANSI_NULLS will always be ON in the near future. So we are not going to… Read more
If you want to grant a user the ability to create/alter/delete any table, SP, function etc in a database you have a several options. For example:
- You can grant all of the CREATE permissions either to the database itself or to all of the schemas.
- You can add the user…
I assume that since it’s the second Tuesday of the month that it’s time for T-SQL Tuesday. And you can be certain that Dev Nambi(b/t) has decided to make Assumptions the topic of the 56th T-SQL Tuesday.
I generally like to start with definitions when discussing… Read more
Why is this exciting? A number of months ago Kirsten photo-shopped Argenis into this self… Read more
One of the most powerful tools we have as users of SQL Server is Books Online (BOL). Whether you work mainly as an admin, a developer or in BI, Microsoft has provided a HUGE amount of information for you to use. But BOL is by no means your only resource… Read more
Every now and again you have to put a really long string (more than 8000 characters). Dynamic SQL is the most frequent example where I see this but I do see it elsewhere as well and it’s very easy to make a simple mistake. This is caused by the fact… Read more
These are a couple of stored procedures I wrote to help me with security research. Each of the stored procedures returns three data sets.
- A list of principals and some basic properties about them.
- Role membership
- Object/Database/Server level permissions
Each of the datasets has a set of do/undo scripts… Read more
Creating an alias for a SQL Server is fairly easy and there are several ways to do it. Configuration Manager is my personal favorite. Open up configuration manager and select the SQL Native Client xx Configuration. Under that you will find Aliases.
From here you can add, update or delete… Read more
A couple of weeks ago I did a post on granting or denying permissions to all the tables within a database. However sometimes you don’t want to grant permissions to the whole database at once. This is still pretty easy but there are no built-in roles to do it.… Read more
I frequently see the default trace mentioned in answers to forum questions, blog posts, articles, even training sessions. My knowledge of it after 15+ years with SQL Server unfortunately minimal. I know that it is a trace that is created by SQL automatically (hence the default) and that it is… Read more
A little while back Michael J Swart(B/T) offered to help you become a better writer. This was exciting for me because over the last 2 years (wow I’ve been doing this almost 2 years) I’ve been trying to get better at writing. However getting better… Read more
- An identity column is an auto incrementing column
- An identity column is typically used as a primary key
- A primary key that’s an identity column is usually a surrogate key
- A surrogate key is one that is not related to the contents of the row in any way
- An identity…
One of my co-workers and I were discussing the difference between the data type MONEY and the data type DECIMAL(19,4). I was rather curious so I spent some time getting it all down on paper.
First the obvious. MONEY takes up 8 bytes and stores the maximum possible values… Read more
The other day I started seeing the following error in my SQL log.
Error: 17810, Severity: 20, State: 2.
Could not connect because the maximum number of ’1′ dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by…
If you have spent any time looking around my site, you will notice that I love study materials and in particular I love free ones. I even have a page devoted to study materials most of which are free.
Typically when you see a heading like this you know the answer is “No” or “False” but in this case it’s more of a “hu?” You see a primary key and a table being a heap have nothing to do with each other. Well very little anyway.
- A “Primary…
Most people know that logon triggers can be dangerous if you aren’t careful. And if you didn’t know, well, logon triggers can be dangerous if you aren’t careful. They can be really useful and are a great tool, but if you mess up you can REALLY mess up.
If you… Read more