SQL Server Security Concept
Concept about Logins, Users, group,Principles, Roles and Schema.
In SQL server security structure is
little complex from learning point of view.
Let's understand the relation among
logins, users, principles, roles, databases, schemas by a diagram:
 |
| SQL Security Concept |
Login:
Login is server level entity which is only used for authentication.
Login is not a user. Each login is mapped by only one user per database. It is
also possible that a login is not mapped with any users of database and
vice-verse.
Logins
and Users mapping\Associate diagram:
 |
| Login Concept |
From above diagram Login L1 is
mapped with user U1 of the database D1 as well as user U1 of the database D2.
In the same way Login L3 is mapped with user U2 of the database D1 and users U4
of the database D2. Login L2 has not mapped with any users of any databases. In
the same way users U3 has not mapped with any login of the sql server.
Each login must have default
database. So when any end user is logged
in SQL the server by using any login, it is first connected to their default
database of the login and then performs any operation as a mapped user in the
database of the login.
Types
of Login\Authentication mode in SQL server:
a. Windows authentication logins (Windows login)
b.
Mixed authentication (Window authentication and Sql
server authentication)
Important
point about Login:
-
Dbo is only user in all the databases where more
than one logins of sysadmin role are implicitly mapped if it has not mapped explicitly
to that database.
- Guest is only user in master, tempdb and msdb databases
where more than one logins of not sysadmin role are implicitly mapped if it has
not mapped explicitly to that database. Make sure Guest user is always disable in Model & all other user databases.
Query to Enable\Disable Guest User in a database:USE <DB_Name>
GO
-- Note: NEVER run this on production server
-- Enabling guest account
GRANT CONNECT TO guest
GO
--Disabling guest accountREVOKE CONNECT TO guest
GO
-
It is not possible to drop user dbo in all the
databases.
- It is not possible to drop the guest user only in
the master and tempdb databases.
-
Script to get all login in SQL Server:
SELECT
name AS
LoginName,
type_desc AS
LoginType,
default_database_name AS DefaultDatabase
FROM sys.server_principals
WHERE [Type] <> 'R'
ORDER BY Type
User:
User is used to assign database side permissions. We can assign specific
permissions to different users on different database objects.
Permission can be assigned to a user using Database roles also.
Users are mapped to logins and the mapping is expressed by the SID
property of logins and users. A login maps to a user in a database if
their SID values are identical. Depending on the type of login, we can
therefore have a categorization of users that mimics the above
categorization for logins; so, we have SQL users and Windows users and
the latter category consists of users mapped to Windows user logins or users mapped to Windows group logins.
So the summary is : A login provides access to the server and to further get access to a
database, a user mapped to the login must exist in the database.
Script to
get all users in the current database:
SELECT name
FROM sys.database_principals
WHERE [TYPE] IN ('S','U','G')
name
|
dbo
|
guest
|
INFORMATION_SCHEMA
|
sys
|
Amit-PC\Amit
|
Amit-PC\Administrator
|
NT AUTHORITY\NETWORK
|
Note: Each database must have user named INFORMATION_SCHEMA and
sys. These users cannot connect any database. It used for internal purposes.
Script to map
the login with user in the current database:
SELECT
DB_NAME() AS CurrentDatabaseName,
SP.name AS LoginName,
CASE
WHEN DP.name IS NULL
AND EXISTS(SELECT * FROM sys.server_role_members SRM INNER
JOIN sys.server_principals ISP ON
SRM.role_principal_id =
principal_id WHERE member_principal_id = SP.principal_id AND ISP.name = 'SYSADMIN') THEN 'dbo'
WHEN DP.name IS NULL
AND EXISTS(SELECT * FROM sys.database_permissions P INNER
JOIN sys.database_principals
DP
ON P.grantee_principal_id
= DP.principal_id
WHERE DP.name
= 'guest' AND P.permission_name
= 'CONNECT') THEN 'guest'
WHEN DP.name IS NULL THEN 'This login cannot
connect this database.'
ELSE DP.name
END AS
UserName
FROM sys.server_principals SP
LEFT JOIN sys.database_principals
DP
ON SP.sid = DP.sid
WHERE SP.[type] IN ('S','U','G')
Database permission:
These permissions which can be granted or revoked to database
level principles on a particular securable.
Scrip to
get permission granted to database principles:
SELECT
P.name AS PrincipleName,
P.type_desc
AS PricipleType,
class_desc AS
SecurableName,
permission_name AS
PermissionName,
state_desc AS
State
FROM sys.database_permissions DP
INNER JOIN sys.database_principals
P
ON DP.grantee_principal_id = P.principal_id
Roles
Roles are used to assign permission to different logins & Users. There are different types of Roles.
Server Role
Database Role
Custom Roles
1. Server Role: Server wide roles are used to assign server wide permissions to
different logins.
As we know role is set of permissions. Server roles are those
roles which are assigned to login. We
can assign more than one role to each login. Each login must have a role named
PUBLIC which cannot be removed.
Script to
get all the server roles in Sql Server:
SELECT
name AS
RoleName
FROM sys.server_principals
WHERE [Type] = 'R'
Script to
get all the assigned roles for given login:
SELECT
SP.name As RoleName
FROM SYS.SERVER_ROLE_MEMBERS SRM
INNER JOIN SYS.SERVER_PRINCIPALS
SP
ON SRM.role_principal_id =
SP.principal_id
INNER JOIN SYS.SERVER_PRINCIPALS
TSP
ON TSP.principal_id = SRM.member_principal_id
WHERE TSP.name = 'LoginName'
UNION ALL
SELECT 'Public'
SQL server has eight fixed server level roles in which not any
permission can be granted or revoked. All the logins must be members of server
role public which cannot be removed.
Unlike to fixed server role, permissions inside server role public can
be granted or revoked. Server
roles are:
S#
|
Role Name
|
1
|
public
|
2
|
sysadmin
|
3
|
securityadmin
|
4
|
serveradmin
|
5
|
setupadmin
|
6
|
processadmin
|
7
|
diskadmin
|
8
|
dbcreator
|
9
|
bulkadmin
|
In the same way we can also created user defined server role and
permission can be granted according our requirement.
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.
 |
