Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

When things go wrong, be compassionate to yourself

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Carefully Giving Normal Users Security Permissions

I've seen numerous posts over the years that ask for a way to allow some "regular" user to run a privileged operation. The two most common items are running jobs and changing security. These posts always invite a number of solutions and lots of cautionary notes about the dangers of allowing users to fundamentally alter the way your instance behaves.

I do understand the reasons why people want to allow this. There are often administrative things that we can delegate to a user that we can trust them to do. They know how security should be managed or they know when to run a job. In essence, we are using them as proxies so that we don't get overwhelmed with requests for simple tasks.

At the same time, we don't want to give them access from SSMS to do something like add a user to a role or create a login. There is too much chance that they'll do something wrong. Worse, they might actually make a mistake and grant someone too many privileges or remove another privileged user from a system.

What I've done in these cases is try to implement a simple proxy system that doesn't directly allow the user to do something, but they can ask the server to do it. What do I mean? Here's an example. I'd create two tables: one for data and one for logging. In the data table, a user(s) has rights to insert some data, like the name for a new login. I then use a process (usually an Agent job) that reads this table, performs some action, and then logs the item in my logging table. For something like a login, this would likely generate a strong password and send it to someone.

I like to be able to run checks, avoiding known issues, preventing things like changing a password for "sa", or some other issue. I also like having my own log since I've known too many users, including administrators, that forget what command they just ran. Using a process for simple tasks allows you to audit what is taking place.

I've seen similar systems for sysadmins implemented as Slackbots that allow them to change passwords or run some job with a command in a channel, rather than directly connecting to machines. The idea, however, is the same as what we try to do in DevOps. Build a process, improve it over time, and use automation to handle the simple tasks that computers do better than humans.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
  Featured Contents

Unable to connect SSIS remotely

iLearnSQL from SQLServerCentral

A strange error with SSIS is solved with a networking change.

Install SQL Server Integration Services in Visual Studio 2019

Additional Articles from MSSQLTips.com

Learn the step by step process to install SQL Server 2019 Integration Services in Visual Studio as well as the history for the tool.

Automate Responses to Bad Deployments with SQL Monitor

Additional Articles from Redgate

Jamie Wallis explains how SQL Monitor can both reveal quickly who ran a deployment, and when, and automate the incident-response workflow to ensure it's dealt with swiftly. By extending such workflows to development and test servers, as well as production, the feedback cycle starts earlier, and you can stop problems from ever reaching the users.

From the SQL Server Central Blogs - The Manager’s Guide to Git Training for DBAs (Video / Podcast)

Chad Crawford from LittleKendra

Learning Git can be daunting for DBAs. In this 20 minute episode, I discuss why learning a VCS is necessary for DBAs, then give three tips on scoping your...

From the SQL Server Central Blogs - Getting the Proc Code–#SQLNewBlogger

Steve Jones - SSC Editor from The Voice of the DBA

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. I saw a question recently about getting the...

 

  Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Do I need a restart?

I have been using sp_configure to change some settings for my instance. I know I need to run RECONFIGURE to change the option, but how can I tell if I need a restart?

Think you know the answer? Click here, and find out if you are right.

 

 

  Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Plotting Data in Python

I am using the pandas module for some data analysis in python. I want to use the plot() method for a dataframe. Which of these is not a type of chart I can produce?

Answer: donut

Explanation: The donut chart is not available in pandas, as of Jul 2020. Ref: pandas.DataFrame.plot - https://pandas.pydata.org/pandas-docs/version/0.23.4/generated/pandas.DataFrame.plot.html

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 2016 - Administration
Migrate database with encrypted columns to SQL 2016 - Hi all, I need to migrate a SQL 2012 database that has a few tables with encrypted columns, to a SQL 2016 instance.  I have done this same process between two 2012 instances, and it always worked, but I am unable to do so with 2016.  Below is the code I run after I restore […]
Snapshot database - how to refresh the sanpshot database  with the source database ,  please let me know the script if any ?
database not in Always on - Hi On my server there are 20 db as part of Always on. For some reason 1 db is not showing in Always on. Is there a way to find out when/who did this happen Regards Atulyan Padmanabhan    
SQL Server 2016 - Development and T-SQL
No record in an UNION query - Sorry if the title is unclear. The following query is used in an Excel pivot table. It is an union of Cie1 and Cie2 companies.  The main criteria is to show the back orders (b/o) I would like that as soon  that either one has a b/o, both Cie1 and Cie2 will show, Currently, only […]
Administration - SQL Server 2014
Create SQL Login to View only one database on server - Hi All, I have one task to create a login, and user need to view only One Database, there are 20 other databases but requirement is for the user to view only one database and only 5 tables from 1000 tables. I tried to create a login and give Server Role Public and Database role […]
Development - SQL Server 2014
To use the same function to pass table name as a parameter - I have this function, but I wanted to pass a table so as to use the same function to get the job done for multiple tables. For example, I want this function work for table1, and table2. But it is just for table1 currently. I was trying to use a dynamic sql in vain; it […]
SQL Server 2019 - Administration
SSRS - Certificate - Hi Team, When I am trying to configure web service URL, using HTTPS certificate I am getting an error as " Create Certificate binding error" I have installed SQL Server 2019 along with SQL Server 2019 reporting service from the link : https://www.microsoft.com/en-us/download/details.aspx?id=100122 Is this error related to any version issue? My SSRS product version […]
SQL Server 2019 - Development
T-SQL decoder ring for Google BigQuery? - Sorry if this is in the wrong forum... feel free to move it. I'm okay with T-SQL, but BigQuery boggles my mind. Is there a good reference comparing the two somewhere? I was looking for a little bit of something sort of like an intro and recipes for converting between the two. thanks!  
Returning data from table based on date ranges - Hi There, I have been play with some scripts and getting a bit stuck. So I have a table called charges that stores rent and service charges for properties. Some properties pay rent monthly and others in English quarter days which are (March 25th/June 24th/September 29th and December 25th). What I am trying to do […]
1-Table & 2-Table data migrate - Hi,   I tend to copy data from Table1 to Table2. Table1 will always being refresh & truncate, because it will subsequent load new record into it.   Table2 will be permanent main table.   As i know there is a way:- Merge  Cursor For Loop Is there any good way to achieve that?   […]
Script works on one server, errors on others - I am running a script that yields exactly what I'm looking for in test, but when I copy and paste the identical script to a prod box I get: Msg 537, Level 16, State 3, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function. I can recopy and repaste it back to […]
SQL Server 2008 - General
Logical Reads of Query is High - I am using SQL Server 2008 R2. The Queries executed and the IO statistics of the the queries are below. Queries: Query 1: select COUNT(*) from [TestRec].dbo.[TestRec] WHERE ([TestRec].dbo.[TestRec].[ParentGrpId] IN (SELECT DISTINCT [TestRec].[ParentGrpId] AS [ParentGrpId] FROM [TestRec].dbo.[TestRec] WHERE ( [TestRec].dbo.[TestRec].[ID] IN (228859,228934) )) ) union all select COUNT(*) from [TestRec].dbo.[TestRec] WHERE [TestRec].dbo.[TestRec].[CL Id] Is Not […]
Analysis Services
Azure Analysis Service: Process Tabular Model with SSIS - Hello, I hope you are well. I know this is not the right place to discuss Azure Analysis Services but I have not found a forum for this product. I would like to process an Azure tabular model using SSIS but I am experiencing connection difficulties in SSIS. I think it should have a configuration […]
Integration Services
how to convert the below procedure into ssis for each loop and send mail task - I am tryting to convert the below procedure into SSIS, even if i get temp1 and temp2 into object variable.. how can i use those in for each loop container where it starts while condition in ssis? BEGIN TRY BEGIN Select PG.ID , PG.Time ,G.Address ,PG.Date ,b.Description as [D] ,b.Rec INTO #temp1 FROM tblPep PG […]
SQLServerCentral.com Website Issues
"Stairway Series" page exposes article revisions - I clicked on the "Stairway Series" link below an article title and it brought me to the main "Stairways Series" page: https://www.sqlservercentral.com/categories/stairway-series As I scrolled down, I noticed that there were 3 entries for one of my articles: "Stairway to SQLCLR Level 2: Sample Stored Procedure and Function". One of them is listed as "Stairway […]
 

 

RSS FeedTwitter

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.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -