Principals:
Principals are the entities that request security to SQL Server resources.
| Scope | Principals |
| SQL Server Level | SQL Server Login |
| Windows Level | Local user, Local group, Domain user, Domain group |
| Database Level | Database User, Database Role, Application Role |
Default Logins
- Every principal that is granted security to SQL Server must have an associated login.
- The login provides access to SQL Server and can be associated with principals that are scoped at the Windows and server levels
- These logins can be associated with Windows accounts, Windows groups, or SQL Server logins
- Logins are stored in the master database and can be granted permission to resources that are scoped at the server level.
- Logins provide the initial permission needed to access a SQL Server instance and allow you to grant access to the related databases.
- Permissions to specific database resources must be granted via a database user.
- The important thing to remember is that logins and users are directly related to each other but are different entities. It is possible to create a new login without creating an associated database user, but a new database user must have an associated login.
1. SA account
- The SA account is a SQL LOGIN that is assigned to the SysAdmin fixed-server role.
- The SA account cannot be removed, and it can always be used to gain access to SQL Server.
- The SA account should always have a strong password to prevent malicious attacks, and it should be used only by database administrators.
2. BUILTIN\Administrators login
- The login is made a member of the SysAdmin fixed-server role
- It is a windows group that corresponds to the local administrators group for the machine that SQL Server is running on.
3. NT AUTHORITY\SYSTEM login
- This account is related to the local system account that SQL Server services can run under.
- It is also added as a member of the sysadmin fixed-server role.
- This account can also be removed if the SQL Server services are not running with the local system account. This should be done with caution, however, because it can affect applications such as Reporting Services.
To better understand logins, you can look at the sys.server_principals catalog view. This view contains a row for every server-level principal, including each server login. The following example selects from this view and displays the results:
select left(name,25) name, type, type_desc from sys.server_principals AS log WHERE (log.type in (‘U’, ‘G’, ‘S’, ‘R’)) order by 3,1
Default Users
- DBO- default owner ( when login by administrator)
- Guest – ( drop it recommended )
- Information_Schema
- Sys -
dbo User
- The dbo user is the database owner and cannot be deleted from the database.
- Members of the Sysadmin server role are mapped to the dbo user in each database, which allows them to administer all databases.
- Objects owned by dbo that are part of the dbo schema can be referenced by the object name alone. When an object is referenced without a schema name, SQL Server first looks for the object in the default schema for the user that is connected. If the object is not in the user’s default schema, the object is retrieved from the dbo schema. Users can have a default schema that is set to dbo.
guest User
- The guest user is created by default in each database when the database is created. This account allows users that do not have a user account in the database to access the database.
- By defa-ult, the guest user does not have permission to connect to the database. To allow logins without a specific user account to connect to the database, you need to grant
- GRANT CONNECT TO GUEST
- The guest account is a special account and does not have an associated login. You can change the access for the guest account by using T-SQL commands instead.
- EXEC sp_addrolemember N’db_datareader’, N’guest’
INFORMATION_SCHEMA User
The INFORMATION_SCHEMA user owns all the information schema views that are installed in each database. it cannot be dropped from the database.
sys User
The sys account gives users access to system objects such as system tables, system views, extended stored procedures, and other objects that are part of the system catalog. The sys user owns these objects. Like the INFORMATION_SCHEMA user, it cannot be dropped from the database.
List All Users
SELECT left(u.name,25) AS [Name], type, left(type_desc,15) as type_desc FROM sys.database_principals AS u WHERE (u.type in (‘U’, ‘S’, ‘G’)) ORDER BY 1
View all object by user
SELECT name, object_id, schema_id, type_desc FROM sys.all_objects WHERE OBJECTPROPERTYEX(object_id, N'OwnerId') = USER_ID(N'sys') ORDER BY 1
User/Schema Separation
It is a collection of database objects that one user owns and that forms a single namespace. A single namespace is one in which each object name is unique and there are no duplicates.
ROLES
Permissions are applied to a role, and then members are added to the role. Any member of the role has all the permissions that the role has.
SQL Server has the following three types of roles:
1.Fixed-server and fixed-database roles
These roles are installed by default and have a predefined set of permissions.
2.User-defined roles
These roles are created in each database, with a custom set of permissions for each set of users assigned to it.
3.Application roles
These roles are a special roles that can be used to manage database access for an application.
Fixed-Server Roles ( Server levelsecurables )
| Role | Permission |
| Bulkadmin | Allowed to run the BULK INSERT statement. |
| Dbcreator | Allowed to CREATE, ALTER, DROP, and RESTORE any database. |
| Diskadmin | Allowed to manage disk files that are used by SQL Server. |
| Processadmin | Allowed to terminate SQL Server processes. |
| Securityadmin | Allowed to GRANT, DENY, and REVOKE permissions for logins at the server and database levels. Members of this role can reset passwords for SQL Server logins |
| Serveradmin | Allowed to change serverwide configuration properties and shut down the server, if needed. |
| Setupadmin | Allowed to add and remove linked servers and execute some system stored procedures. |
| Sysadmin | Allowed to perform any activity in the server. |
Fixed-Database Roles
| Role | Permission | |
| db_accessadmin | Allowed to add or remove database access for logins. | |
| db_backupoperator | Allowed to back up the database | |
| db_datareader | Allowed to read all user table data. | |
| db_datawriter | Allowed to change the data in all user tables. | |
| db_ddladmin | Allowed to run any Data Definition Language (DDL) command against the database. This includes commands to create, alter, and drop database objects. | |
| db_denydatareader | Denied the right to read all user table data. | |
| db_denydatawriter | Denied the right to change the data in any of the user tables. | |
| db_owner | Allowed to perform any action on the database. Members of the sysadmin fixed–server role are mapped to this database role | |
| db_securityadmin | Allowed to manage permissions for database users, including membership in roles. |
The public Role
- The public role is a special database role that is like a fixed-database role except that its permissions are not fixed. The permissions for this role can be altered.
- Every user in a database is automatically made a member of the public role and in turn receives any permissions that have been granted to the public role. Database users cannot be removed from the public role.
- The public role is similar in function to the guest user that is installed by default in each database. The difference is that the permissions granted to the guest user are used by any login that does not have a user account in the database. In this case, the login is allowed to enter the database via the guest account. In the case of the public role, the login has been added as a user of the database and in turn picks up any permissions that have been granted to the public role.
To view the permissions associated with the public role
SELECT top 5 g.name, object_name(major_id) as ‘Object’, permission_name from sys.database_permissions p join sys.database_principals g on p.grantee_principal_id = g.principal_id and g.name = ‘public’ order by 1,2
This SELECT utilizes two catalog views that contain security information. The SELECT returns only the first five permissions for the public role, but the TOP clause can be removed to return all the permissions.
Application Role
To obtain the permissions associated with the role, the connection must set the role and supply the password.
This is done using the stored procedure sp_setapprole. You set the role to the sales application role (with the password PassW0rd) as follows:
EXEC sp_setapprole ‘sales’, ‘PassW0rd’
You can also encrypt the password:
EXEC sp_setapprole ‘sales’, {ENCRYPT N ‘ PassW0rd’}, ‘odbc’
Tags: Pricipals