SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Ad hoc updates to system catalog are not allowed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

skybvi
Posting Yak Master

Virgin Islands (United Kingdom)
193 Posts

Posted - 12/01/2011 :  10:23:46  Show Profile  Reply with Quote
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', 1
RECONFIGURE WITH OVERRIDE
GO
update sys.sql_logins
set is_expiration_checked ='1'
go


BUT, I am getting this error:-

Ad hoc updates to system catalogs are not allowed.


Regards,
Sushant
DBA
West Indies

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/01/2011 :  10:30:29  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/01/2011 :  10:31:08  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

skybvi
Posting Yak Master

Virgin Islands (United Kingdom)
193 Posts

Posted - 12/01/2011 :  10:34:15  Show Profile  Reply with Quote
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,
Sushant
DBA
West Indies
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/01/2011 :  10:36:13  Show Profile  Reply with Quote
for changing all sql logins except sa to enable expiration you can generate script as follows

select '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 execute
you should have ALTER LOGIN permission to run this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

skybvi
Posting Yak Master

Virgin Islands (United Kingdom)
193 Posts

Posted - 12/01/2011 :  11:47:06  Show Profile  Reply with Quote
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,
Sushant
DBA
West Indies
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/01/2011 :  12:02:56  Show Profile  Reply with Quote
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,
Sushant
DBA
West Indies


if you're changing password policy, then they will prompted for new password next time if its not as per policy

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

skybvi
Posting Yak Master

Virgin Islands (United Kingdom)
193 Posts

Posted - 12/01/2011 :  12:17:48  Show Profile  Reply with Quote
OK
Thanks a lot visakhm and nigelrivett.


Regards,
Sushant
DBA
West Indies

Edited by - skybvi on 12/01/2011 12:18:55
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/01/2011 :  12:22:07  Show Profile  Reply with Quote
wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000