| Server Roles |
2. Database 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.
 |
| Database Role |
3. Custom Roles
We can also create Custom Roles as per our requirements.
Syntax:
USE <DB_Name>
GO
Create role <Role_Name>;
GO
Example:
USE TestDB
GO
Create role UserSp_Role;
GO
-- Grant access to user created stored procedures
SELECT 'GRANT EXECUTE ON '+name+' TO UserSp_role;' from sys.procedures where name not like 'sp_MS%'
GO
--copy and run the output of the above query on this database.
EXEC sp_addrolemember N'UserSp_role', N'TestDB'
GO
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.
For example if we want to assign permissions
to particular user only on 15 tables, it is better to create a schema,
keep all 15 tables in the schema and assign permissions to the user schema
instead of assigning same set of permissions
in all the 15 tables one by one.
--Script to create a new schema:
CREATE SCHEMA ExactHelp
--Creating new table in the new schema
CREATE TABLE ExactHelp.tblTest(a INT)
--Script to get list of all schema in a the current database
SELECT name FROM sys.schemas
Each user
must have default schema to connect any database.
--Script to get the default schema of users:
SELECT name,default_schema_name
FROM sys.database_principals
WHERE [TYPE] IN ('S','U','G')
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.
Script to
get all the server principle in Sql Server:
SELECT
name AS
RoleName
FROM sys.server_principals
Typically, linked servers are used to handle distributed
queries. When a client application executes a distributed query through a
linked server, SQL Server parses the command and sends requests to OLE
DB. The rowset request may be in the form of executing a query against
the provider or opening a base table from the provider. 