Voice of the DBA

Tag: sql server

  • 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

  • Guess the RTM Build

    I’m going to take a page from Joey D’Antoni and start a contest. We’ve gotten quite a few releases of SQL Server 2016, including now 4 Release Candidates (RC). RC3 was released recently, and noted as being feature complete. I am assuming this might is the last RC, though certainly any big bugs might result in an RC4 (or 5).

    This week, for a bit of fun, I’m going to start a pool for the final RTM Release number. If you post in the comment thread for this editorial, and guess correctly, I’ll send you a prize. Likely this will be something from Amazon, though I haven’t picked anything yet. Suggestions are welcome, though I am thinking some new SSD. We all need more fast storage.

    I’ll use the Price is Right rules, so the closest person, without going over. Ties will result in the first person posting winning, based on thread order. If the final build is 13.00.9500.12, and you post 13.00.9500.13, you’re out of the running.

    The RCs have been almost 100 builds apart. However there’s no guarantee that the RTM build will be follow any pattern, though it seems like previous releases have ended on an easy to remember build number like 12.00.2000, 11.00.2100, 10.0.1600, etc.

    Take your guess, and if you’re right, I’ll contact you with a private message to redeem your prize. Contest ends when SQL Server 2016 RTMs.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • 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

  • The Hidden Trigger

    I ran across a post recently where someone asked about using a trigger for a particular issue. There were quite a few responses, but one person brought up the fact that triggers have various downsides, all of which I agreed with. However I didn’t see one mentioned, which is a big one with me.

    Triggers are hidden.

    It’s not that triggers are hidden from DBAs by default, after all, there’s a triggers folder under each table in the Object Explorer, but how often do you open that folder? It’s just not obvious when a trigger exists on a table. The use of triggers is too uncommon, and it’s easy to forget they exist, even if you’re the person that added them to a database.

    This is really my one big complaint about triggers. I can’t easily determine when a trigger is firing, which sometimes leads to issues. I’ve troubleshot strange things in SQL Servers, only to realize hours later that a trigger is firing based on some action.

    Perhaps it’s me, but do many of you think about triggers first when something strange happens in SQL Server? I think I’ve gotten better over the years at looking for them, but I still get stymied and waste time trying to debug issues. I really wish that a “triggers” tab would appear in SSMS, maybe after the Execution Plan tab, whenever a trigger fired. It could have a copy of inserted and deleted, or even just the trigger code. That alone might clue me in quicker.

    I think triggers can be valuable in database systems, but because they seem to be so rarely used, I’ve grown to distrust and dislike them over the years.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 1.9MB) podcast or subscribe to the feed at iTunes and LibSyn.