May 21, 2012 at 9:22 pm
Comments posted to this topic are about the item New features in SQL 2012, the contained database
May 22, 2012 at 4:10 am
Nice article Daniel. It is always good to know about new features. It would have been nice to put some more details in the topic like how contained databases can resolve the collation conflicts when creating/using temporary tables.
May 22, 2012 at 6:45 am
I suppose what you meant to say was the main idea is to remove the dependency?
May 22, 2012 at 7:59 am
Why do we want a database that can be accessed without a login?
Between functional/role-based logins for applications and trusted machines, I can't think of a reason for this other than to accommodate lazy developers. I assume the DBA should be guarding against lazy developers.
I hope I've misunderstood what you've shown here. Can you provide another few examples of why we should like this feature and how it would be used appropriately?
btw, I'm not disparaging lazy developers: I can relate, which is why I know I should do better while wearing the DBA hat.
May 22, 2012 at 9:21 am
Mike,
The logins are technically within the database at this point. It makes the database portable from instance to instance without having to clone the users from the original database including SIDs and hashed passwords like Microsoft sp_help_revlogin or SSIS Transfer logins tasks do.
With 2008 R2 and below, when a database is restored to an instance, unless the logins were created by using one of the other methods listed in this article, even if they have the same password the SIDs don't match and the login has no rights to the database. Normally this is fixed with sp_change_users_login after the fact.
This only mattered for SQL Authentication, AD carries the SIDs for Windows Authentication.
Now the user SQL login accounts port around with the databases when you restore them.
May 22, 2012 at 10:45 am
Daniel: Thank you for this article. I particularly appreciate that you took the time to explain the methods used prior to 2012 for transferring logins. For those of us who are not likely to actually see 2012 for many a year, that was a nice inclusion.
I wanted to read your article even though I do not have access to 2012 and likely will not for some time, because I am very interested in learning about the new features. While you did a great job of explaining what contained databases are as a concept, I would have wanted to learn more about the options. For example, why "= partial"? Only partial? What are we missing?
Also, is this feature only available with t-sql or is there a user-interface/check box option somewhere? If available in the SSMS GUI, I would have liked to have seen a picture in the article of how it looks. I do a lot of my work in code, but for the initial creating of a database, I usually just do that using the GUI. Even if I didn't create databases that way, it seems like knowing whether a database is "contained" or not would be something someone would want to be able to quickly look up - (actually, both in the GUI and by code).
Just some feedback. I was pleased with the article and appreciate you writing it.
May 22, 2012 at 2:35 pm
Yes, all this features can be done using GUI.
I added some links for more details
Use the references for more information.
May 22, 2012 at 2:39 pm
Daniel: Thanks for the reply and additional work! Very nice.
May 22, 2012 at 9:26 pm
Sounds very interesting for a self contained database, I have many business systems where I would find this valuable for DR.
Just wondering how this works if I need to join tables between different databases, or is that one of those cases where it is listed as unsupported?
thanks!
May 23, 2012 at 3:35 am
David in .AU (5/22/2012)
Just wondering how this works if I need to join tables between different databases, or is that one of those cases where it is listed as unsupported?
thanks!
yes, it is unsupported till now. If you are joining the tables from other databases then you are crossing the boundary of the database.
May 23, 2012 at 6:48 am
Divine Flame (5/23/2012)
David in .AU (5/22/2012)
Just wondering how this works if I need to join tables between different databases, or is that one of those cases where it is listed as unsupported?
thanks!
yes, it is unsupported till now. If you are joining the tables from other databases then you are crossing the boundary of the database.
Happens all the time though.... our development team prior to me created a separate database for every project and joined across databases all the time.
The real question is when you are authenticating to a single database via contained databases and need to join to table in another database, how do you do that? It's a valid question...
May 23, 2012 at 7:50 am
mtassin (5/22/2012)
The logins are technically within the database at this point. It makes the database portable from instance to instance without having to clone the users from the original database including SIDs and hashed passwords like Microsoft sp_help_revlogin or SSIS Transfer logins tasks do.With 2008 R2 and below, when a database is restored to an instance, unless the logins were created by using one of the other methods listed in this article, even if they have the same password the SIDs don't match and the login has no rights to the database. Normally this is fixed with sp_change_users_login after the fact.
This only mattered for SQL Authentication, AD carries the SIDs for Windows Authentication.
Now the user SQL login accounts port around with the databases when you restore them.
I'd be tempted to make this the default for database creation on a webserver where each hosted domain has an associated database. What are the downsides to using a self-contained database, either in this usage example or in more general practice ?
May 23, 2012 at 2:57 pm
So lets say that my application needs to connect. How do I construct the connection string?
ATBCharles Kincaid
May 24, 2012 at 10:52 am
Nice article. Thanks for sharing
We have created SSIS package to transfer logins from one server to other when we move the database. We have to do coding as well to retain the same password because transfer login doesn't retain the same password.
Thanks
May 24, 2012 at 12:49 pm
Thanks.
If you like the post, a rating to the article is apreciated.
🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply