ASA SQL User's Guide
Transact-SQL Compatibility
Adaptive Server architectures
There are some differences between the Adaptive Server Enterprise and Adaptive Server Anywhere models of users and groups.
In Adaptive Server Enterprise, users connect to a server, and each user requires a login ID and password to the server as well as a user ID for each database they want to access on that server. Each user of a database can only be a member of one group.
In Adaptive Server Anywhere, users connect directly to a database and do not require a separate login ID to the database server. Instead, each user receives a user ID and password on a database so they can use that database. Users can be members of many groups, and group hierarchies are allowed.
Both servers support user groups, so you can grant permissions to many users at one time. However, there are differences in the specifics of groups in the two servers. For example, Adaptive Server Enterprise allows each user to be a member of only one group, while Adaptive Server Anywhere has no such restriction. You should compare the documentation on users and groups in the two products for specific information.
Both Adaptive Server Enterprise and Adaptive Server Anywhere have a public group, for defining default permissions. Every user automatically becomes a member of the public group.
Adaptive Server Anywhere supports the following Adaptive Server Enterprise system procedures for managing users and groups.
For the arguments to each procedure, see Adaptive Server Enterprise system and catalog procedures.
| System procedure | Description |
|---|---|
| sp_addlogin | In Adaptive Server Enterprise, this adds a user to the server. In Adaptive Server Anywhere, this adds a user to a database. |
| sp_adduser | In Adaptive Server Enterprise and Adaptive Server Anywhere, this adds a user to a database. While this is a distinct task from sp_addlogin in Adaptive Server Enterprise, in Adaptive Server Anywhere, they are the same. |
| sp_addgroup | Adds a group to a database. |
| sp_changegroup | Adds a user to a group, or moves a user from one group to another. |
| sp_droplogin | In Adaptive Server Enterprise, removes a user from the server. In Adaptive Server Anywhere, removes a user from the database. |
| sp_dropuser | Removes a user from the database. |
| sp_dropgroup | Removes a group from the database. |
In Adaptive Server Enterprise, login IDs are server-wide. In Adaptive Server Anywhere, users belong to individual databases.
The Adaptive Server Enterprise and Adaptive Server Anywhere GRANT and REVOKE statements for granting permissions on individual database objects are very similar. Both allow SELECT, INSERT, DELETE, UPDATE, and REFERENCES permissions on database tables and views, and UPDATE permissions on selected columns of database tables. Both allow EXECUTE permissions to be granted on stored procedures.
For example, the following statement is valid in both Adaptive Server Enterprise and Adaptive Server Anywhere:
GRANT INSERT, DELETE ON TITLES TO MARY, SALES
This statement grants permission to use the INSERT and DELETE statements on the TITLES table to user MARY and to the SALES group.
Both Adaptive Server Anywhere and Adaptive Server Enterprise support the WITH GRANT OPTION clause, allowing the recipient of permissions to grant them in turn, although Adaptive Server Anywhere does not permit WITH GRANT OPTION to be used on a GRANT EXECUTE statement.
Adaptive Server Enterprise and Adaptive Server Anywhere use different models for database-wide user permissions. These are discussed in Users and groups. Adaptive Server Anywhere employs DBA permissions to allow a user full authority within a database. The System Administrator in Adaptive Server Enterprise enjoys this permission for all databases on a server. However, DBA authority on an Adaptive Server Anywhere database is different from the permissions of an Adaptive Server Enterprise Database Owner, who must use the Adaptive Server Enterprise SETUSER statement to gain permissions on objects owned by other users.
Adaptive Server Anywhere employs RESOURCE permissions to allow a user the right to create objects in a database. A closely corresponding Adaptive Server Enterprise permission is GRANT ALL, used by a Database Owner.