Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
Is there code out there to audit users and their password compliance in SQL Server 2012 contained databases?

Answered Is there code out there to audit users and their password compliance in SQL Server 2012 contained databases?

  • Friday, May 17, 2013 4:46 PM
     
     
    Sign In to Vote
    0

    Is there code out there to audit users and their password compliance in SQL Server 2012 contained databases?  I have looked and I don't see anything.

    What is the best way to "audit the capabilities of the users and modules in contained databases"?

    For example, someone put this code together to check for blank or null passwords.  How would you do this for a contained database?


    --Password Compliance

    if (@@version like '%2000%' or @@version like '%7.00.%')
    begin
     insert into @passwords
      SELECT name FROM master.dbo.syslogins
      WHERE   (PWDCOMPARE('''', password)    = 1
            OR PWDCOMPARE('''', password, 1) = 1
            OR PWDCOMPARE(null, password)    = 1
            OR PWDCOMPARE(null, password, 1) = 1)
    end
    else
    begin
     insert into @passwords
      SELECT name FROM sys.sql_logins
      WHERE    PWDCOMPARE('''', password_hash)    = 1
            OR PWDCOMPARE('''', password_hash, 1) = 1
            OR PWDCOMPARE(null, password_hash)    = 1
            OR PWDCOMPARE(null, password_hash, 1) = 1
    end

    --select @@servername, user_name+'has a default password', @@date from @passwords

    if (select count(*) from @passwords) > 0
      set @passstatus = 3
    else
      set @passstatus = 0

     

    lcerni



    • Changed Type lcerni Friday, May 17, 2013 5:27 PM x
    • Edited by lcerni Friday, May 17, 2013 6:17 PM
    •  
     

All Replies

  • Tuesday, May 21, 2013 6:35 AM
     
     
    Sign In to Vote
    0
    password policy http://msdn.microsoft.com/en-us/library/ms161959.aspx
     
  • Tuesday, May 21, 2013 3:24 PM
     
     Answered
    Sign In to Vote
    0

    As you know PDWCOMPARE does not work against the passwords of contained database users which are not available in sys.syslogins. There is no equivalent for contained database users. The CREATE USER statement does not have the option to turn CHECK_POLICY off, so you should not be able to create users with passwords that do not match the password requirements of the computer.

    I haven't experimented with moving contained databases between computers that have different password requirements. So I suppose that a lax password policy on one computer could have a contained database user that gets moved to another computer with a stronger policy. The new policy wouldn't take affect until the password was changed. Again, I haven't tested that.

    I'll add a note to PWDCOMPARE so others don't spend any time looking for some contained database equivalent.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked As Answer by lcerni Thursday, May 23, 2013 2:33 PM
    •