SQL Server Security: Users

As mentioned earlier, server access is provided by logins, but database access is provided by users. As users are database-specific , they are created in the user databases and stored in the sysusers table. When a user is created in a database, it must be mapped to a login to provide access. Typically, the user account and login would be the same, such as mapping the login "sales" to a user "sales"; however, they are separate objects, so this convention is not enforced. It should also be noted that the database "user" doesn't always represent a single person. As in the previous example, the user "sales" could be mapped to a Windows group login "sales," which could represent many actual Windows accounts. This allows you to dynamically manage potentially thousands of end users by manipulating only two SQL Server objects: the login and user .

Two users have special meaning in SQL Server: dbo and guest. The dbo user is created in every database, but guest must be manually created if required.

dbo

The dbo account is the database owner; therefore, it cannot be deleted from the database. Members of the Sysadmin server role are mapped to the dbo user in each database, which allows them to administer all databases. Objects owned by dbo can be referenced by the object name alone, such as example table1; objects with owners other than dbo or the current user must be referenced with the owner name , such as bob.table1. It is good practice for all objects in a database to be owned by dbo.

guest

The guest account is used to provide access to a database for logins that do not have a mapped user account in the database. When you create a guest account in a database (there isn't one by default), all logins that aren't mapped to a user get access through the guest account. Any permission granted to the guest account or the public role (of which all users, including guest, are members) will be granted to these logins. Use the guest account sparingly, and only in low security databases.