Free SQL Help for Devs, DBAs, and the Curious

Welcome to SQLSupport.org, your trusted resource for all things SQL. The Data Whisperer is back with another insightful post. This time, we're delving into the realm of SQL Server Contained Users and Cross-Database Access. If you've worked with SQL Server 2022, Azure SQL, or Microsoft Fabric, this topic should pique your interest. We'll also touch on how cutting-edge technologies like Delta Lake, OpenAI + SQL, and Databricks can enhance your database practices.

SQL Server 2022 introduced a number of new features, one of which is the concept of contained users. A contained user is a user that exists within the database and doesn't have a login on the server. This encapsulation makes the database portable and reduces the administrative overhead. To create a contained user, we use the following syntax:

CREATE USER [UserName] WITH PASSWORD = 'password';
 
GRANT SELECT ON [DatabaseName].[SchemaName].[TableName] TO [UserName];

Now, let’s talk about cross-database access. In the traditional SQL Server model, accessing objects in a different database involves creating users and permissions in both databases. However, with the introduction of Azure SQL and Microsoft Fabric, we can use Elastic Query, which allows querying across different databases in Azure SQL.

To create an external data source to another Azure SQL database, you need to create a database scoped credential and then an external data source. Here's how:

CREATE DATABASE SCOPED CREDENTIAL MyCredential WITH IDENTITY = 'UserName', Secret = 'password';

CREATE EXTERNAL DATA SOURCE MyDataSource WITH (TYPE = RDBMS, LOCATION = 'azure.database.windows.net', DATABASE_NAME = 'DatabaseName', CREDENTIAL = MyCredential);

In the era of big data, technologies like Delta Lake and Databricks are becoming increasingly important. Delta Lake is an open-source storage layer that brings reliability to data lakes. It provides ACID transactions and can handle petabyte-scale tables with billions of partitions and trillions of events. Databricks, on the other hand, is a platform that combines data engineering and data science capabilities.

SQL combined with OpenAI introduces a new level of interactivity with your database. It enables you to 'ask' your database questions in natural language and receive the results in SQL format. While this technology is still in the early stages, it's an exciting development that underscores the continued relevance and versatility of SQL.

In conclusion, the world of SQL is evolving, and with technologies like SQL Server 2022, Azure SQL, Microsoft Fabric, Delta Lake, OpenAI + SQL, and Databricks, we are set to take database management to the next level. Stay tuned for more insights from the Data Whisperer on how to leverage these technologies to optimize your database practices.