Administer database user accounts
Manage database roles.
- The ALTER ANY USER permission is a required A security principal on the database to be able to create database users.
- We can map a SQL Server login to only one database user in each database.
- We can map a single login to different users in different databases if we follow the “one database user per SQL Server login per database” rule.
- We can assign database-level permissions directly to database users.
Note: It is best practice to assign database-level permissions to database-level roles and then add database users to those roles.
So before we assign users to roles it is best to discover the permissions a role grants by using the T-SQL Stored Procedure sp_srvrolepermission
USE [MY_DB] GO CREATE USER [myLogin] FOR LOGIN [MyLogin] WITH THE DEFAULT SCHEMA=[dbo] GO
Or we could do it this way
USE [AdventureWorks2012] GO CREATE USER "myDomain\domain_group_a" FOR LOGIN "myDomain\domain_group_a"; GO
We can detect orphaned users in a database by using:
USE [AdventureWorks2012] GO sp_change_users_login @Action='Report'; GO
We can use the sp_change_users_login stored procedure to relink a database user with a SQL login.
There are 9 Fixed Database Roles
- db_owner – Assign this role to principals who need to perform all database configuration and management tasks. Role members are able to drop the database.
- db_securityadmin – Members of this role are able to manage the membership of fixed and flexible database-level roles. Principals who are members of this role can elevate their privileges to those functionally equivalent to the db_owner role.
- db_accessadmin – Assign this role to security principals who need to manage database access for logins.
- db_backupoperator - Members of this role can back up the database.
- db_ddladmin – Adding a principal to this role enables him or her to run any Data Definition Language (DDL) command in the database.
- db_datawriter - Assign this role when you want to enable the principal to insert, delete, or modify data in a database’s user tables.
- db_datareader - Members of this role can read all data from all user tables in a database.
- db_denydatawriter - Assign this role when you want to block a principal from inserting, altering, or deleting data from a database’s user tables.
- db_denydatareader - Assign this role when you want to block a principal from reading data stored within a database’s user tables.
USE [AdventureWorks2012]; GO EXEC sp_addrolemember 'db_datawriter', "contoso\domain_user_b"; GO
Flexible Database Role
To create a role, a principal either needs the CREATE ROLE permission on the database or must be a member of the db_securityadmin fixed database role.
USE [AdventureWorks2012]; GO CREATE ROLE TableCreator AUTHORIZATION [mydomain\my_designated_user]; GO
If you do not use the AUTHORIZATION clause, the role will be owned by the security principal who executed the CREATE ROLE statement
USE [AdventureWorks2012]; GO GRANT CREATE TABLE TO TableCreator; GO USE [AdventureWorks2012]; GO EXEC sp_addrolemember 'TableCreator', "mydomain\domain_user_b"; GO
The msdb system database has a set of special database roles in addition to the nine fixed database-level roles. These roles enable you to assign permissions associated with Integration Services, Data Collector, Server Groups, database mirroring, and Policy-Based Management.
- db_ssisadmin, db_ssisoperator, and db_ssisltduser
- dc_admin, dc_operator, and dc_proxy
- ServerGroupAdministratorRole and ServerGroupReaderRole
- SQLAgentOperatorRole, SQLAgentReaderRole, and SQLAgentUser
Configure contained users.
- A contained user is a database user who does not have a SQL Server login
- Contained users can connect to the contained database by specifying credentials in the connection string
Contained databases have no external dependencies. The primary benefit of a contained database is that you can move it to another instance, or even to an appropriately configured cloud provider, and have the database work without requiring additional configuration.
Contained databases do not use SQL logins but instead use contained users.
You can create contained users only after you enable contained database authentication at the instance level.
sp_configure 'show advanced', 1; RECONFIGURE WITH OVERRIDE; GO sp_configure 'contained database authentication', 1; RECONFIGURE WITH OVERRIDE; GO CREATE DATABASE partially_contained_db CONTAINMENT = PARTIAL; CREATE USER contained_user WITH PASSWORD = 'Pa$$w0rd';
To creating a partially contained database user mapped to the mydomain\contained_user_a domain account, execute the statement:
CREATE USER [mydomain\contained_user_a];
Grant database access with least privilege
To grant permissions to a specific application:
- A user interacts with a client application that connects to an instance with the user’s credentials.
- The application executes the sp_setapprole stored procedure, authenticating by using a password configured to be used by the application.
- When authenticated, the application role is enabled, and the connection uses the permissions assigned to the application role.
USE [AdventureWorks2012] GO CREATE APPLICATION ROLE app_role_alpha WITH PASSWORD = 'Pa$$w0rd'; GO
Context Switching Example:
execute as user = 'MyLogin'
The revert command will return the user pre execute as user command
Adding user to Roles
alter role [myRole] add member [myLogin]