Stairway to SQL Server Security Level 5: Schemas and Security (2022-09-21)

SQLServerCentral Newsletter for September 21, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to SQL Server Security Level 5: Schemas and Security]( - [SQL Server and Oracle Transparent Data Encryption Differences and Similarities]( - [Data Governance: Joining the Dots]( - [From the SQL Server Central Blogs - Creating a SERVER ROLE created a LOGIN as well.]( - [From the SQL Server Central Blogs - Distributed Replay: The Little Engine That Almost Could]( Question of the Day - [Another Conversion Question]( The Voice of the DBA  Daily Coping Tip Leave positive messages for yourself to see regularly 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. Custom SQL Server on RDS One of the challenges in moving to the cloud is achieving a similar level of performance to what you would get with dedicated hardware on-premises. The cloud is someone else's computer, and you are limited to the choices that the vendors allow you to rent. In many cases, you might even share the physical hardware resources with other customers, though many cloud providers have started to allow dedicated hardware reservations in the last few years. One of the challenges of choosing a PaaS cloud database platform is that there are often restrictions that differ from a similar platform on-premises. This is often to ensure that one customer doesn't affect another, or that customers don't make changes that might prevent the vendor from supporting the system. Azure SQL Database gives us a database only, no instance features, though we do see a virtual master database. AWS, however, is changing its RDS offering to allow customers to customize their database software. They [started with Oracle]( and then [added SQL Server](. For the SQL Server offering, they will let you not only change the instance config with things like CLR and install drivers in the host OS. They'll continue to manage the overall service, and handle backups, DR, etc. You can focus on your database, but if you have special configuration needs, you can set them up. This is a nice evolution of the cloud by giving customers more control and meeting their needs, but not forcing them to handle everything. While a lot of technologists don't think the OS adds a burden to a DBA, I'd disagree. I think the popularity of RDS and Azure SQL Managed Instance show that a lot of customers feel the way I do. Paying for a service to be managed is valuable, and it allows your staff to focus on more helpful tasks, like performance tuning, finding data quality issues, etc. Of course, your staff has to take advantage of that extra time. I see plenty of companies that don't culturally change how they work with their systems, and that means that a lot of the benefits the cloud offers aren't being seen by the company. 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 5: Schemas and Security]( Don Kiely from SQLServerCentral.com In this stairway level you’ll learn how you can give principals access to groups of objects by assigning permissions on schemas instead of individual tables, code modules, and other objects. You’ll also learn about the benefits of user-schema separation and how it can increase object security, and how using default schemas for users and groups can simplify object access management and security. [External Article]( [SQL Server and Oracle Transparent Data Encryption Differences and Similarities]( Additional Articles from MSSQLTips.com In this article, we look at how to set up Transparent Data Encryption for SQL Server and Oracle along with the differences and similarities. [External Article]( [Data Governance: Joining the Dots]( Additional Articles from Redgate Every organization must perform data governance. This requires planning, oversight, and control over the management, security, resilience and quality of data and over the use of data by the organization. In larger organizations, it can be a complex task. William Brewer explains what's involved. [Blog Post]( From the SQL Server Central Blogs - [Creating a SERVER ROLE created a LOGIN as well.]( Kenneth.Fisher from SQLStudies One of my co-workers came to me the other day and told me that they found their network id as ... Continue reading [Blog Post]( From the SQL Server Central Blogs - [Distributed Replay: The Little Engine That Almost Could]( Grant Fritchey from The Scary DBA Honestly, sincerely, no kidding, I love Distributed Replay. Yes, I get it. Proof positive I’m an idiot. As we needed proof. To be a little fair to me, I...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Another Conversion Question What is the result of this code? select 4444.0 * N'1111.0' 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) More Conversion Quetsions I have this code in SQL Server 2019. select 4.0 * N'1111.0' What is the result when I run this? Answer: Arithmetic overflow error converting nvarchar to data type numeric. Explanation: This creates an overflow error. The data types are converted based on precedence. In this case, we have two data types: - decimal(2,1) - nvarchar(6) The decimal has a higher precedence and converting the 6 character value into a 2,1 scale is an overflow. If we do this, it works: select CAST(4.0 AS numeric(6,1)) * N'1111.0' Ref: Cast and Convert - [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 [Backup directly to cloud storage for 1.5 tb database]( - Today we looking to take backup from on prem SQL to cloud storage in blob storage directly and DB size is 1.5 tb, network speed 100 mbps. Yes, its possible to do backup directly to cloud storage blob. But want to know which method this copy / backup can work like AZ copy or any […] SQL Server 2016 - Administration [Permissions for Conflict Viewer]( - Hi, I'm trying to find the minimum permissions necessary to give a user access to the Replication Conflict Viewer and allow user to view and resolve conflicts in Management Studio. SQL Server 2016 - Development and T-SQL [How to drill down in recursive cte?]( - I have the following working example: DROP TABLE IF EXISTS consignment_levels; GO CREATE TABLE consignment_levels( consignment_id INT NOT NULL, parent_id CHAR(10) NULL, level_id CHAR(10) NOT NULL, level_type CHAR(10) NOT NULL,--case, master_case, pallet ); CREATE UNIQUE INDEX consignment_levels_U01 ON consignment_levels (consignment_id,parent_id,level_id); GO DECLARE @v_consignment_id INT = 1000; DECLARE @c_pallet CHAR(6) = 'PALLET', @c_master_case CHAR(6) = 'MASTER', […] [Parsing XML with SQL Server]( - Thanks in advance for the assistance. There is some data on a website that I would like to import into a SQL Server database to query: Disclosure Table The data is also presented in an XML file: I'm having difficulties parsing the data. I think I'd like three tables variables (or temp tables) […] [Complex SQL Pattern Matching Assistance]( - Hello Community, Can someone assist in SQL pattern matching. I have two tables company and organisations. In the organsations table there is a field 'cbname'. I would like to find a 85% match between the field 'cbname' and the field in company table called 'companyname'. I would then like the results to be added to […] [How to calculate canceled accounts / total accounts]( - Good Morning, I have the two tables customers (joined) and their orders (dateoforder- record perday) CREATE TABLE Customers ( CID int NOT NULL PRIMARY KEY, GroupName varchar(255) NOT NULL, FirstName varchar(255), Age int, CustomerStatus SMALLINT, ---1 Means Active, 0 Inactive CustomerCancelDate Date, DateofJoin DATE ); INSERT INTO Customers Values(100, 'ABC123', 'John',30,1,NULL,'01/14/2022') INSERT INTO Customers Values(101, […] Development - SQL Server 2014 [SQL XML help]( - I have the following query that generates the output SELECT top 1 AccountNumber as 'ExternalId' from Account FOR XML PATH('Element'),type --output generated 3002543105  How can I get the following output 3002543105  Thank you in advance [Recursive calculation // calculation column referring to itself]( - I have following data table with quantities and minimum stocks. I would like to check if the cumulative quantity goes below the minimum stock. If so the column "ProductionProposal" should calculate the quantity that needs to be produced in order to fill up the stock (with consideration of the defined minimum lot size). My problem is […] SQL 2012 - General [SQL Server 2012 Migration ideas?]( - Hi Guys, I have a 2008R2 VM running SQL Server 2012. I would like to migrate hosts to a 2012 box that will also be running SQL server 2012. The original SQL box has an OS partition and a Data partition (separate VMDK's). Data partition is the root directory and contains all DB's. I could […] SQL Server 2019 - Administration [Issue with SQL Server mirroring endpoint creation using SQL Authentication]( - I am trying to create EndPoint in SQL Server 2019 using transact SQL CREATE ENDPOINT endpoint_mirroring STATE = STARTED AS TCP ( LISTENER_PORT = 5022 ) FOR DATABASE_MIRRORING ( AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = SUPPORTED, ROLE=ALL); GO  I want to use SQL Authentication for creation of mirroring endpoints. I can use SQL Management studio to do that, but […] SQL Server 2019 - Development [2 new Column from string]( - I have string in table where column has the value 163 Bathroom {BATHROOM CLEANING}{2.5%} I want to 2 new columns from this string column1 BATHROOM CLEANING column2 2.5% The first curly bracket { is starting point for first new column and will end data closing curly } bracket and second curly bracket { for second […] Azure Data Factory [Upskill Azure Data Factory knowledge]( - Hey guys, I'm a DE with of 2 experience and I've been working with Azure Data Factory as the main ETL tool for 6 months now. However, I feel I need to gather more knowledge and assume more responsibilities inside my team but most of the senior professionals don't have much time to teach me. […] Reporting Services [Combine large tables into one dataset]( - Hello everyone, I'm facing a small challenge. I have four different data pots and need to merge them into one data set. The join at the database level is generally not a problem, but the performance is sub-par. The four tables are as follows. projectStructure (65,527 rows) cashManagement (261,135 rows) bookedHours (48,233 rows) budgetValues (35,836 […] Analysis Services [MDX Year on Year]( - Hi Guys Trust you keeping well. I have an issue with an meausre where we need to show the previous years data. Basically we need to compare a certain month on a daily basis the MTD value vs the previous year for the same month MTD per day. I am not that well versed in […] Integration Services [Data Flow Task Error showing generic message]( - I'm having difficulties performing a Data Flow Task between a .csv file to OLE DB. By default, the column [ID] is in DT_STR datatype. It throws the following generic error: GF Data Flow Task:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (515) failed with error code 0xC0209029 while processing input […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -