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:

grantSELECTon 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:

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:

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:

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.

I had a request which has to be done on our production servers, but up to this time i had nor founded solution for that, pls kindly help me.

My requriement : We are having our Production Database (AMT) on the client servers, we just want to restrict the sa for the AMT Databases by giving only read and write permission, there is no other chance for us, hence the client needs sa password with them.

Great article, lcris – thanks! I do have one question you might be able to help with. With access to db-level securables being granted at the user level, how are cross-db accesses handled? An example would be writing a stored procedure, say [uspMySproc], in db [Alpha] that attempts to access a table in database [Beta]. I can grant execute access on uspMySproc to a db user in [Alpha], and the procedure can access all tables in it's own db ([Alpha]). However, since the user is at the db level, and users in one db are distinct from users in another db, how is it determined whether access to the table in [Beta] will be permitted? Does it map from the user in [Alpha] to the parent login and back down to that login's user in [Beta]? It seems like there should be a way to handle this type of situation, but I haven't yet found a helpful description of how the permissions would need to be configured to allow it to occur.

I couldn't access the video now, but I hope it's only momentarily inaccessible. It was mainly providing an overview of the theory demonstrated in the demo, but I hope I commented the script well enough to make it useful on its own. Maybe if I get some time, I'll create a new post with the material from the presentation.

I have been instructed by external auditors to remove execute permissions for 150 of the system stored procedures from the Public Role, as it poses a security risk.

I disagree with their instruction, as there must be a reason for these procs to be allocated to the public role to begin with. I see that you have mentioned that removing access could break functionality, which I agree with – would you perhaps have an official link to a msdn article or KB article to substantiate keeping execute permission allocated to the public role?

The explanation that these permissions are not dangerous and may break functionality if removed had been articulated by Al Comeau in his presentation on SQL Server Security Best Practices, which used to be available at the link I mentioned a few comments above – sadly the videos are gone now.

I would like to turn your request around and ask if you have found any MSDN article that says it is safe to strip the public role of its permissions and that in general it is safe to change system behavior. By default, the features and configuration we ship are meant to be safe, so you'll not see statements saying "this particular system behavior is safer than others and should not be disabled".

If you want to confirm all this, I suggest that you post on the SQL Server Security forum on MSDN:

The answers that you'll get there are as official as you can get when they come from the SQL Server Security team members (btw, I moved out of SQL Server in 2007, so you should take all I say as semi-official :))

Before SQL Server 2005, there might have been a higher risk around the permissions granted to the public role because there was no catalog security in place. But with catalog security in place, having execute permission on a procedure isn't enough if the caller doesn't also have permissions on the system objects being accessed by that procedure – thus, there are builtin restrictions around what public can do that weren't there in earlier versions. If you find that public is able to do more than he should, you should let the SQL Server team know about it, so they can fix the issue – I don't know of any such issue discovered since SQL Server 2005 shipped and until 2007 when I left the organization. Stripping public of its permissions is something we can't prevent you from doing, but it can break features or upgrades, so it's something we don't advise and you can only do at your own risk.

I know this is an old thread, but I had a question. I am in a large Corporation. We currently have the traditional setup DEV, PROD, UAT and DR. Our permission got mixed up with the W2k8 upgrade on the DEV server. I'm trying to get my access back as a developer. We have an Admin lan group set up which is set to public. Currenlty I am very limited as to what I can do, this was not the case on the orignal server. They seem reluctant to set the Lan group to SysAdmin. If this is not an option what would I need changed on this lan group so I can create db, create backups etc. thanks

Sorry, I do not understand your scenario and question. You mentioned being a developer – don't you have a database admin to manage your database setup? Why are you trying to do a dbadmin's job?

Also, if your question is not specifically about a feature but more about a particular database setup, you should ask it on the SQL Server security forums or call customer support to walk through your scenario.