Home

	Expand Search

Introducing Sybase Workplace SQL Server

Chapter 3: Using Workplace SQL Server

Assigning Logins, Users, and Permissions

Assigning Logins, Users, and Permissions

SQL Server security is based on a three-tiered system that separates server access, database access, and data access.

To be able to connect to a server, a person must have a login on the server. A login does not automatically give the person permission to access data or create database objects, just to connect. It consists of a login name and a password.

To give others access to a server, the Sybase system administrator (called the System Administrator) creates logins using either the SQL Server Manager create login command dialog box or the sp_addlogin system procedure from isql.

To be able to connect to a database, a person must be made a user and have a database user ID. The database username may or may not be the same as the person's login name. The System Administrator or the Database Owner (the person who created the database or was given ownership by the database creator) create database users. You can create users with either the SQL Server Manager create user command dialog box or the sp_adduser system procedure from isql.

In addition to individual users, the System Administrator or Database Owner can create a group of database users in SQL Server. The group is identified by a group name and provides a convenient way to grant and revoke permissions to more than one user in a single statement. For example, you can define a "managers" group or a "payroll" group, assign users to the group, and assign the same privileges to all the users in the group by assigning the privileges to the group as a whole. There is also a system-defined group named "all," of which all database users are members.

You can create a group using the SQL Server Manager create group command dialog box or the sp_addgroup system procedure from isql. When you add a user to the database, you can designate the groups that the user is a member of as one of the options in the sp_adduser system procedure or SQL Server Manager create user command dialog box.

Having a login and username gives an individual access to a server and a database, but it does not give automatic permission to select data from tables or views, modify data in tables, or execute stored procedures. To give a user permission to access a database object, the System Administrator or the owner of the database object (the user who created the object) must explicitly grant the user, or a group in which the user is a member, permission to access that object. They can grant and deny various permissions to other users to use specific database objects with either SQL Server Manager or the Transact-SQL grant and revoke commands from isql.