| Author |
Topic |
|
skybvi
Posting Yak Master
193 Posts |
 Posted - 2011-12-01 : 10:23:46
|
| Hi, I want to set the expiration policy checked in for all the sql server logins and so I am running this syntax:---EXEC sp_configure 'allow updates', 1RECONFIGURE WITH OVERRIDEGO update sys.sql_logins set is_expiration_checked ='1'goBUT, I am getting this error:-Ad hoc updates to system catalogs are not allowed.Regards,SushantDBAWest Indies |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-01 : 10:30:29
|
| you cant update all system catalog views like this.For modifying expiration property for login you should be using ALTER LOGIN.... T-SQL statement or doing it from wizard------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-12-01 : 10:31:08
|
| Have a look at alter login In general it's a bad idea to try and update system tables (which aren't usually tables at all).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
skybvi
Posting Yak Master
193 Posts |
Posted - 2011-12-01 : 10:34:15
|
| Alter login...That script works for 1 login at a time...I want to change the properties of all sql server logins at one time ..That is why I ran that script..Regards,SushantDBAWest Indies |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-01 : 10:36:13
|
for changing all sql logins except sa to enable expiration you can generate script as followsselect 'ALTER LOGIN '+ name + ' WITH CHECK_EXPIRATION = ON ' from sys.sql_logins WHERE name <> 'sa' you can copy result of above statement and copy it in a new query window to executeyou should have ALTER LOGIN permission to run this------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
skybvi
Posting Yak Master
193 Posts |
Posted - 2011-12-01 : 11:47:06
|
| Cool..I think it would work perfectly.1 thing more I would like to ask you..The logins whcih are not complying on the above critriea (complex passwrod)...when I change the policy for them, will they be asked to create new password(at logon) as the policy requires complex password..OR it that they won't be asked until the password expires (45 days)Similarily, the logins whcih already have complex passwords, they WON'T be asked for complex password at login??Regards,SushantDBAWest Indies |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-01 : 12:02:56
|
quote:
Originally posted by skybvi
Cool..I think it would work perfectly.1 thing more I would like to ask you..The logins whcih are not complying on the above critriea (complex passwrod)...when I change the policy for them, will they be asked to create new password(at logon) as the policy requires complex password..OR it that they won't be asked until the password expires (45 days)Similarily, the logins whcih already have complex passwords, they WON'T be asked for complex password at login??Regards,SushantDBAWest Indies
if you're changing password policy, then they will prompted for new password next time if its not as per policy------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
skybvi
Posting Yak Master
193 Posts |
Posted - 2011-12-01 : 12:17:48
|
| OKThanks a lot visakhm and nigelrivett.Regards,SushantDBAWest Indies |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-01 : 12:22:07
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Ad hoc updates to system catalog are not allowed