After a user has been authenticated and allowed to log in to Microsoft® SQL Server™ using their login, a user account is required in each database the user needs to access. Requiring a user account in each database prevents users from being able to connect to SQL Server and access all the databases on a server.
For example, if a server contains a personnel database and a recruiting database, users who should be able to access the recruiting database but not the personnel database would have a user account created only in the recruiting database.
The user account in each database is used to apply security permissions for the objects (tables, views, stored procedures and so on) in that database. This user account can be mapped from Microsoft Windows NT® user accounts, Windows NT groups in which the user is a member, or SQL Server login accounts. If there is no account mapped directly, the user may be allowed to work in a database under the guest account, if one exists. The activities a user is allowed to perform are controlled by the permissions applied to the user account used to gain access to a database.
SQL Server accepts commands after a user has successfully gained access to a database. The user may enter ad hoc commands or choose menu options in an application. All activities a user performs in a database are communicated to SQL Server through Transact-SQL statements. When SQL Server receives a Transact-SQL statement, it ensures the user has permission to execute the statement in the database. If the user does not have the proper permissions, either to execute a statement or access an object used by the statement in the database, SQL Server returns a permissions error.