Chapter 1: System Procedures

sp_addlogin

Description

Adds a new user account to Adaptive Server; specifies the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified login at creation.

Syntax

sp_addlogin loginame, passwd [, defdb] 
	[, deflanguage] [, fullname] [, passwdexp]
	[, minpwdlen] [, maxfailedlogins] [, auth_mech]

Parameters

loginame: is the user’s login name. Login names must conform to the rules for identifiers.

passwd: is the user’s password. Passwords must be at least 6 characters long. If you specify a shorter password, sp_addlogin returns an error message and exits. Enclose passwords that include characters besides A – Z, a – z, or 0 – 9 in quotation marks. Also enclose passwords that begin with 0-9 in quotation marks.

defdb: is the name of the default database assigned when a user logs into Adaptive Server. If you do not specify defdb, the default, master, is used.

deflanguage: is the official name of the default language assigned when a user logs into Adaptive Server. The Adaptive Server default language, defined by the default language id configuration parameter, is used if you do not specify deflanguage.

fullname: is the full name of the user who owns the login account. This can be used for documentation and identification purposes.

passwdexp: specifies the password expiration interval in days. It can be any value between 0 and 32767, inclusive.

minpwdlen: specifies the minimum password length required for that login. The values range between 0 and 30 characters.

maxfailedlogins: is the number of allowable failed login attempts. It can be any whole number between 0 and 32767.

auth_mech: defines the authentication mechanism.

Examples

Example 1

Creates an Adaptive Server login for “albert” with the password “longer1” and the default database corporate:

sp_addlogin albert, longer1, corporate

Example 2

Creates an Adaptive Server login for “claire.” Her password is “bleurouge,” her default database is public_db, and her default language is French:

sp_addlogin claire, bleurouge, public_db, french

Example 3

Creates an Adaptive Server login for “robertw.” His password is “terrible2.” his default database is public_db, and his full name is “Robert Willis.” Do not enclose null in quotes:

sp_addlogin robertw, terrible2, public_db, null, "Robert Willis"

Example 4

Creates a login for “susan” with a password of “wonderful,” a full name of “Susan B. Anthony,” and the server’s default database and language. Do not enclose null in quotes:

sp_addlogin susan, wonderful, null, null, "Susan B. Anthony"

Alternately, you can also use the following:

sp_addlogin susan, wonderful, @fullname="Susan B. Anthony"

Example 5

Configures the login “mylogin” to override global authentication mechanisms:

sp_addlogin mylogin, mypassword, @auth_mech = ASE

Usage

For ease of management, it is strongly recommended that all users’ Adaptive Server login names be the same as their operating system login names. This makes it easier to correlate audit data between the operating system and Adaptive Server. Otherwise, keep a record of the correspondence between operating system and server login names.
After assigning a default database to a user with sp_addlogin, the Database Owner or System Administrator must provide access to the database by executing sp_adduser or sp_addalias.
auth_mech can take the same values as sp_modify login "authenticate with" option.
Although a user can use sp_modifylogin to change his or her own default database at any time, a database cannot be used without permission from the Database Owner.
A user can use sp_password at any time to change his or her own password. A System Security Officer can use sp_password to change any user’s password.
A user can use sp_modifylogin to change his or her own default language. A System Administrator can use sp_modifylogin to change any user’s default language.
A user can use sp_modifylogin to change his or her own fullname. A System Administrator can use sp_modifylogin to change any user’s fullname.

Permissions

Only a System Security Officer can execute sp_addlogin.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect