May include but is not limited to: application roles, schema ownership, execution context, Windows vs. SQL authentication, permissions and database roles
Let’s start with the boring definitions: authentication is the act of identifying yourself, while authorization is that one when you gain access to resources, based on your identity.
SQL Server allows two types of authentication methods: Windows authentication allows you to connect SQL Server with an existing Windows account , while SQL Server authentication allows connections from anywhere – as long as you set it up this way (which is a bad idea). You should use Windows Authentication, because this way you make your life easier (don’t have to store passwords in config files to connect), but if it isn’t possible, SQL Server authentication is the way to go.
Now how to build up your authentication model – there’s an easy way to go – connect with a fixed application credential. This way you can control what the app can do in the database server. However, sometimes you need more granularity – let’s say you are interested in who did what. If you connect with a single application credential, you’ll won’t get user-detailed information. Then you should use built-in user accounts.
You can even impersonate other users: there are two ways to do this. The first is SETUSER, which is deprecated, and will be removed in the future, the second is the EXECUTE AS ‘username’ command. EXECUTE AS has two types, LOGIN which let s you impersonate on the level of the server, and USER which is in the level of the current database. You should note that when you impersonate another user, you’ll lose the extra rights you have – but gain those you don’t. When you have done with impersonation you should call REVERT to switch back to your default credentials.
Another useful feature to use is schema. A schema can be used to contain databases, stored procedures, etc. and is linked to the owner of the schema. You can create a schema by calling CREATE SCHEMA as long as you have the permission to do so. Then the schema is bound to you. If you’d like to move an item from a schema to another, then you need to call ALTER SCHEMA, and then the TRANSFER TABLE command with the name of the desired schema.
A good idea to further granulate the level of authorization is to create proxy users. These cannot be used to log in (they aren’t mapped to a specified LOGIN) but are very useful to grant and deny permissions to them. Then you can switch to their execution context anytime in when you need so.
For the end, the common question which drives me nuts sometimes: REVOKE or DENY?
REVOKE can be used to revoke previously granted permissions, but isn’t exclusive. If the user’s group has right to read a table, and you call REVOKE on the user, he/she will be still able to read that, because of the group settings. To eliminate this right, you should call DENY, and it’s gone!