• Home
  • About Me
  • Contact Us
  • Gallery
  • Resume

WikiDBA

by Virendra Yaduvanshi – Microsoft SQL Server Database Architect | Consultant | Blogger | Specialist | DBA | Speaker
Stay updated via RSS

  • SQL Authors

  • Articles

  • Blog Stats

    wordpress visitor counter View My Stats
  • Search

  • Social

    • View YaduvanshiVirendra’s profile on Facebook
    • View v_yaduvanshi’s profile on Twitter
    • View virendrayaduvanshi’s profile on LinkedIn
  • wikidba

    wikidba

Contained Databases

Posted: July 1, 2013 by Virendra Yaduvanshi in Database Administrator
Tags: Contained Database. Migrating Servers, Securing Database, sp_change_users_login
2

As a DBA, its very frequent task with databases from the production environment to a lower or higher level environment such as QC,UAT or DEV or Server migration task. One very typical problem facing is to getting the correct logins and properly aligned with users within the database. Even in database backup/restore process, with restore it may be left with orphaned users that do not have a corresponding server login, or misaligned internal SID’s that need to fix using the sp_change_users_login system stored procedure. I means this process takes multiple rounds to get everything in synced order and we can face users reports that particular login is not working…,

To resolve this problem, In SQL SERVER 2012, new feature Contained Database introduced. This enables a user in a database to exist without a corresponding login on the server. For security this makes the database more portable in the event where task like migrating servers, or restoring lower level environments.

To enable Contained Database feature, we have to change some Instance level setting with T-SQL or SSMS. Let see TSQL example

sp_configure ‘show advanced options’,1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure ‘contained database authentication’,1
GO
RECONFIGURE WITH OVERRIDE
GO

From SSMS, right click on the instance name and select the “Properties” option.  Once the Server Properties interface loads, choose the “Advanced” option in the left navigation then set the “Enable Contained Databases” to “true”.


To create new Contained Database, T-SQL syntax is

CREATE DATABASE VirendraTest
CONTAINMENT = PARTIAL
ON PRIMARY ( NAME = N’VirendraTest’, FILENAME = N’D:\TESTDB\VirendraTest.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG 
ON ( NAME = N’VirendraTest_log’, FILENAME = N’C:\TESTDB\VirendraTest_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
GO

And from SSMS, right click the “Databases” folder and select the “New Database” option.  This will load the New Database interface.  On this interface go to the “Options” tab and for the “Containment” dropdown select the “Partial” option.


To change existing database as Contained database, T-SQL syntax is

USE [Master]
GO

ALTER DATABASE VirendraTest SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

From SSMS, right click on the database and select the “Properties” option which will load the Database Properties interface.  In this interface select the “Options” menu and for the “Containment Type” option select  “Partial” from the drop down list.


Partially Contained Databases are beneficial in case of

1) Database Movement
2) Initial Database Development
3) Contained Database Users with AlwaysOn
4) Database Administration
Maintaining database settings in the database, instead of in the master database, lets each database owner have more control over their database, without giving the database owner sysadmin permission.

Limitation of Contained Database are its don’t support replication, change data capture, or change tracking, numbered procedures, schema-bound objects that depend on built-in functions with collation changes and binding changes resulting from collation changes.


Share this:

  • Facebook
  • LinkedIn
  • Twitter
  • Tumblr
  • Print
  • Email

Like this:

Like Loading...

Related

Comments
  1. Amol Raikar says:
    July 5, 2013 at 8:53 am

    Hi sir can I Installed sql server 2008 in linex On Jul 1, 2013 6:39 PM, “Virendra Yaduvanshi” wrote:

    > ** > Virendra Yaduvanshi posted: “As a DBA, its very frequent task with > databases from the production environment to a lower or higher level > environment such as QC,UAT or DEV or Server migration task. One very > typical problem facing is to getting the correct logins and properly > aligned w”

    LikeLike

    Reply
  2. Virendra Yaduvanshi says:
    July 8, 2013 at 5:04 pm

    Hi Amol,
    Sorry for late reply as i was busy on others projects,
    You can’t install MS SQL server on Linux, but you can connect it via java ODBC connector in linux environment.

    LikeLike

    Reply

Leave a Reply Cancel reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

SQL SERVER – Check If Column Exists in SQL Server Table
Connecting SQL Server from different domain

  • Upcoming Events

    No upcoming events

  • Follow WikiDBA on WordPress.com
  • Virendra Yaduvanshi

    Sector - 46, Gurgaon
    +91 8130396263
    Lunch: 11am - 2pm
    Dinner: M-Th 5pm - 11pm, Fri-Sat:5pm - 1am
Create a free website or blog at WordPress.com.
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
%d bloggers like this: