Fine-grained Access Control for Stored Procedures (2024-06-14)

SQLServerCentral Newsletter for June 14, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Fine-grained Access Control for Stored Procedures]( - [Power BI Treemap Chart Step-by-Step Tutorial]( - [From the SQL Server Central Blogs - Extracting Data from the Planview Portfolios REST API using ADF – Introduction]( - [From the SQL Server Central Blogs - Writing Parquet Files – #SQLNewBlogger]( - [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Question of the Day - [Sensitivity Ranks]( The Voice of the DBA  NOSQL and RDBMSs This editorial was originally published on Aug 27, 2015. It is being republished as Steve is traveling. I first came across the term NOSQL in 2010 at a SQL Bits presentation by Simon Munroe. Provocatively titled “[Improving Database Performance by Removing the Database]( it was both deeply disturbing but also strangely liberating. The irony is that for all the NOSQL fanboyism from the extreme end of the development community many DBAs had also been quietly harbouring heretical thoughts about the way in which their databases were used. The examples Simon used to articulate the case for NOSQL gave voice to those guilty thoughts. Why do we use an RDBMS for…. - Web session state? - Logging and auditing? - Queueing applications? I believe the development community saw themselves as experiencing a sort of data Glasnost and escape from the tyranny of the DBAs. The death of relational technology was gleefully announced…..again. Where there is change society divides itself into four camps which my experience tells me aligns to a bell curve (not drawn to scale). [Four audiences for change] The stick-in-the-muds took one look at NOSQL solutions and concluded (correctly at that time) that the technology was somewhat flaky and dismissed it out of hand. The fanboys definitely saw the potential but forgot that RDBMS’s have survived and thrived for a reason. At the heart of our RDBMS technology is a well thought out and robust model. Thirty years of evolution and compromise have resulted in a balance of all the concerns that an RDBMS has had to cope with. The technology has shown a robustness sufficing a number of concerns beyond those it was originally designed to satisfy. These concerns have been driven by the commercial needs of customers for the technology.  NOSQL recognises that commercial needs have arisen that require a different compromise, perhaps sacrificing certain attributes in order to favour others. [RDBMS and NOSQL solutions require different compromises to satisfy business need] I think that both the stick-in-the-mudes and fanboys have failed.  The stick-in-the-muds failed in two ways: - To consider that in order to satisfy new business models a different balance in the technology compromises must be made - To consider the speed at which the NOSQL solutions were evolving. On the flip side the Fanboys also failed: - To consider that the RDBMS vendors would react and evolve themselves to rise to the NOSQL challenge. - To consider that some of the ideas from the RDBMS world are actually pretty good and are why the technology has thrived for so long. It is here that I would offer a few words of caution. The NOSQL vendors have re-discovered that some form of Structured Query Language is a useful abstraction for interacting with database technologies. While I understand the pressure to adopt a SQL like language I do think it is a mistake to base that language too closely on SQL. I feel that basing their query language on SQL, while aiding adoption it will lead to lazy thinking by architects and developers. It will prevent the exploration of key strengths of the NOSQL platform that do not fit easily with a language based on SQL. In the RDBMS camp I can understand the attraction of being able to accept and process JSON and absorb other NOSQL type functionality but isn’t this precisely the one-size-fits-all approach to solutions that NOSQL solutions originally identified as a mistake? David.Poole [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Fine-grained Access Control for Stored Procedures]( maxtardiveau from SQLServerCentral Fine-grained access control for stored procedures using a programmable database proxy [External Article]( [Power BI Treemap Chart Step-by-Step Tutorial]( Additional Articles from MSSQLTips.com Microsoft Power BI Desktop provides a wide variety of visuals to its users. Sometimes, one wants to conduct a comparative analysis, a simple way to explore the relationship between the data categories within a set hierarchy. Hence, when a decision revolves around understanding the relationship of each element, this visual can provide an insightful analysis. This article will highlight all the steps to create a treemap chart in Power BI Desktop. [Blog Post]( From the SQL Server Central Blogs - [Extracting Data from the Planview Portfolios REST API using ADF – Introduction]( Koen Verbeeck from Koen Verbeeck I have a client using Planview Portfolios to manage their PMO. As part of the data warehouse, we want to extract the project data from the Planview REST API.... [Blog Post]( From the SQL Server Central Blogs - [Writing Parquet Files – #SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA Recently I’ve been looking at archiving some data at SQL Saturday, possibly querying it, and perhaps building a data warehouse of sorts. The modern view of data warehousing seems... [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Site Owners from SQLServerCentral Learn how to write and design simple and efficient T-SQL code. This is a hands-on book that teaches you how to write better T-SQL with examples and straightforward explanations.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Sensitivity Ranks I want to use the ADD SENSITIVITY CLASSIFICATION DDL to mark some columns in my SQL Server 2022 database as PII. Can I use this code? ADD SENSITIVITY CLASSIFICATION TO dbo.sales.price, dbo.sales.discount WITH ( LABEL = 'Highly Confidential', INFORMATION_TYPE = 'Financial', RANK = 'INTERNAL ONLY' ); 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) Operator Precedence Order What is the correct order of operator precedence from highest (evaluated first) to lowest? Answer: +, NOT, AND, OR Explanation: The correct order is +, NOT, AND, OR Ref: Logical Operator Precedence - [ [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 - Development and T-SQL [SQL Plan Guide usage]( - Is it possible to see how many times a SQL plan guide has been used? [Transferring millions of records in live production]( - Hi we have req of transferring huge data from live table to another table in production env. Live table means simultaneous insert will be going on when transfer of data is happening. For e. G table A at 11 in the night has 5 million records. At 11 it is decided to shift this 5 […] [Parse Double Quoted comma separated data with comma]( - I've used this function before and I'm sorry I do not recall it's origination. (DelimitedSplit8K) It works as expected however there is a comma in the data that I am trying to parse. I've poste here the situation that I am trying to resolve and would greatly appreciate any help this accomplish the task of […] SQL 2012 - General [Replication help, please!]( - I cannot get a snapshot to generate for one of my five publications. The snapshot gets to 99% complete and then goes to status: "The process is running and is waiting for a response from the server." Eventually, it restarts from 0% again. I've been playing this game for hours, and now I don't know […] SQL Server 2019 - Administration [Run SSIS package asynchronously from SQL Agent job step]( - I'd like to run an SSIS package asynchronously from a SQL Agent job step through the the [SSISDB].[catalog].[create_execution] and [SSISDB].[catalog].[start_execution] stored procedures. I need the target package to be run by a specific login, so that login has been set as the owner of the SQL Agent job. However, the job step returns the error: […] [Creating an Index with a lot of Columns]( - Hi all. I have been working on SQL Environment that are really not well due to legacy system mixed with new systems. Need to ask bit of stupid question. I have run few different SQL Scripts to recommend Creating of indexes including BrentOzar FirstResponderKit. Mostly I use FirstResponderKit scripts. Here is where I am bit […] [What is re-enabling Ole Automation Procedures?]( - Hello experts, I'm trying to disable (0) Ole Automation Procedures via sp_configure. But each time I do so, the next day the setting has been re-enabled (1). I've tried a SQL trace but didn't catch it. Maybe I need to revise the trace. And there may be a Windows policy in place doing this but […] SQL Server 2019 - Development [Index Scan instead of index seek]( - Hello guys, I have the following query: SELECT COUNT(*) FROM "dbo"."STXL" INNER JOIN "dbo"."STXL_RET" ON "STXL_RET"."MANDT" = "STXL"."MANDT" AND "STXL_RET"."RELID" = "STXL"."RELID" AND "STXL_RET"."TDOBJECT" = "STXL"."TDOBJECT" AND "STXL_RET"."TDNAME" = "STXL"."TDNAME" AND "STXL_RET"."TDID" = "STXL"."TDID" AND "STXL_RET"."SRTF2" = "STXL"."SRTF2" AND "STXL_RET"."SPRAS_ISO" = "STXL"."SPRAS_ISO" WHERE "STXL_RET".ProcessName = 'ISAM_EKKO_T1' AND "STXL_RET".BoName = 'MM_EKKO' And the following indexes: STXL_RET […] SQL Azure - Administration [SQL DB migration]( - Hi I am New To Azure Sql.  Can some one please share  If we can migrate sql 2017 db with symmetric key and certificate in it on Prem to Azure SQL database? how to restore db from one azure sql database to another azure awl database? Thanks [Database Engine Tuning Advisor in Azure]( - Hello, all We have several performance issues, which I think could be mitigated by proper indices, better queries or even partitioning as some tables have over 1B records. I was hoping to use Database Engine Tuning Advisor, as I would do on my local copy of the DB in my previous roles, but seems it […] Azure Data Factory [deploying datafactory using YAML script - Error]( - Hi All, First foray into deploying adf using YAML scripting from Azure Devops from a dev resource to a test resource group..am using boilerplate code YAML code cribbed from the internet. The artifacts have been created correctly so that bit works. Deployment the second half requires creating in my azure project settings a service connection […] SQL Server 2022 - Administration [Servers being moved go new location - new IP addresses.]( - Hi Our Physical servers are going to be moved to a new location and new IP addresses will need to be assigned to the SQL servers. We use High Availability so will the listener sub net and IP need to be changed? I cant think of any other pain points - am I missing something? […] [Contained Availability Groups - Failover Questions.]( - Hi I am considering creating Contained Availability Groups in my production environment. Thinking I need to drop disabled jobs that live on both servers first? I've searched and read allot of Contained Availability Groups articles but none of them that I can find describe the failover process once the Contained Availability Groups are setup. During […] SQL Server 2022 - Development [Using USE does not set DB context]( - Hi Experts, I have a Sql file with a bunch of DDL statements (Create Tables/Views). I start with a DB then change DB context to another DB followed by 2nd set of DDLs. In Sql file, I have: DECLARE @DB1 NVARCHAR(MAX) = 'DevDB', @DB2 NVARCHAR(MAX) = 'TestDB', @ENV NVARCHAR(MAX) SELECT @ENV = 'USE ' + […] [blocking due to cuncurrency problem.]( - hi, CREATE TABLE [dbo].[bridge]( bridgeid [bigint] NOT NULL, [idpool] bigint NULL, status int null CONSTRAINT [PK_bridge] PRIMARY KEY CLUSTERED ( [bridgeid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] Go bridgeid is pk , and […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -