Windows Authentication, SQL Server Authentication, Windows
Groups, Database Roles, Schema, and Application Roles are all aspects used to
manage SQL Server Security. So how do you use one or more of these items to
develop your SQL Server security architecture for an instance of SQL Server?
Is there a correct way or a wrong way for granting people access to SQL
Server? In this article, I will discuss the different options available
within SQL Server 2005 for managing security.
Windows verses SQL Server Authentication
For an application or a person to connect to an instance of
SQL Server, they need to have or be associated with a login that is defined in
the master database for the instance. A login can be either a Windows account
or SQL Server account. When a person connects via a Windows account this is
known as Windows Authentication, whereas when connecting using a SQL Server
account it is call SQL Server Authentication.
A Windows account can be one of four different types of
accounts. It can be a domain Windows account, a local Windows account, a
Windows domain group, or a local domain group. A domain Windows account is an
account that is defined to a Windows domain and that has a specific name like
“DJGREG”, where “DJ” is the domain and “GREG” is a uniquely identifiable name
within the domain. A domain Windows account is normally associated with an
individual person, but can also be defined for an application, or can be used
by multiple individuals. Windows accounts are defined to Active Directory.
A local Windows account is similar to a Windows account but
is defined locally on the SQL Server machine, and has the following naming
convention: <machine_name><login name>. So, if my SQL Server
machine name was “SSESQL01” then my local Windows account on that machine might
Another type of account is a Windows domain group. A
Windows domain group is a unique name you define to Active Directory, much like
a Windows account, but Windows domain groups can have members. A Windows
domain group member can be one or more domain Windows accounts or groups.
A local Windows group is similar to a Windows group but it
is defined locally on the SQL Server machine. Members of a local Windows group
can be a local Windows account, a local Windows group, a Windows domain
account, or a domain Windows group.
A SQL Server account is an account that is defined only
within SQL Server. It is not known to Active Directory. When defining the
account to SQL Server you provided a name and a password. With SQL Server
accounts, SQL Server maintains the password for the account. This is different
from a Windows account where the password is maintained by either Active
Directory for Windows domain accounts, or the local machine for local Windows
SQL Server Login
In order for a person or an application to connect to SQL Server,
it must have a login. A SQL Server login can be defined for a SQL Server
account, a Windows Account, a Windows Group or a local Windows Group. When you
define a SQL Server account, you are defining it in SQL Server so in effect you
are defining a SQL Server login. However, when you define a Windows
account/group it doesn’t automatically become a login to SQL Server, because
you are defining the Windows accounts and groups to Active Directory or the
local SQL Server machine. So for Windows accounts and groups you need to
specifically add them to SQL Server in order for them to become a SQL Server
To summarize, Windows Authentication is a login defined for
a domain Windows account, a domain Windows group, a local Windows account, or a
local Windows group, but a SQL Server Authenticated login can only be defined
for a SQL Server account. Only SQL Server logins have passwords that are
maintained by SQL Server. Windows account passwords are maintained by either
Active Directory or the local machine depending on the type of Windows
account. SQL Server Logins are created for SQL Server Accounts when they are
defined, whereas you need to create a SQL Server login for a Windows
account/group after it has been defined to Active Directory or the local SQL
Associating Permissions to Login
When a login is granted access to log in to SQL Server they
are only given access to log in but not access to any database or database
objects within SQL Server. In order for a login to use SQL Server resources,
they need to be specifically granted access to those resources. Permissions
can be granted at the Server Level, Database Level, Schema Level, Database Role
level, Application Role level or object level. I will describe each of these
Server Level Roles
Server level permissions allow logins access to perform
various tasks in SQL Server at the server level. Server level rights are given
to logins by making them members of server level roles. There are eight
different server level roles: bulkadmin, dbcreator, diskadmin, processadmin, public,
securityadmin, serveradmin, setupadmin, and sysadmin. Review Books Online for
more details about each one of these server roles, and what kinds of access a
login gets when they are a member of one of these roles. You would grant
logins access to these server roles when you want the login to have specific
server wide access. You are not able to add your own custom server wide roles
to SQL Server.
In order for a login to perform T-SQL statements against a database,
they need to be given access to that database. When a login is granted access
to a database a user within the database is identified and created. Each
database user name within a database can be associated with one and only one
login. Granting a login access to a database doesn’t give them any access to
objects within a database. You need to grant rights to use an object
specifically to a database user or they can inherit rights by being a member of
a role within the database.
Database Level Roles
A database role is a way to group users within a database,
and provided them access to database objects based on their role membership.
When you have multiple database users that are going to have the same rights,
it simplifies management if you use a database role. This way when you grant
or revoke rights to a database role within a database, all the database users
that are members of that role are affected by changes made to the database
You can define your own database roles to match your application
requirements. You would create different roles for each different set of
rights your application needs to control. If your application needs one set of
users to be able to only read the data, while another set of users need update
permissions then you would create two roles. One role might be named “Viewer”
and would be given only rights to “SELECT” data for each table within the
database. Then another role might be named “Update” for those that need to
“SELECT, INSERT, UPDATE, and DELETE” permissions to all database tables.
Using database roles simplifies security management.
Without database roles, you would need to assign permissions to each database
user. By using database roles, you can assign permissions to the appropriate
database role, and make users members of a database role to give them the
permissions of the database role. Using database roles provides an easy way to
identify, name, and manage the different grouping of database permissions.
Database Schema Level Permissions
You can use schema level permission to grant access to all
the objects within a schema at one time. Granting schema level permissions to
a role or database users can simplify the amount of security work you might
need to perform. If a user has a particular access like “SELECT” at the
schema level then this allows them to issue a “SELECT” statement against any
table within the schema. When a new object is added to a database schema the
database users get the same rights on the new object that they have been
granted at the schema level.
Using schema level permissions reduces the need to change
permissions each time an object is added to a schema. However, it also
automatically allows users access to any new object without specifically
granting them access. If your security policy requires that users be granted
specific access to objects, then using schema level permissions might violate
this kind of policy. So before you start granting schema level permissions
think about the future implications this might have as your schema changes over
Application roles are roles associated with an application,
and activated by an application. Application roles have no members, but rights
are associated to an application role. Accessing and using database objects
are granted to application roles just as they are to database roles, or users
in the database, but unlike database roles, application roles have a password.
Application roles are activated by a connection. To activate an application
role the connection would execute the sp_setapprole SP passing an application
role name and password. If a valid application role name and the associated
password are provided then the permissions associated with the Application Role
are enabled for the connection, and all rights associated with the user
connection are removed. Once the application role is enabled, the application
role rights remain active until the current connection is terminated.
Application roles are a great way of restricting database
users from accessing SQL Server directly (outside the application) with rights
created to support only a specific application. If all rights to a specific
database object were granted to an Application Role then a user would not be
able to gain these rights by login on to the SQL Server outside the
application. That is unless they know the application role name and password.
Therefore, if you plan to use Application Roles to ensure access is only gained
by using the application then you will need to consider how you plan to manage
the passwords for Application Roles.
Object Level Access
You don’t have to grant access to objects using roles or
schemas. You can grant access to a database user for each database object the
user needs to access. If your database has only a single database user then
granting access to each object is easier to set up than using a database role
to grant access. However, if you have many database users, granting access at
the object level to every user quickly becomes a management nightmare.
While you are designing your database, you need to determine
how to manage access to your database objects. You can either manage access at
the database user, database role, schema, or application role level. Depending
on the number of users, the number of different security views you need, or how
restrictive you want your security will help determine what kind of security
model you will implement. If you have a single database user then granting
access to the individual database users might be appropriate, but if you have a
handful or thousands of users, managing security at the database user can
become overwhelming very fast. Granting database role, and/or schema level
access make managing permissions much easier when there are multiple users and
multiple different security access needed. Application roles should be
considered as a way to tighten your security rules, especially when you don’t
want users to be accessing/updating your database outside of the application.
Consider all these different ways to manage security while you are designing
your application. A little thought up front will minimize the effort and
problems with your security model down the road.