[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 945
  • Last Modified:

SQL Update sys_logins table

Is there a way for me to update the sys.sql_logins table?  I would like to turn on password policy and expiration.  However, I do not want to do it one by one.  I would like to be able to do a mass update with user that does not have this on.  Any ideas?

Update sys.sql_logins
set is_policy_checked = 1,
      is_expiration_checked = 1
where is_policy_Checked = 1 or is_expiration_checked = 1

Getting the following error.

Ad hoc updates to system catalogs are not allowed.
0
holemania
Asked:
holemania
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
You aren't allowed to directly update the system tables.

Run this script, sending output to text, instead of grid:

select 'ALTER LOGIN ['+name+'] WITH CHECK_EXPIRATION=ON, CHECK_POLICY=ON'
from sys.sql_logins
where is_policy_Checked = 1 or is_expiration_checked = 1

Open in new window


Check your output, remove or modify any you don't need, copy that into a query window and run it.

Will do exactly the thing you want done without the direct update of the system catalog tables.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now