If you want to create a separate account for a database, you need this database to contain the database
- current (non-contained) database problems
Before describing what is a contained database, Let's see why the inclusion database appears. There are some problems with the current database, as follows:
1, some information is lost during database migration or deployment
when we migrate a database from one instance of SQL Server to another, such as login, Information such as work agents will not be migrated together. Because this information has a special purpose, it is a permanent instance of SQL Server since it was created. Recreating these tasks on a new instance of SQL Server will be a time-consuming and error-prone process.
2, moving from application development to deployment
deploying apps on server may encounter some obstacles, as there is a good chance that the environment does not match. For example, you might not have permission to create a login, a command-line utility such as "xp_cmdshell" might be disabled, and the database collation used in the application might be different from the initial (or possibly default) collation that the server originally specified.
3. Security Considerations for Application Management
managing and maintaining a single database is difficult because information such as login, job Agent, and so on is accessed across DB instances, so Allowing users to authorize the entire instance will cause unnecessary access to other databases, leading to a security risk.
- What is the contained database
, as its name, is a self-contained database. For example, it comes with all the database settings and metadata information that is required to create the database. It is independent of the SQL Server instance, has no external dependencies, and comes with the self-contained mechanism of the authorized user. Because it is independent of the database instance, the collation of the database is no longer an issue when deployed to a different server.
A contained database that retains all necessary information and objects in all databases, such as tables, functions, restrictions, schemas, types, and so on. It also contains application-level objects in all databases, such as login, Agent jobs, system settings, linked server information, and so on.
The benefit of this kind of database is that it can be easily moved from one server to another and can be used immediately without any additional configuration, because they have no external dependencies.
Create a containing database
Method 1: Use Management Studio to enable the contained database
1. In Object Explorer, right-click the server name, and then select Properties
2. On the Advanced page, in the Include section, set the Enable included Databases option to True (the default is "False")
3. Click OK.
Method 2: Use T-SQL to enable the contained database
--Enabled Advanced Optionssp_configure'Show Advanced',1;RECONFIGURE withOVERRIDE;Go--Enabled Database Containmentsp_configure'contained database authentication',1;RECONFIGURE withOVERRIDE;Go
To convert a non-contained database to a containing database
1. Right-click on the database and clicking its properties
2. In the "Options" column, select the containing type as "partial" and then click "Confirm".
This method sometimes does not complete the conversion, so you can try to use SQL to convert
Use [Master]goalter DATABASE testnoncontaineddb SET containment = Partialgo
You can create an account and password only after you have converted the database to a containing database
Use DBName GO CREATE USER [username] with PASSWORD=N'PASSWORD', default_schema=[ dbo ]GO
Once created, you can use this account to link the database, and this account will be migrated after the database is migrated.
Citation: 7477470
MSSQL creates a login account for a separate database
