Starting in SQL Server 2012 and in Azure SQL Database, Microsoft introduced the concept of a contained database. A contained database is a database that is completely un-reliant on the instance of SQL Server that hosts the database including the master database–theoretically this makes a database much easier to move between servers (you’ll note the absence of SQL Agent jobs from this post, that’s a difference problem.). One of the biggest benefit is that it allows database level authentication, so there no need to have user logins on server level.
Contained database now enables us to make a database more portable. I can backup and restore to any instance of SQL Server and the database will carry all its logins with it. There is no longer a need to script out all logins and create those at the server instance level for a user to connect to that restored database. I personally have run into the issue of missing logins at the instance level when restoring to another server. In these cases, I have had to go back and script out those logins to apply them to the new instance. You can see how in an emergency where the source server may not be available that not having access to those logins could present a real issue. This is also beneficial for databases that are members of Always On Availability Groups–you don’t have to create logins on each server.
In addition to portability contained databases allow us to expand control of login creation to more than just the database administrator or highly privileged user accounts. Traditional databases require you to create server level roles and server level permissions in order to grant database rights to a user. With contained databases you avoid this, database owner and users with an ALTER ANY USER permission can now control access to the database. One drawback is the database user account must be independently created in each database that the user will need which add a little more maintenance.
Below will show you how to enable this option at both the server and database levels. From there I will show you how to create user logins and what the difference is between traditional (non-contained) login accounts and contained users.
Enable at SERVER level
EXEC sys.sp_configure N'contained database authentication', N'1'
RECONFIGURE WITH OVERRIDE
Enable at DATABASE Level
Note the word “Partial” in the dropdown and script
The contained database feature is currently available only in a partially contained state. A partially contained database is a contained database that allows the use of uncontained features.
Use the sys.dm_db_uncontained_entities and sys.sql_modules (Transact-SQL) view to return information about uncontained objects or features. By determining the containment status of the elements of your database, you can discover what objects or features must be replaced or altered to promote containment.
ALTER DATABASE [AdventureWorks2016CTP3] SET CONTAINMENT = PARTIAL WITH NO_WAIT
To Add a User
Below you will note a few differences in syntax Traditional used the work LOGIN while Contained used USER. Also note that when adding or changing database permissions the ALTER statements are very difference. Traditional uses ROLE and MEMBER will Contained uses AUTHORIZATION and SCHEMA
Traditional NON-Contained, adding user and granting READ/WRITE to a database
CREATE LOGIN JoeShmo WITH PASSWORD = '1234Password';
CREATE USER [JoeShmo] FOR LOGIN [JoeShmo]
ALTER ROLE [db_datareader] ADD MEMBER [JoeShmo]
ALTER ROLE [db_datawriter] ADD MEMBER [JoeShmo]
Contained Database adding user and granting READ/WRITE to a database this works for both SQL Authentication and Windows.
CREATE USER JoeShmo WITH PASSWORD = '1234strong_password';
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [JoeShmo]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [JoeShmo]
If changing to Contained database and you want to convert all your Server Logins to contained database users Microsoft has given us a great script use. I have reposted it below. The example must be executed in the contained database.
DECLARE @username sysname ;
DECLARE user_cursor CURSOR
FROM sys.database_principals AS dp
JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;
FETCH NEXT FROM user_cursor INTO @username
WHILE @@FETCH_STATUS = 0
@username = @username,
@rename = N'keep_name',
@disablelogin = N'disable_login';
FETCH NEXT FROM user_cursor INTO @username
CLOSE user_cursor ;
DEALLOCATE user_cursor ;
Aside from the lack of support for MSDB, the one other issue I’ve run into with contained databases was an application that contained multiple databases supporting the applications but used SQL logins. In this case, it was a version of dynamics–with Windows logins this is easy–you simply create the login in each database and let Active Directory deal with the passwords. However, with contained databases the passwords are local to each database–so it’s a challenge to sync these accounts. With my current customer in this situation, we’ve reverted to server logins and used Dbatools to sync the passwords between servers.
I can think of many ways contained database can add benefits, I can’t wait to play around with it more.
Contained databases do not support replication, change data capture, or change tracking. Confirm the database does not use these features.
July 1st is a day all Microsoft Most Valuable Professionals (MVP) wait patiently at their computers hitting F5 over and over again waiting to find out if they have been renewed for the upcoming year. I am proud to announce that Denny Cherry and Associates once again have FOUR MVP’s on staff. Denny, Monica and Joey have all been renewed for 2018. John, being newer to the program, will not be up for renewal until the 2019 award cycle. Combine we have over 16 years of MVP program experience. Big mile stones were hit by both Denny and Joey. Denny was awarded his 10th and Joey reached his 5th. Congratulations to all!
Per Microsoft “Microsoft Most Valuable Professionals, or MVPs, are technology experts who passionately share their knowledge with the community. They are always on the “bleeding edge” and have an unstoppable urge to get their hands on new, exciting technologies. They have very deep knowledge of Microsoft products and services, while also being able to bring together diverse platforms, products and solutions, to solve real world problems. MVPs make up a global community of over 4,000 technical experts and community leaders across 90 countries and are driven by their passion, community spirit, and quest for knowledge.”
Ever seen the below error? Until this week I hadn’t. So, I figured I’d take a little time and introduce it to those that had not.
Error Description: Length of LOB data (65754) to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit
We ran into an issue with a customer this week, this error was flooding the error log. After a little digging I found it had to do with transactional replication (also applies to Change Data Capture) they had setup which included LOB data.
Per MSDN: The max text repl size option specifies the maximum size (in bytes) of text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data that can be added to a replicated column or captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement. The default value is 65536 bytes.
In the error above you can see it plainly states that the column’s LOB data nvarchar(max) in this case was 65754 bytes which was over the max default size of 65536. Which ironically is 64k. 64*1024 = 65536 (if you didn’t know). Adjusting the max text repl size for this server solved our issue. Below you can see the ways to change this value. For us changing it to the max value of 2147483647 bytes which is 2 GB was the way to go. If you don’t know the max value you can also set it to -1 which means no limit, the limit will be based on data type limits. Prior to the limit was 2GB.
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'max text repl size',2147483647;
- At the Server Level right click and go to Properties.
- Click the Advanced.
- Under Miscellaneous, change the Max Text Replication Size option to the desired value.
Once we made this change our problem was resolved.
I don’t usually write editorial opinion type blogs, but given recent events I thought it poignant to follow up on one of my tweets. Last week, I sent a tweet talking about DB Stack Exchange and my thoughts on the lack of respect in the responses to those who take the time ask questions in forums.
I had many ask as to what I was referring to specifically, including some representatives of the Microsoft MVP program. My response to them is that I don’t believe in mob reactions or smearing anyone’s name and chose not to reveal the details. My reason for posting that tweet was just to remind those in the community that we have all been a beginner once and none of us know all there is to know about SQL Server or technologies in general. It takes a lot of courage for some to take the time to ask a question and all our responses should be respectful of that.
Unfortunately, too many times I read “shouldn’t you already know that” kind of responses that make the person asking a question look dumb. I also read sarcastic responses that to me tells the readers you’re not taking them or your response seriously enough. This disappoints me and is not representative of what makes our community great. In some of tweet responses, I had more than one “newbie” tweet saying they no longer use certain forums as they feel the forum has become too toxic. They’ve felt belittled or their questions were just ignored.
Now, I have also heard the argument that some questions on forums can seem like homework questions and like they are asking you to do their work for them, which is cheating. Honestly to me that shouldn’t matter. However, for those take issue with it maybe more advantageous in your answer to ask the what have they tried so far, so you can guide them rather than give a rude answer. My opinion in this is that there will always be someone that will read the responses and learn something. Isn’t that what it’s all about? I do understand the frustration in that, I do. But if you think about it, we help each other daily with our own work, is that considered cheating? Regardless of what our answers are used for we are here to help lift and teach others not make them feel inferior.
I will happily say, that Stack Exchange is very much actively working on making the forums a friendlier environment and I applaud them for that. I had several of their moderators reach out to me to try to identify which post(s) I was referring to, so they could remove it or address the individual directly Again, I choose not to identify it but reiterated what my irritation was.
I want to make it clear and acknowledge how great of a resource DB Stack Exchange and other forums are. I very much appreciate those that take the time to respond and answer questions. Without those individuals that freely share their knowledge, many of us would be lost in the technology world. Thank you to those who do it with integrity and grace. For those who don’t, please consider making a change or just stop answering questions.
One last note. Everyone is entitled to their opinion and I respect all. Those who took the time to respond to me, thank you. Those who commented with arguing that I take things too serious or it’s not a big deal, I get it. It’s just not how I see it. I am not writing this to stir the pot, I am just following up with some thoughts. Thanks for listening.