How To Move Logins to Contained Database

 

You can find detailed information about Contained Database in the article “What is Contained Database

We can create a new Contained Database and create user as “SQL User with Password” instead of “Login”.

Well, is it possible to convert the existing logins to the contained user(SQL User with Password)?

You need password information to create a new Contained User. We can create a new login with a hash value password. But, we can not create contained user with a hash value password. We must set the password as clear text.

For this reason, in order to create the user with the same password and the same authorization, we need to run the following procedure in the related database.

There is three parameter in this procedure:

UserName: Login name you want to convert to Contained User(SQL User with Password).

Rename: The login name and the database user name that are connected to the login may have been created differently. To understand the difference between a login and a user, you should read the below article.

Difference Between Login and User in SQL Server

If you type ‘keep_name’ in the rename parameter, the contained user name will be the database user name, and if you type ‘copy_login_name’, the container user name will be the login name. If the login and database user names are the same, both options give the same result.

DisableLogin: Determining whether the login is disabled after processing. ‘disable_login’ and ‘do_not_disable_login’ are available.

Afer the procedure is run, you can connect to the database with its previous privileges. You can use the “What is Contained Database” article.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *