locked
CHECK_POLICY = OFF Contained CREATE USER Statement RRS feed

  • Question

  • Is there any way to turn off the password policy check when creating a new Contained User in SQL 2012? We are trying to set up existing accounts without changing any passwords for our upgrade process.
    Monday, October 22, 2012 11:04 PM

Answers

  • Hi Michelle,

    There’s no way to turn off the password policy check when creating a contained user in SQL Server 2012. CHECK_POLICY = { ON | OFF} applies to SQL Server logins only. It cannot apply to SQL Server users.

    But we have a workaround here. First we create a normal login with CHECK_POLICY = OFF, and then use the system procedure sp_migrate_user_to_contained to convert it to a contained database user with simple password. Please check the following code which works fine on my machine:

    sp_configure 'contained database authentication', 1;
    go
    reconfigure;
    go
    use master
    go
    alter database Test set containment = partial
    go
    create login tester02 with password = 'test', check_policy = off
    go
    use Test
    go 
    create user tester02 for login tester02
    go
    sp_migrate_user_to_contained @username = N'tester02',
                                 @rename = N'keep_name',
                                 @disablelogin = N'disable_login'


    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Best Regards,
    Ray Chen

    • Proposed as answer by Shulei Chen Tuesday, October 30, 2012 9:28 AM
    • Marked as answer by Shulei Chen Wednesday, October 31, 2012 9:42 AM
    Tuesday, October 23, 2012 7:13 AM

All replies

  • http://msdn.microsoft.com/en-us/library/ff929275.aspx

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Tuesday, October 23, 2012 6:30 AM
  • Hi Michelle,

    There’s no way to turn off the password policy check when creating a contained user in SQL Server 2012. CHECK_POLICY = { ON | OFF} applies to SQL Server logins only. It cannot apply to SQL Server users.

    But we have a workaround here. First we create a normal login with CHECK_POLICY = OFF, and then use the system procedure sp_migrate_user_to_contained to convert it to a contained database user with simple password. Please check the following code which works fine on my machine:

    sp_configure 'contained database authentication', 1;
    go
    reconfigure;
    go
    use master
    go
    alter database Test set containment = partial
    go
    create login tester02 with password = 'test', check_policy = off
    go
    use Test
    go 
    create user tester02 for login tester02
    go
    sp_migrate_user_to_contained @username = N'tester02',
                                 @rename = N'keep_name',
                                 @disablelogin = N'disable_login'


    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Best Regards,
    Ray Chen

    • Proposed as answer by Shulei Chen Tuesday, October 30, 2012 9:28 AM
    • Marked as answer by Shulei Chen Wednesday, October 31, 2012 9:42 AM
    Tuesday, October 23, 2012 7:13 AM
  • I don't think password policy is applied to contained users.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Tuesday, October 23, 2012 7:13 AM