Post reply

Ad hoc updates to system catalogs are not allowed

  • December 1, 2011 at 8:28 am

    #247012

    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

    Regards
    Sushant Kumar
    MCTS,MCP

  • December 1, 2011 at 8:48 am

    #1415324

    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

  • December 1, 2011 at 8:51 am

    #1415325

    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

    Regards
    Sushant Kumar
    MCTS,MCP

  • December 1, 2011 at 8:56 am

    #1415328

    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

  • December 1, 2011 at 8:59 am

    #1415330

    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

    Regards
    Sushant Kumar
    MCTS,MCP

  • December 1, 2011 at 9:04 am

    #1415335

    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

  • December 1, 2011 at 9:29 am

    #1415359

    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

    Regards
    Sushant Kumar
    MCTS,MCP

  • December 2, 2011 at 3:29 am

    #1415780

    Yes, I don't see why that shouldn't work.

    John

  • December 2, 2011 at 7:22 am

    #1415879

    John Mitchell-245523 (12/2/2011)

    Yes, I don't see why that shouldn't work.

    John

    Cool

    thanks a lot

    Regards,

    Skybvi

    Regards
    Sushant Kumar
    MCTS,MCP

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply