SQL Server DevOps with Production Databases and Git (SQLServerCentral 3/11/2019)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [SQL Server DevOps with Production Databases and Git]( - [Calling all Oracle Database users]( - [The BI Journey: The Expert’s Advice – Part 1]( - [Optimize for ad hoc VS sp_executesql?]( (From the SQLServerCentral Blogs) - [How to resolve ‘Login failed for user’ and sa password lost (Error 18456)]( (From the SQLServerCentral Blogs) The Voice of the DBA Who should have Sysadmin rights? This is somewhat of a trick question since I believe the answer is: no one should have sysadmin rights. I should clarify that statement. What I mean is, that no one’s normal network login should have sysadmin rights. It should only be granted to service accounts and/or to a specific SQL admin account. This would be a SQL Server admin specific network login account. The danger and security concerns for a normal login having sysadmin rights are very real. I am sure many of us have thought we were connected to a test or dev box only to find out after applying some update or alter statement that we just changed production by mistake. Some of you may be thinking, but how am I supposed to get my job done without sysadmin rights? I agree there are a number of things that happen in a day where a DBA needs sysadmin rights or at least elevated privileges. So, when those things come up you should be logging in as the SQL Server admin account to gain the admin level access you need. Now, you might be saying to yourself, I have too many servers, I would spend a large part of my day remoting in to the different servers to log in with a SQL admin login. That is one option to access SQL Server with a different account, but there are others. One option, assuming you use Management Studio when you are administering SQL Server, is you can hold the shift key down and right click the Management Studio icon. You will see an option for “Run as different user” If you select this, you can enter your SQL Server admin login and password once and all servers you connect to after that will be with those elevated admin permissions. Now, this does make it a lot easier to access SQL Server with the elevated permissions, but you have lost some of the benefits of not having those permissions with your normal login. So I would suggest you exercise caution when doing this to ensure you know when you have admin rights and when you don’t. Many DBAs would agree that least privilege is the best path for security, yet many of us bypass this by giving our own login elevated rights. In the end, I believe the benefits of giving sysadmin only to a SQL admin account out weight the detractions to doing it. So how about you? Does your normal login give you sysadmin rights? If you use a SQL admin login for elevated rights share how is it working for you? Ben Kubicek from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( ADVERTISEMENT [Webinar]( Extend DevOps to Your SQL Server Database Wednesday 13 March, 17.00-18.00 GMT / 12.00-13.00 CDT - Discover how implementing Redgate’s Compliant Database DevOps solution to your SQL Server Database estate helps you protect business-critical data and improve the efficiency and quality of your software delivery. [Register now]( [SQL Change Automation]( CI/CD for your SQL Server database Feeling the pain of managing and deploying database changes manually? Redgate SQL Change Automation completes your database delivery process by building, testing, and deploying the database changes you and your team check into version control. [Try it free]( Featured Contents  [] [SQL Server DevOps with Production Databases and Git]( Paul Stanton from [SQLServerCentral.com]() Learn how to start using DevOps techniques to upgrade your database containers with the latest version of your code from a git repository.[More »]( ---------------------------------------------------------------  [] [Calling all Oracle Database users]( We’re excited to announce that Redgate is doubling its efforts on Oracle and committed to aligning its DevOps solution across both SQL Server and Oracle. Please take a few minutes to complete this short survey to stay up-to-date on our Oracle developments.[More »]( ---------------------------------------------------------------  [] [The BI Journey: The Expert’s Advice – Part 1]( Additional Articles from [SimpleTalk]() Creating a quick dashboard with a few reports may be the beginning of a BI solution, but unless time is spent on the underlying model, it won't be adaptable enough to grow. In this article by Gogula Aryalingam, you'll see how the advice of a mentor helped a newbie understand how to create a flexible model that will lead to a valuable BI solution.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Optimize for ad hoc VS sp_executesql?]( Arthur Daniels from [SQLServerCentral Blogs]( Happy Friday! We made it. Here’s something I came across while testing optimize for ad hoc for this week’s blog...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [How to resolve ‘Login failed for user’ and sa password lost (Error 18456)]( HamishWatson from [SQLServerCentral Blogs]( This blog post explains how to get into SQL Server if you have lost your sa password or you have...[More »]( Question of the Day Today's Question (by Steve Jones): I connect to a SSH session on my SQL Server on Linux 2017 Ubuntu server host. Only the database engine was installed, but I want to install the command line tools. How do I do this? Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: SQLCMD. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): On SQL Server 2017, I have a brand new database with no objects. I run this code: CREATE PROCEDURE GetOne AS SELECT 1 GO CREATE PROCEDURE GetOne;2 AS SELECT 2 GO In SSMS, I only see one object listed, as shown here: If I get the script code by choosing modify, what is returned? Answer: The ALTER code for dbo.GetOne and dbo.GetOne;2 Explanation: There is an older feature of CREATE PROCEDURE that allows you to create versions of stored procedures. You do this with a semicolon and a number after the name. This only creates one object in sys.objects, but this contains code for both versions. When you script the object, you get the ALTER code for both versions. Note this feature is in maintenance mode (deprecated), and should not be used. Ref: CREATE PROCEDURE - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Administration]( [SQL Server Reporting Services 2017 Instability / Janky product?]( - Hi, We upgraded to SQL Server 2017 on January 4th this year, and we've had several situations where SQL Server Reporting... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Calculate the median]( - What is the fastest way to calculate the median --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [query plans drive off the cliff]( - query plans drive off the road. Have 3rd party app db users complained of slowness. This db and app running... [Always On]( - I know we can have 4 secondary database using Always on. However, i am little unsure that 4 secondary means... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Alternate Query for better performance]( - Hi, I have two massive tables, Order Header and Order details, I need to join these two tables. I need to... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Transactional replication latency issue]( - Hi, We configured Transactional replication. Suddenly in the replication monitor the current average performance it is showing as critical. I inserted... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [sent SQL query as formatted Excel .xlsx via email]( - I already have a working stored procedure that exports sql query data set into a .csv file and emails it. Works great. Now I... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Does the Order of Tables in the Join clauses of the Update Statement Matter]( - Suppose I have 3 tables as seen below: I would like to update the Name column of the Manufacturer Table based... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [Identify missing record on accounts]( - Hello guys, I have a table with accounts that have numbers in sequence. I would like to identify the account(s) when... --------------------------------------------------------------- [Programming]( : [General]( [Query a dynamic date range based off current month]( - I’m trying to determine which month I’m in so I can setup aSSIS package to auto run and drop off... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [Run SSIS packages unscheduled with interactive prompting]( - Hi, I hope this is the right forum. Also, I have Googled extensively (a few days) before posting, more for the... This email has been sent to {EMAIL}. To be removed from this list, please click [here.]( If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. --------------------------------------------------------------- This newsletter was sent to you because you signed up at [SQLServerCentral.com](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com