In a previous post, I talked about the various types of principals in SQL Server. Let's have a further look in this post at permissions and at some of the hardcoded principals that ship with any installation of SQL Server.
Permissions are what allow principals (logins, users, roles, etc) to perform (or not perform) activities in SQL Server. Permissions are managed through three operations: grant, deny, and revoke (or GDR for short). A grant specifies that a principal (the grantee) is allowed to perform a specific operation; a deny specifies the reverse - that the principal (still referred to as grantee) should not be capable of performing a specific operation; finally, a revoke is simply a mechanism for erasing any previous grants or denies. The person that performs a GDR operation is referred to as the grantor, regardless of the type of operation (i.e. the grantor may perform a deny). All GDR operations have three main parts: they specify a permission name, a grantee name (the subject of the GDR operation), and optionally an entity name on which the permission takes effect (the securable - the object of the GDR operation). The securable is optional because in some cases it is implied by the permission. Sounds complicated? Let's walk through an example. Let's say Alice grants Bob the SELECT permission on table t. She would do this with a statement like:
grant SELECT on t to Bob
In this operation, Alice is the grantor (because she executes the statement), Bob is the grantee, SELECT is the permission name, and t is the securable - the object on which the SELECT permission is granted.
Depending on the scope of the securable, the permission granted is either a server permission or a database permission. When a server permission is GDRed, the securable is implicitly the server and it is not specified in the GDR statement. Grants and denies of server permissions are recorded in the catalog sys.server_permissions; grants and denies of database permissions are recorded in sys.database_permissions. Of course, a server permission can only be granted to a server principal and a database permission to a database principal (in the above example, Alice and Bob are users; if they would be only logins, without any corresponding users of the same names, the statement would be invalid). Also note that revokes, because they act as an eraser, are not recorded anywhere - they are just removing entries from these catalogs! The revoke operation is often misunderstood, but the simplest way to think about it is that is an eraser of grants or denies.
All this information gives you a good basic understanding of the permissions system. There is only one more rule that you should keep in mind at all times: denies prevail over grants. What this means is that if I am a member of two roles, one of which is granted a permission and one of which is denied that same permission, the end result is that the permission is denied to me, because the deny will take precedence over the grant. A caveat here: there is a special case when permission checks are completely bypassed - ownership chaining, which can allow a principal to access an object despite that principal being denied access to it. I'll comment on ownership chaining in a future post.
With the basics of permissions understood, let's look at some special SQL Server principals. The most notorious SQL Server login must be sa. sa is a SQL login administrator account that can be used if mixed authentication is enabled on SQL Server. sa has been infamous due to most people using weak passwords for it (often an empty password) and thus making their systems vulnerable to any attacker that could attempt a connection. The sa login is hardcoded to be a member of the sysadmin server role. sa and sysadmin membership should be regarded as representing ownership of the server system - they are the pinnacle of power in the SQL Server world! This means that the sa password should be chosen to be a strong password and that membership in the sysadmin role should not be granted around freely. sa is so powerful that you can't even deny him permissions. Because sa is builtin, most attackers are always attempting to break into this account first. SQL Server 2005 has added a number of new features that you can use to defend this account better from attacks:
1) If you don't use sa but you want to use SQL Authentication (if you don't want to use SQL authentication, you can just restrict authentication to Windows only mode), then you can disable sa using the ALTER LOGIN ... DISABLE command. A disabled login cannot be used for gaining access to SQL Server until it is enabled back again.
2) If you use sa, you should make sure that you keep password policy checks enforced for that account (this is the default behavior and something that you should keep for any login). Note that password policy checks can only be enforced if your OS is Windows 2003 or Windows Vista. Password policy checks will increase the difficulty of someone figuring out your password through brute force.
3) If you notice frequent failed attempts to connect as sa, you can rename the sa account - this will stop outright those attackers, because they'll now have to first figure out the name of a valid login to attack. You can rename sa using the ALTER LOGIN ... WITH NAME statement.
The next famous and often misunderstood principal is the database user dbo. I discussed about how users are mapped to logins via their SID values. The same holds for dbo, except the mapping of dbo is not done at user creation - dbo always exists since the database was created; instead, the login to which dbo maps is determined by what login is the owner of the database - dbo will always map to the login that is marked as the database owner. Here's a query that can be used to find who is the owner of the current database:
selectsuser_sname(sid) from sys.database_principals where principal_id = user_id('dbo')
The way to change the mapping of dbo to a login is by changing the database ownership. This can be done via sp_changedbowner or using the newer syntax of ALTER AUTHORIZATION. The owner of a database is also recorded in sys.databases; however, this information can become stale when moving a database from one system to another - to fix a stale entry you can always reissue a database ownership change command. The above query returning NULL, for example, would be an indication that the database was brought from another server where it was owned by a principal that doesn't exist in the current server.
The dbo denomination exists so that, within each database, the most powerful principal is clearly known. Same as sa was built in at server level, dbo is its database counterpart. Like sa, dbo is the most powerful user in a database and no permissions can be denied to him. dbo is a member of the db_owner database role, and these represent the equivalent of the sa/sysadmin pair at the database level. A final important note is that sa and sysadmin members will always map to dbo, regardless of what login is set as the database owner. Thus, dbo is a fuzzy concept that doesn't clearly identify a single principal mapped to it.
Next stop is guest; guest is a database user that was meant as a way to provide anonymous access to any database. This kind of access is however unrecommended and these days guest is disabled in all databases except some system databases. If guest is enabled, it basically allows any login that is not explicitly mapped in the database to a user, to connect to the database as guest (otherwise, the login would not be able to connect, unless sysadmin, database owner, etc). There is little point in allowing this kind of anonymous access, so by default, in user databases, guest is not granted connect permission, which effectively disables it. Note that it is not possible to effectively remove guest from a database - it cannot be dropped and attempting to drop it will just issue a REVOKE CONNECT command. You can check the permissions assigned to guest using the following query:
selectpermission_name, state_desc, object_name(major_id) as securable, user_name(grantor_principal_id) as grantor from sys.database_permissions where grantee_principal_id = user_id('guest')
Finally, to conclude this post, let's look at the public roles. There is a new public server role in SQL Server 2005, in addition to the public database role that existed earlier. Any server principal is implicitly a member of the public server role and any database principal is implicitly a member of the public database role. I say implicitly because the memberships are hardcoded in the system and they do not appear in the catalogs, nor can they be modified. These roles allow a simple mechanism to GDR permissions to every principal at server or database scope; you can think of them as meaning "everyone". The permissions associated with the public database role can be checked using the previous query, after replacing 'guest' with 'public'. For querying the public server role permissions, you can use the following query:
selectpermission_name, state_desc, suser_name(grantor_principal_id) as grantor from sys.server_permissions where grantee_principal_id = suser_id('public')
Keep in mind that permissions assigned to these roles by default are necessary for the good functioning of the system. You can revoke some of the default permissions, but that could break functionality. The grants should be inoffensive from a security point of view; if you feel otherwise, you should contact the SQL Server security team on the MSDN forums.