Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Ad hoc updates to system catalogs are not allowed Expand / Collapse
Author
Message
Posted Thursday, December 01, 2011 8:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, February 09, 2014 9:25 PM
Points: 1,200, Visits: 3,236
Hi,
I want to set the expiration policy checked in for
all the sql server logins and so I am running this query:---

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,
Skybvi
Post #1214679
Posted Thursday, December 01, 2011 8:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:58 AM
Points: 5,077, Visits: 8,918
You can't make direct updates to system tables from 2005 onwards. Use ALTER LOGIN instead.

See here:
http://technet.microsoft.com/en-us/library/ms189631.aspx

John
Post #1214698
Posted Thursday, December 01, 2011 8:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, February 09, 2014 9:25 PM
Points: 1,200, Visits: 3,236
John Mitchell-245523 (12/1/2011)
You can't make direct updates to system tables from 2005 onwards. Use ALTER LOGIN instead.

See here:
http://technet.microsoft.com/en-us/library/ms189631.aspx

John


John, what about If i need to set the policy for all the sql logins and not just 1 like in an alter login
statement...?

Regards,
Skybvi
Post #1214699
Posted Thursday, December 01, 2011 8:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:58 AM
Points: 5,077, Visits: 8,918
The only way I know for existing logins is to execute the ALTER LOGIN statements one by one. You can generate them very easily something like this (pseudo code because I haven't checked the syntax):

SELECT 'ALTER LOGIN ' + name + ' SET CHECK_EXPIRATION ON'
FROM master.sys.server_principals
WHERE IsNTLogin = 0

John
Post #1214702
Posted Thursday, December 01, 2011 8:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, February 09, 2014 9:25 PM
Points: 1,200, Visits: 3,236
John Mitchell-245523 (12/1/2011)
The only way I know for existing logins is to execute the ALTER LOGIN statements one by one. You can generate them very easily something like this (pseudo code because I haven't checked the syntax):

SELECT 'ALTER LOGIN ' + name + ' SET CHECK_EXPIRATION ON'
FROM master.sys.server_principals
WHERE IsNTLogin = 0

John


What is IsNTLogin ??

Regards,
Skybvi
Post #1214704
Posted Thursday, December 01, 2011 9:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:58 AM
Points: 5,077, Visits: 8,918
Like I said, it's pseudo code, so you'll need to check the exact syntax of ALTER LOGIN and the structure of server_principals to see what the column names and the values in them are. It's just saying put a WHERE clause on so that you're not trying to set the expiration check for Windows logins.

John
Post #1214710
Posted Thursday, December 01, 2011 9:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, February 09, 2014 9:25 PM
Points: 1,200, Visits: 3,236
John Mitchell-245523 (12/1/2011)
Like I said, it's pseudo code, so you'll need to check the exact syntax of ALTER LOGIN and the structure of server_principals to see what the column names and the values in them are. It's just saying put a WHERE clause on so that you're not trying to set the expiration check for Windows logins.

John


I guess ( not sure) though that if i use sys.sql_logins, the logins will be of sql only and not of windows logins..
So is it that I can use sys.sql_logins ?

Regards,
Skybvi
Post #1214739
Posted Friday, December 02, 2011 3:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:58 AM
Points: 5,077, Visits: 8,918
Yes, I don't see why that shouldn't work.

John
Post #1215232
Posted Friday, December 02, 2011 7:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, February 09, 2014 9:25 PM
Points: 1,200, Visits: 3,236
John Mitchell-245523 (12/2/2011)
Yes, I don't see why that shouldn't work.

John


Cool
thanks a lot

Regards,
Skybvi
Post #1215346
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse