Solved

SQL Update sys_logins table

Posted on 2013-11-21
1
841 Views
Last Modified: 2013-11-26
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
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 39667167
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question