Enables Sybase IQ Login Management for servers and modifies Sybase IQ user account information in the IQ_SYSTEM_LOGIN_INFO_TABLE system table.
call sp_iqmodifyadmin (‘{enable | disable}’, )
call sp_iqmodifyadmin ('option ', value [, 'server-name'])
sp_iqmodifyadmin ‘{enable | disable}'
sp_iqmodifyadmin ‘option ' , [value] [, 'server-name' ]
DBA authority required.
enable | disable Enables or disables Sybase IQ Login Management. Cannot be specified with the server-name argument.
option Name of the option to change:
user_connections Sets the maximum number of connections per user for new users. 0 means no limit is enforced. Cannot be specified with the server-name argument.
db_connections Sets the maximum number of connections to the database allowed on a server. Can be specified with the server-name argument. This serves as the default value for new users, but does not affect existing users’ settings. 0 means no limit is enforced.
password_expiration Sets the default number of days a password is valid. 0 means the password does not expire. Can only be set globally, not per server. Cannot be specified with the server-name argument.
password_warning Sets the number of days before a password expires that a warning is sent to the user console. Can only be set globally, not per server. Cannot be specified with the server-name argument.
value Value to which the named option is set. Values can be integers from 0 through 32767.
server-name Optional parameter allowed only when specifying db_connections. Values are the server name or all servers keyword. The latter removes server level settings and specifies the global default setting.
Enabling login management through sp_iqmodifylogin automatically
adds existing users to the Sybase IQ Login Management tables. New
users added with the GRANT CONNECT command after
a database upgrade are not automatically added
to the Sybase IQ Login Management tables.
Users added to the database with GRANT CONNECT after IQ Login Management has been enabled or a database upgrade has been done will not be managed by IQ Login Management until IQ Login Management is enabled again.
To identify such users, compare the output of SELECT * FROM SYSUSERPERM and sp_iqlistpasswordexpirations. Users who appear in the SYSUSERPERM table but not in the output of sp_iqlistpasswordexpirations are not managed by IQ Login Management. To manage these users with IQ Login Management, simply enable IQ Login Management again, as follows:
call sp_iqmodifyadmin('enable');
The following errors may occur. Causes are listed after each error.
Permission denied: You do not have permission to execute the procedure sp_iqmodifyadmin.
Cause: A user without DBA role tried to execute sp_iqmodifyadmin.
RAISERROR executed: "The number of connections allowed must specified and be greater than or equal to zero and less than or equal to 32767."
Cause: A value other than 0 through 32767 was entered for user_connections.
RAISERROR executed: "The number of connections allowed must specified and be greater than or equal to zero and less than or equal to 32767."
Cause: A value other than 0 through 32767 was entered for db_connections.
RAISERROR executed: "The number of days the password is valid must specified and be greater than or equal to zero and less than or equal to 32767."
Cause: A value other than 0 through 32767 was entered for password_expiration.
RAISERROR executed: "The number of days to warn before a password expires must specified and be greater than or equal to zero and less than or equal to 32767."
Cause: A value other than 0 through 32767 was entered for password_warning.
RAISERROR executed: "Invalid input was supplied to sp_iqmodifyadmin. Valid options are: enable, disable, user_connections, db_connections, password_warning, password_expiration."
Cause: Invalid data was entered somewhere in the command.
RAISERROR executed: "Server name <server name> not found."
Cause: server name value specified did not match a valid server name in IQ_MPX_INFO.
RAISERROR executed: "Server name parameter not allowed with this option."
Cause: server name value was specified for an option other than 'db_connections'
RAISERROR executed: "Server name parameter not allowed with this option in non-multiplex mode."
Cause: procedure was called with server name argument in a nonmultiplex environment
The following procedure calls set the maximum number of connections to the database at 200. They do not enable or disable Sybase IQ Login Management.
call sp_iqmodifyadmin ('db_connections', 200)
sp_iqmodifyadmin 'db_connections', 200
If Sybase IQ Login Management is not enabled, this change is not enforced.
The following statement changes the per-server connection limit to 20 on server master:
sp_iqmodifyadmin('user_connections', 20, 'master'
