Articles‎ > ‎

SQL Server - Login. Users, Roles, Schema etc

Login

A login is required to access SQL server. Either Windows Login or SQL server login can be used to access SQL server.  A login  is used to assign server wide permissions. Permissions are assigned to a login using Roles.

For example, sysadmin is a server wide role. A login which is member of sysadmin role has permission to do anything in SQL server.

Such permissions which a login posses due to the member ship of a Role is know as Implied Permissions.

Users

Users are used to assign database wide permissions. We can assign specific permissions  to different users on different database objects. Permission can be assigned to a user using Database roles also.

Interestingly, a user can not access SQL server unless it is mapped to a login. In other words, every SQL server user should be mapped to a login. User access a SQL database using credentials of a login to which it is mapped.

Roles

Roles are used to assign permission to different logins & Users. There are different types of Roles.


Server wide Role
Database wide Role
Custom Roles


1. Server wide Role Server wide roles are used to assign server wide permissions to different logins. Server wide permissions are usually assigned using following built in server wide Roles.


bulkadmin
dbcreator
diskadmin
processadmin
securityadmin
serveradmin
setupadmin
sysadmin


sysadmin is a Role which gives full permission to a login to do anything in the server. sa is a login in SQL server which is member of sysadmin Role. So sa login can be used to connect and administer a SQL server if windows login can not be used. If we need to create another SQL server administrator, we can create a new SQL server login and add that login to sysadmin Role.

2. Database wide Role

Database Roles are used to assign Database wide permission to users. Database permissions are usually assigned using following built in Database Roles.


db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner
db_securityadmin
public


dbo is a user in every SQL database. dbo will be assigned the Role db_owner by default. This db_owner Role gives full permission to the dbo user in a SQL database.




3. Custom Roles

We can also create Custom Roles as per our requirements.

 Schema

Schema can be imagined as a container to hold objects in a SQL database.  For example, when we create  a table in a SQL database, we are creating it in a schema.

Every schema will be owned by a user or a Database wide Role. The user or Role who owns the schema will also own all objects inside that schema. This gives a flexible permission structure to SQL server  database objects.

If a schema is owned by a Role, every user who is member of that Role will also get ownership on that schema. Such a permission inherited by members of a role on a schema is also an Implied permission.

Main advantage of  schema is that users can be easily removed since they are not owning any database objects. Even if a user owns a schema, we can easily change ownership of that schema to another Role or user before deleting that user. Obviously, it is easier than changing ownership of every database objects as it was in SQL server versions prior to 2005.

Securables

Anything that can be secured in a SQL server can be called a Securable. In short, access to a securable can be restricted or regulated using SQL server authentication system.

Database, table, schema, login, User, Role etc are examples of securables.

Prinicipals

Any entity that can request access to a SQL server resource can be called as a Principal. SQL server login, Windows Login, database user etc are examples of principals.

Comments