Wednesday, January 14, 2009

Tip #28 – Create Users with SQL Statements

Users can be created using a system procedure by the administrator (adssys) or any user who has been granted ALTER permission on the data dictionary. With version 9.x you can also add users to the DB:Admin group to give them full administrative rights to the data dictionary.

To create a user use the sp_CreateUser system procedure. This procedure takes three parameters; Username, Password and Comment. This procedure will create the new user in the dictionary with the specified password and comment. Users must have a unique name otherwise a 5132 “Invalid Object Name” error will be returned.

Users can be removed from the database using the sp_DropUser system procedure. If the user does not exist a 5132 error will be returned otherwise the user is removed from the database.

SQL User System Procedures  You can also create and remove groups by using the sp_CreateGroup and sp_DropGroup respectively.

No comments: