A Fine Slice Of SQL Server
Kenneth Fisher creates a diet version of db_ddladmin:
What if I want the user to be able to create VIEWS, TABLES, FUNCTIONS, etc. Basically, I want a db_ddladmin Jr. Something I can give to a developer that is focused on a single schema. Say the reporting schema.
The best practice is, of course, to use a role.
Click through for Kenneth’s script for a db_ddlcreator role.
Comments closedTom Collins distrusts (certificate) authority:
I’m getting this error when connecting Microsoft OLE DB Driver 19 for SQL Server & Microsoft ODBC Driver 18.x for SQL Server.
The certificate chain was issued by an authority that is not trusted
What is the fix for this error ?
Click through for some possible solutions.
Comments closedNikita Takru announces a new public preview:
We are thrilled to announce the Public Preview of Transparent Data Encryption (TDE) and Service-Managed Credential Rotation for Arc-enabled SQL Managed Instance. With a strong focus on data security and management, this release introduces cutting-edge features that ensure your sensitive information is protected.
Click through for more details, particularly on automating credential rotation.
Comments closedPostgreSQL is a robust open-source relational database management system that provides a wide range of capabilities to guarantee safe and effective data administration. One such privilege is the CREATEROLE privilege, which is vital to PostgreSQL database management of users and roles. We will examine the nuances of the CREATEROLE privilege, its use, and how it affects user administration in this blog post. So let’s investigate this privilege and comprehend PostgreSQL’s use of it.
Click through to see how this privilege works and what has changed in different versions of Postgres.
Comments closedKenneth Fisher needs more factory workers:
When you create a user (a database principal) you have several options on what the user is associated with (usually a login/server principal), or it might not be associated with anything at all (created without a login). And a common problem is when that object you’ve associated your user with is no longer available and you’ve got an orphan.
Click through to see how Kenneth finds them all and has them working in his wallet-making factories. Kenneth is an inspiration to us all.
Comments closedKeshav Kiran performs a migration:
One of our customers came up with a requirement where they wanted to Migrate On-prem Database to Azure SQL Managed instance. The databases had traditional column level encryption enabled.
He has restored the database on the SQL Managed instance by Backup/Restore approach. Now when he was trying to read the encrypted column on the destination database, It was showing NULL values after decryption.
Read on for the solution.
Comments closedAngela Henry takes it out of the user’s hands:
A little background for those new to using Power BI and Data Gateways. If the data source for your Power BI dataset lives on-prem or behind a private endpoint, you will need a Data Gateway to access the data. If you want to keep your data fresh (either using Direct Query or Import mode), but don’t want to rely on a specific user’s credentials (because we all want to go on vacation at some point), you will need to use a service principal for authentication.
Read on for the step-by-step instructions on how to do this.
Comments closedKenneth Fisher is trying to figure out where he left his keys:
I was having a conversation with some friends the other day and Jen McCown (blog|twitter) asked about SQL Server security references and “What’s something that’s really difficult in SQL Server Security.” As happens sometimes I started thinking about this in the back of my head and I realized something. The two absolute hardest things that people run into with security (at least in my ever so humble opinion) are
- Who: or Who am I when I try to access a resource?
- Where: or Where am I when I try to access a resource? And Where is that resource?
Read on for Kenneth’s thoughts.
Comments closedI ran into a concern to quickly audit all current users and role members for a set of Azure SQL databases, spread across multiple resource groups. Without an easy CMS concept or a way to quickly loop through an unknown set of servers, resource groups, and databases, that can be a little challenging. I have an account to use that should have access to all databases (but doesn’t) so put together some PowerShell that I could run locally to get that information and send the results to Excel.
Click through for a SQL script to get the data and a Powershell script to run this for each database and export the results into different tabs in Excel.
Comments closedPieter Vanhove makes an announcement:
A few months ago we announced the support for virtualization-based security (VBS) enclaves in Azure SQL Database. This announcement brings numerous advantages, including robust confidential queries and seamless cryptographic operations, to all Azure SQL Database offerings, independent from the underlying hardware. You can use the feature with any compute tier (provisioned or serverless), purchasing model (vCore or DTU), compute size and region that aligns with your workload needs. And, since VBS enclaves are available in existing hardware offerings, there is no additional cost.
In addition to this preview, we are excited to announce the preview of VBS enclaves in Azure SQL Database elastic pools!
Read on to learn more about how to enable enclaves and add a database to an elastic pool.
Comments closed