Voice of the DBA

Category: Blog

  • Explicitly using tempdb with ##tables

    I had someone ask me last night if this statement would create a permanent table in tempdb with a strange name or a global temp table:

    CREATE TABLE tempdb..##mytable
    ( id int
    );

    My suspicion was that this would always be a temp table, but since I usually don’t include tempdb, I decided to test things. I quickly opened up SSMS and tried it. I got this message:

    2016-04-21 13_55_14-Microsoft Solitaire Collection

    That seems to imply what I suspected. The ## override everything and determine the table type. When I look in the list of tables, I see my table there as a temporary one.

    2016-04-21 13_56_21-Start

    This is the behavior I’d expect, and it acts the same in SQL 2012, 2014, and 2016 (RC2).

    I don’t think there’s an advantage to using tempdb..##mytable, or even creating a permanent table (until restart) using tempdb.dbo.mytable, but if there is, please note something in the comments.

  • Changing the sa Password with SQLCMD

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I wanted to make a quick note on changing the sa password, as this is a sensitive account, and the password should be changed if you ever suspect it is compromised. I’d also recommend you change this if anyone that knows the password leaves your group.

    I wrote about using SSMS, but that’s not always convenient. If you need to change this remotely, perhaps in a hurry, SQLCMD is a quick way to do this.

    SQLCMD is a command line tool, so open a command prompt.

    2016-04-06 12_47_33-Photos

    Run SQLCMD and connect to your instance as a sysadmin. If you have any doubt, you can enter the query from my previous post to check your connection.

    Once you’ve connected, you can issue this code:

    ALTER LOGIN [sa] with PASSWORD = N‘Sup#rAmaz!ngP@$$w0rd’

    This is the code that will change the password for the login specified, even if I’ve logged in with a different account.

    Once I’ve done this, test the sa login from a new session and verify it works.

    SQLNewBlogger

    Make sure you know how to do this. It’s a basic skill, so learn it, blog about it, and use it where appropriate. Maybe write about why you’d do this in your own post.

    References

    SQLCMD – https://msdn.microsoft.com/en-us/library/ms162773.aspx

    ALTER LOGIN – https://msdn.microsoft.com/en-us/library/ms189828.aspx

  • Promotions and Conflicts of Interest

    I noticed my co-worker, friend, and PASS board member, Grant Fritchey, posted a note on members of the PASS Board of Directors (BoD) presenting pre-cons at SQL Saturday events. It’s potentially a legal issue, and conflict of interest. I’m glad that the issue is being raised, and discussed publically. Here’s Grant’s question:

    The question is simple, for a PASS branded event, should a member of the PASS board receive payment?

    There’s two parts to this, because there are two events. There are events that the organization runs and takes legal and financial responsibility for, and there are events associated with PASS, but run by others who have responsibility.

    My short answer is yes to local events, and no to events run by the PASS board. I’ve read through some of the comments and I have some thoughts.

    PASS Run Events

    First, events run by the organization, such as the Summit and BAC, are different. The BoD can vote on aspects of these events, and can override the decisions on which individuals are chosen. With that being the case, I think there is a clear conflict of interest here and for the limited time the BoD members serve, they should not be a part of these events. Whether they receive direct payment or not, I’d say no.

    There was a conflict about this a few years ago, and I think it was justified. If you serve, you can’t present a pre-conference (or post) session. You have other duties, and a responsibility here.

    If your business or your employer wants you to be a part of this event in a different way, resign one position or the other.

    SQL Saturdays

    Really this could be any event that PASS might support or lend their name to, but doesn’t have any financial (or likely, legal) responsibility. This is trickier, as certainly the ability to bestow favors on the organizers of these events in terms of choosing them for PASS run events is possible. However I’d say that this is very unlikely, and hasn’t been an abuse of power. If that changes, I’d change my opinion

    I think that the BoD members are still speakers and well respected trainers, and I really have no issue with them being accepted to present a pre-con and being paid by the events. I’d like the fact disclosed, but this doesn’t seem to be a conflict of interest to me at this point.

    It’s also good for the community.

  • Getting Started with Encryption in SQL Server 2016

    The release of SQL Server 2016 gives developers a number of ways in which they can securely encrypt and protect their data. In this introductory session, you will learn about the encryption options in SQL Server 2016, watching Always Encrypted, TDE, Row Level Security, Dynamic Data Masking, and server side column encryption can secure and protect your data from unauthorized users.

    Length: 60-75 minutes

    Code: Github

    Powerpoint deck: Getting Started with Encryption in SQL 2016.pptx