Contained Database

by Amol 12. March 2011 03:56

What is contained database and how to create it is explained in earlier post Using Contained Database

Here is SQL query to list out contained database users who can connect to it. Any user like windows, group user or SQL Server user contained in database can connect to contained database

SELECT       
     principal_id     
    ,name
    ,type_desc
    ,authentication_type_desc 
FROM db_Contained.sys.database_principals 
WHERE authentication_type IN (2, 3)

Tags: ,

SQL Server Denali

SQL Server Tools code-named “Juneau”

by Amol 7. March 2011 11:30

Good news for SQL Server Developers, SQL Server Developer tool named Juneau is coming out.

Juneau will be used as SQL Server Explorer to drill down into a database and get the same preview that SSMS provides. It will be used as text editior to execute SQL statements, debuging step over step.

Plenty of other cool things with Juneau check it out Here

Tags: , ,

SQL Server Denali

Using Contained Database

by Amol 5. March 2011 19:41

Why do we need Contained Database? Lets take an example, we develop application and database on our development machine. Now its time to deploy it on production. There are couple of ways to do it. One, generate database object script and create the database. Second, backup the database and restore it on production. These steps will create database on production but it wont create any dependant server level objects for example logins. With help of database database backup user will user will get created on production but it will be an orphand user. You have to map a login for the user. 

To avoid such ambiguities, Contained Database comes in picture. While creating database SQL Server Denali will gives you option of setting its containment type, which creates Contained objects defined at server level within database itself. Currently only containment option "Partial" is available in SQL Server Denali CTP 1. "Full" containment is not yet enabled in CTP 1.

Let's see how to create Contained Database. First you have enable contained databases server level option. To Enable contained databases open Server 

Properties as shown in figure. By default Contained Database property is False. To work on it make it True.

 

Or secondly you can enable this property by using T-SQL as well. 

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE
GO

Once you enabled contained databases, create contained database using create database statement shown in below.

CREATE DATABASE [TestContainedDB]
CONTAINMENT = PARTIAL
GO

Lets create user in TestContainedDB. 

USE [TestContainedDB]
GO
CREATE USER [TestUser] WITH PASSWORD = ‘password123’
GO

If you notice, earlier we required valid server login to create database user. But here we do not require to create SQL Server login for the database user. So dependency between database user and server login has been removed here. Means it all required objects containance in Contained Database.

This is useful when you need to move database from one SQL Server instance to another. You do not have to map the database users to login. 

Tags: , , ,

SQL Server Denali

Category

Recent Posts

Tag cloud