Pentest Documentation

Appearance

🔓 Permissions ​

Microsoft SQL Server (MSSQL) uses a robust permissions model to control access to database objects and operations.

📘 Types of MSSQL Permissions ​

Server-Level Permissions ​

Server-level permissions are assigned to logins and server roles. They control access to server-wide operations and resources.

  • Logins: Server-level principals that allow access to the SQL Server instance.
  • Server Roles: Groups of logins that share common permissions.

Database-Level Permissions ​

Database-level permissions are assigned to database users, database roles, and application roles. They control access to operations and resources within a specific database.

  • Users: Database-level principals that allow access to the database.
  • Database Roles: Groups of users that share common permissions. Examples include db_owner, db_datareader, and db_datawriter.
  • Application Roles: Special roles that allow applications to run with their own, predefined permissions.

Object-Level Permissions ​

Object-level permissions control access to specific database objects such as tables, views, procedures, and functions.

  • Permissions on Tables and Views: Include SELECT, INSERT, UPDATE, and DELETE.
  • Permissions on Stored Procedures and Functions: Include EXECUTE.
  • Permissions on Schemas: Include ALTER, CONTROL, and REFERENCES.

Special Server Roles ​

  • sysadmin

Members of the sysadmin server role have full control over the SQL Server instance. They can perform any activity, including configuration changes and management of all databases.

  • dbcreator

Members of the dbcreator server role can create, alter, drop, and restore databases.

  • securityadmin

Members of the securityadmin server role manage logins and their properties. They can also grant, deny, and revoke server-level permissions.

Special Database Roles ​

  • db_owner

Members of the db_owner database role have full control over the database. They can perform all configuration and maintenance activities on the database.

  • db_datareader

Members of the db_datareader role can read all data from all user tables in the database.

  • db_datawriter

Members of the db_datawriter role can add, delete, or change data in all user tables in the database.

👀 Viewing Permissions ​

To view the permissions granted to a principal, you can query system views.

sql
SELECT * FROM sys.server_permissions WHERE grantee_principal_id = USER_ID('qu35t');
sql
SELECT * FROM sys.database_permissions WHERE grantee_principal_id = USER_ID('qu35t');

🥷🏿 Impersonate Another Login ​

The EXECUTE AS LOGIN statement in Microsoft SQL Server allows to impersonate another login at the server level. This can be useful for testing and verifying permissions, troubleshooting access issues, or running queries and commands as if you were the impersonated login.

sql
SELECT grantor.name AS grantor_name, grantee.name AS grantee_name, permission_name, state_desc FROM sys.server_permissions AS perms JOIN sys.server_principals AS grantee ON perms.grantee_principal_id = grantee.principal_id JOIN sys.server_principals AS grantor ON perms.grantor_principal_id = grantor.principal_id WHERE permission_name = 'IMPERSONATE';
sql
exec_as_login sa