Users
A user identifier (ID) identifies a user within a database. All permissions and ownership of objects in the database are controlled by the user account. User accounts are specific to a database; the xyz user account in the sales database is different from the xyz user account in the inventory database, even though both accounts have the same ID. User IDs are defined by members of the db_owner fixed database role.
A login ID by itself does not give a user permissions to access objects in any databases. A login ID must be associated with a user ID in each database before anyone connecting with that login ID can access objects in the databases. If a login ID has not been explicitly associated with any user ID in a database, it is associated with the guest user ID. If a database has no guest user account, a login cannot access the database unless it has been associated with a valid user account.
When a user ID is defined, it is associated with a login ID. For example, a member of the db_owner role can associate the Microsoft® Windows® 2000 login NETDOMAIN\Joe with user ID abc in the sales database and user ID def in the employee database. The default is for the login ID and user ID to be the same.
This example shows giving a Windows 2000 account access to a database and associating the login with a user in the database:
USE master
GO
sp_grantlogin 'NETDOMAIN\Sue'
GO
sp_defaultdb @loginame = 'NETDOMAIN\Sue', defdb = 'sales'
GO
USE sales
GO
sp_grantdbaccess 'NETDOMAIN\Sue', 'Sue'
GO
In the sp_grantlogin statement, the Windows 2000 user NETDOMAIN\Sue is given access to Microsoft SQL Server™ 2000. The sp_defaultdb statement makes the sales database her default database. The sp_grantdbaccess statement gives the login NETDOMAIN\Sue access to the sales database and sets her user ID within sales to Sue.
This example shows defining a SQL Server login, assigning a default database, and associating the login with a user in the database:
USE master
GO
sp_addlogin @loginame = 'TempWorker', @password = 'fff', defdb = 'sales'
GO
USE sales
GO
sp_grantdbaccess 'TempWorker'
GO
The sp_addlogin statement defines a SQL Server login that will be used by various temporary workers. The statement also specifies the sales database as the default database for the login. The sp_grantdbaccess statement grants the TempWorker login access to the sales database; because no username is specified, it defaults to TempWorker.
A user in a database is identified by their user ID, not their login ID. For example, sa is a login account mapped to the special user account dbo (database owner) in every database. All the security-related Transact-SQL statements use the user ID as the security_name parameter. The administration and understanding of permissions is less confusing if the members of the sysadmin fixed server role and the db_owner fixed database role set up the system such that the login ID and user ID of each user are the same, but it is not a requirement.
The guest account is a special user account in SQL Server databases. If a user enters a USE database statement to access a database in which they are not associated with a user account, they are instead associated with the guest user.