Contained Database SQL Server

 Contained Database

A contained database is a database that is isolated from other databases and from the instance of SQL Server.

To use a contained database, it must first be enabled at the server level using the following command.

EXEC sp_configure 'contained database authentication', 1;

RECONFIGURE;

  • Easier Migration: Contained databases simplify the process of migrating databases between servers by eliminating the need to migrate server-level logins.
  • Self-Contained Management: They make it easier to manage all database settings within the database itself.

    • Create a Contained Database

    Once contained database authentication is enabled, you can create a new contained database by specifying the CONTAINMENT option as PARTIAL. Here’s an example

    -- Create a contained database

    CREATE DATABASE ContainedDB

    CONTAINMENT = PARTIAL;

    Create Contained Users in the Database

    Now that the contained database is created, you can add users directly within the database. There are two types of contained users:

    • SQL user with password
    • Windows user

    Connect to the Contained Database

    To connect to the contained database, specify the database name in the connection string or in the SQL Server Management Studio (SSMS) connection options. This allows the user to authenticate directly with the contained database.


    Limitations

    • Some server-level features are not supported within contained databases, such as cross-database queries that use three- or four-part naming conventions.
    • The feature is available only in certain editions of SQL Server (starting from SQL Server 2012).

    Comments

    Post a Comment

    Popular posts from this blog

    disable Instant File Initialization (IFI) in SQL Server

     To disable Instant File Initialization (IFI) in SQL Server, you need to revoke the "Perform Volume Maintenance Tasks" permission from the SQL Server service account. Here's how to do it: Step 1: Identify the SQL Server Service Account Open SQL Server Configuration Manager. Expand "SQL Server Services" and find the SQL Server instance you want to configure. Note the service account being used for the SQL Server service (e.g., NT Service\MSSQLSERVER , a domain account, or a local account). Step 2: Revoke "Perform Volume Maintenance Tasks" Permission Open the "Local Security Policy" management console: Press Windows + R , type secpol.msc , and press Enter . Navigate to "Local Policies" > "User Rights Assignment." Find the "Perform volume maintenance tasks" policy. Double-click on the policy , select the SQL Server service account, and click Remove . Click "Apply" and then OK to save the changes. ...
    Read more

    common questions related to updating patches, Cumulative Updates (CUs), Service Packs, and security patches for SQL Server

     Here are some common questions related to updating patches, Cumulative Updates (CUs), Service Packs, and security patches for SQL Server: 1. What is a Cumulative Update (CU) in SQL Server? Answer: A Cumulative Update (CU) is a package that contains hotfixes, updates, and improvements that have been released after the original release of a specific SQL Server version. Each CU is cumulative, meaning it includes all previous updates released for that version. 2. What is the difference between a Service Pack and a Cumulative Update? Answer: Service Pack: A Service Pack is a collection of updates, hotfixes, and new features that have been thoroughly tested and released together. Service Packs are less frequent than CUs and include significant changes. Cumulative Update: CUs are released more frequently than Service Packs (usually monthly or quarterly). They include all previous updates and address specific issues reported by customers. 3. How do you check the current patch level of ...
    Read more

    Azure Private Endpoint

    Azure Private Endpoint is a network interface that enables secure and private communication between resources within an Azure Virtual Network (VNet) and Azure services. It allows you to access Azure services, such as Azure Storage, Azure SQL Database, Azure Cosmos DB, Azure Data Lake Storage, and more, over a private network connection. Key features and benefits of Azure Private Endpoint include: Private Connectivity: It establishes a private and secure connection between the resources in your VNet and the Azure service. This connection avoids exposure to the public internet, enhancing security and minimizing the risk of data exposure. Secure Communication: All traffic between your VNet and the Azure service flows through the private endpoint, leveraging the Azure backbone network. This traffic stays within the Azure network and is protected from eavesdropping or interception. No Public IP Address: With a private endpoint, you don't need to assign a public IP address to access Azur...
    Read more