Home

	Expand Search

Transact-SQL User's Guide

Chapter 1 SQL Building Blocks

Adaptive Server login accounts

Adaptive Server login accounts

Each Adaptive Server user must have a login account identified by a unique login name and a password. The account is established by a System Security Officer. Login accounts have the following characteristics:

• A login name, unique on that server.

• A password.

• A default database (optional). If a default is defined, the user starts each Adaptive Server session in the defined database without having to issue the use command. If no default is defined, each session starts in the master database.

• A default language (optional). This specifies the language in which prompts and messages display. If a language is not defined, Adaptive Server's default language, which is set at installation, is used.

• A full name (optional). This is your full name, which can be useful for documentation and identification purposes.

Group membership

In Adaptive Server, you can use groups to grant and revoke permissions to more than one user at a time within a database. For example, if everyone who works in the "Sales" department needs access to certain tables, all of those users can be put into a group called "sales". The Database Owner can grant specific access permissions to that group rather than having to grant permission to each user individually.

A group is created within a database, not on the server as a whole. The Database Owner is responsible for creating groups and assigning users to them. You are always a member of the "public" group, which includes all users on Adaptive Server. You can also belong to one other group. You can use sp_helpuser to find out what group you are a member of:

sp_helpuser user_name

Role membership

In Adaptive Server, a System Security Officer can define and create roles as a convenient way to grant and revoke permissions to several users at a time, server-wide. For example, clerical staff may need to be able to insert and select from tables in several databases, but they may not need to update them. A System Security Officer could define a role called "clerical_user_role" and grant the role to everyone in the clerical staff. Database object owners could then grant "clerical_user_role" the required privileges.

Roles can be defined in a role hierarchy, where a role such as "office_manager_role" contains the "clerical_user_role". Users who are granted roles in a hierarchy automatically have all the permissions of the roles that are lower in the hierarchy. For example, the Office Manager can perform all the actions permitted for the clerical staff. Hierarchies can include either system or user-defined roles.

To find out more about roles assigned to you use:

• sp_displayroles - to find out all roles assigned to you, whether or not they are active.

• sp_activeroles - to find out which of your assigned roles are active. If you specify the expand_down parameter, Adaptive Server displays any roles contained within your currently active roles.

The syntax is:

sp_displayroles user_name

sp_activeroles expand_down

For more information about roles, see the System Administration Guide.

Getting information about your Adaptive Server account

You can get information about your own Adaptive Server login account by using:

sp_displaylogin

Adaptive Server returns the following information:

• Your server user ID

• Your login name

• Your full name

• Any roles granted to you (regardless of whether they are currently active)

• Whether your account is locked

• The date you last changed your password

Changing your password

It is a good idea to change your password periodically. The System Security Officer can configure Adaptive Server to require that you change your password at preset, regular intervals. If this is the case on your server, Adaptive Server notifies you when it is time to change your password.

If you use remote servers, you must change your password on all remote servers that you access before you change it on your local server. For more information, see "Changing your password on a remote server".

You can change your password at any time using sp_password:

sp_password old_passwd,
new_passwd

When you create a new password:

• It must be at least six bytes long.

• It can be any printable letters, numbers, or symbols.

• The maximum size for a password is 30 bytes. If your password exceeds 30 bytes, Adaptive Server uses only the first 30 characters.

When you specify a password, enclose it in quotation marks if:

• It includes characters other than A-Z, a-z, 0-9, _, #, valid single-byte or multibyte alphabetic characters, or accented alphabetic characters.

• It begins with a number 0-9.

The following example shows how to change the password "terrible2" to "3blindmice":

sp_password terrible2, "3blindmice"

A return status of 0 means that the password was changed. For more information about sp_password, see the Reference Manual.

Understanding remote logins

You can execute stored procedures on a remote Adaptive Server using RPCs if you have been granted access to the remote server and an appropriate database on that server. Remote execution of a stored procedure is a remote procedure call (RPC).

To obtain access to a remote server:

• The remote server must be known to your local server. This occurs when the System Security Officer executes sp_addserver.

• You must acquire a login on the remote server. This happens when the System Administrator executes sp_addremotelogin.

• You must be added as a user to the appropriate database on the remote server. This is accomplished when the Database Owner executes sp_adduser.

These procedures are discussed in the System Administration Guide.

When you can access the remote server, you can execute an RPC by qualifying the stored procedure name with the name of the remote server. For example, to execute sp_help on the GATEWAY server, enter:

GATEWAY...sp_help

Or, to fully qualify the name of the procedure, include the name of the database containing the procedure and the name of its owner:

GATEWAY.sybsystemprocs.dbo.sp_help

In addition, if a System Security Officer has set up the local and remote servers to use network-based security services, one or more of the following functions may be in effect when you execute RPCs:

• Mutual authentication - the local server authenticates the remote server by retrieving the credential of the remote server and verifying it with the security mechanism. With this service, the credentials of both servers are authenticated and verified.

• Message confidentiality via encryption - messages are encrypted when sent to the remote server, and results from the remote server are encrypted.

• Message integrity - messages between the servers are checked for tampering.

If you are using the unified login feature of network-based security, a System Security Officer can use sp_remoteoption on the remote server to establish you as a trusted user who does not need to supply a password to the remote server. If you are using Open Client(TM) Client-Library(TM)/C, you can use ct_remote_pwd to specify a password for the remote server.

For more information about network-based security services, see the System Administration Guide.

You must change your password on all remote servers that you access before you change it on your local server. If you change it on the local server first, when you issue the RPC to execute sp_password on the remote server, the command fails because your local and remote passwords do not match.

The syntax for changing your password on the remote server is:

remote_server...sp_password old_passwd, new_passwd 

For example:

GATEWAY...sp_password terrible2, "3blindmice"

For information on changing your password on the local server, review "Changing your password".