Stairway to SQL Server Security Level 4: Permissions (2020-12-02)

 SQLServerCentral Newsletter for December 3, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to SQL Server Security Level 4: Permissions]( - [Configure SQL Server Database Mail on Amazon RDS]( - [Join us for this year’s final Redgate Summit]( - [Free eBook: SQL Server Backup and Restore]( - [From the SQL Server Central Blogs - Differences between using a Load Balanced Service and an Ingress in Kubernetes]( - [From the SQL Server Central Blogs - Server-Level Roles – Back to Basics]( Question of the Day - [WHILE in R]( The Voice of the DBA  Daily Coping Tip Tune in to a different radio station or TV channel 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. Completely Encrypted Data I remember reading about, and doing some message exchange, with PGP, in the 90s. At the time I worked in a utility company and my boss and I were interested in whether encryption might be something we should implement. At the time, the integration with mail clients, and the relatively unsophisticated users limited our options, and we never moved forward, but I've always been interested in encryption and how it fits into our digital world.  The technical bits have gotten easier, with https encryption automatically enabling for most of us, though perhaps only preventing limited attacks. We've gotten more options in the data platform, some that work well, some that require a decent coding effort, but they do work to some extent. At least, they make auditors happy and prevent silly leakage from something like a lost disk drive.  One of the main areas where encryption has been controversial is in real time communications. Governments and law enforcement want to be able to eavesdrop on criminal activity, or maybe other activity, and individuals want privacy. This seems to be an ongoing battle between technical companies and lawmakers as to how to implement features and what limitations should be enabled. I noticed a [story recently where Google is rolling out end to end encryption in its messaging apps](.  That got me to thinking. We capture and store data, and we may have some sort of communications in our system. If users demanded, or application developers built, end to end encryption, do we care as data professionals? Certainly we would need to allow for binary storage, and we'd lack insight or indexing into the actual data, but certainly could work with metadata like user, time, etc.  There are also other considerations for us. If we store encrypted data, is this more of a hassle in dealing with legal requirements? Do we want to have another sort of PII in a key or have to constantly explain to management or legal staff that we can't read the data because we don't store the key? There are non-technical burdens that we might not want to shoulder.  I do think that more systems ought to allow end-to-end encryption for communications, and user-managed keys are a capability that plenty of us might want in a world where no one physically sees the database server or disks. While I do like the idea of secure enclaves, which are catching on in computing, I also think that key management, especially for users, needs to improve. Perhaps we need a password manager for certificates, with backup included, to ensure our end users can properly manage their certificates across devices and in the event of any personal disasters.  Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to SQL Server Security]( [Stairway to SQL Server Security Level 4: Permissions]( Don Kiely from SQLServerCentral.com A permission gives a principal access to an object to perform certain actions on or with the object. SQL Server has a mind-numbingly huge number of permissions that you can grant to a principal, and you can even deny or revoke those permissions. This sounds a bit complicated, but by the end of this stairway level you’ll understand how SQL Server permissions work and how you can exert very granular control over object creation, data access, and other types of actions on database and server objects. [Configure SQL Server Database Mail on Amazon RDS]( Additional Articles from MSSQLTips.com In this article we look at how to configure database mail for a RDS SQL Server instance on Amazon AWS. [Join us for this year’s final Redgate Summit]( Additional Articles from Redgate With 2020 coming to a close, it's time to look at the new year ahead. Join our final 2020 summit this December 10th, to discover how to implement a generative culture for successful IT teams and how database DevOps will drive growth and ROI in a remote world. Register to tune in live or watch the recordings. [Free eBook: SQL Server Backup and Restore]( Press Release from Redgate In this free eBook Shawn McGehee offers advice on query tuning, cutting stored procedures, and system process design and implementation for high availability. Discover how to perform backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Redgate's SQL Backup tool. From the SQL Server Central Blogs - [Differences between using a Load Balanced Service and an Ingress in Kubernetes]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold What is the difference between using a load balanced service and an ingress to access applications in Kubernetes? Basically, they achieve the same thing. Being able to access an... From the SQL Server Central Blogs - [Server-Level Roles – Back to Basics]( SQLRNNR from SQL RNNR In this article, I have shown how to use the power of custom server roles to help reduce your administration time. The custom security role is like using a...   Question of the Day Today's question (by Steve Jones - SSC Editor):  WHILE in R Is this a valid WHILE loop in the R language? i <- 2 while (i<= 25) { print(paste0("Christmas Day ", i)) i <- i + 1 } 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) HA in General Purpose Managed Instance How is high availability handled in the General Purpose tier of Azure SQL Managed Instance in Nov 2020? Answer: Azure Blob Storage and Azure Service Fabric Explanation: The General Purpose tier uses Azure Blob Storage and Service Fabric. Ref: - High availability for Azure SQL Database and SQL Managed Instance - - Managed Instance Overview - [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 - Administration [alias SQL multisubnet failover = true]( - Good morning all , I have an always on instance to configure with a listener in mutlisubnet mode on the application side I am unable to add multisubnetfailover = true is it possible to create an sql alias with this parameter DESKTOP-95KKMMH\DISS; multisubnet failover = true thanks [HA Availability Group Listener]( - I was looking for a specific definition for the Active Directory / DNS team as to what I need set up for an HA Group Listener; does it just need to be a DNS name, IP address, and Directory Object in AD? If so, what is the object called, a cluster object? I have the […] [RegisterAllProvidersIP = 0 and timeout in failover]( - Good morning all , I have an alwayson instance with a multisubnet listener I configured RegisterAllProvidersIP = 0 in order to register a single DNS entry HostRecordTTL is configured at 1200 seconds I would like if with this configuration I will have timoeout during the failover if so how much time is needed for the […] SQL Server 2016 - Administration [SQL server night cpu usage]( - Hi using MS SQL maintenance plan to update statistic with full scan. The plan running over 2 days but still running. Try to restart DB server but the job auto resume. How can I kill all statistic update or estimate the complete time ? [Error connecting to sql using listener name only]( -  Hello I've a problem when i try to connect to an instance with Always On When i use a domain account, i can connect using listener_name\instance name or only listener_name When i use a sql account, i can only connect using listener_name\instance_name but not using only listener_name (error 18456) Is there a solution to […] [SQL permissions based on multiple AD Groups]( - Hi I'd like some input on the best way to handle setting permissions in SQL Server 2016 using 2 Active Directory Groups. I have 2 AD Groups - SQLDevs and SecurityCleared. I have created database roles for DEVS, CLEARED and DEV_CLEARED. I would like to apply permissions based on these Groups as follows... PublicSchema - […] Administration - SQL Server 2014 [sp_refreshview on indexed views]( - Hi I have to increase the size of a field in a table. I've listed all the views that use this table so I can run sp_refreshview on these. I'm left with a couple of schema bound views that use the underlying table, but not the changed field otherwise it wouldn't have allowed me to […] SQL 2012 - General [How to use While Loop to Insert Book details where status is not null?]( - I work on SQL SERVER 2012 . I face issue I can't use While loop to insert Books details that status is not NULL to table #BookHaveGeneralStatus . Meaning if any book have only one status not null on Books Details then insert it on table #BookHaveGeneralStatus using While loop . but if all Status […] [Get Particular Date from a Month]( - I would like to get a Thursday in a month which is followed with 2nd Tuesday. If the output is greater than today, Then I need to show the last month Thursday which is followed with 2nd Tuesday. For Instance, CASE 1 - If Today is 30 Nov 2020 - 2nd Tue is 10 Nov […] [Tlog migration]( - Hi Expert, In tlog migration, from 1 server to another where there is no connectivity I am doing following steps1 creating database replica on new server Running job's, user cript on new server In cutoverCoping the . Bak and log files from one server to another Creating ag group and assigning server to it Any […] [Tempdb increasing crazily]( - These days, the Tempdb of my one's SQL server increases crazily, sometimes only abount 5 mintues the MDF file increases 70GB, but lof file of tempdb is not big(Maybe about 2-3GB), I have some questions regarding Tempdb, thanks! as shown in the picture, there are many temp table in the tempdb, normally, when the temp […] SQL Server 2019 - Development [Find matched records between two tables]( - Hi, I haven't been able to find anywhere how to do or which transformation to use. I've seen Lookup but it only has one input, merge join but I could only get it to append... I'm using SSIS 2019 Community in VS with Windows 10. I've brought in two tables using flat file source nodes […] SQL Server 2008 - General [sys.dm_os_performance_counters Resource Pool Stats returns zero]( - I am using SqlServer 2008 R2 Express edition. When i execute below query i will get all 0 . select * from sys.dm_os_performance_counters where object_name = 'SQLServer:Resource Pool Stats' By referring this Link - In express edition I can't use this query to get resource statistics. But is there any other method to get the […] SSRS 2016 [Enabling date Input parameter to allow mmdd input but display as dd/mm/yyyy]( - Hi there I have an SSRS Report with various input parameters Now one of these is a date field which we want to format to mmdd format but as the user tabs out of it, it will then snap back into dd/mm/yyyy format. For example , 1130 would be entered by the user but then […] General [Costing per database]( - Hi, I am just checking to see if anyone has ever did a costing per database (based on it size). I am trying to figure out what the cost is for a database throughout its lifecycle. I am looking for mow much disk space will each DB use, how much memory does it require, how […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -