Best Practices for Managing Contained Database Users and Permissions in Azure SQL

Question

Best Practices for Managing Contained Database Users and Permissions in Azure SQL

YERNAIDU SIRAPARAPU 125

Hi everyone,

I have created an Azure SQL Database using SQL Authentication.

To give my client access without sharing my admin credentials, I created a contained database user and granted them read/write access using the following script:

CREATE USER [ClientUser] WITH PASSWORD = 'StrongP@ssw0rd!';

ALTER ROLE db_datareader ADD MEMBER [ClientUser];

ALTER ROLE db_datawriter ADD MEMBER [ClientUser];

I have a few questions regarding best practices:

Credential Management:

What’s the recommended way to protect or rotate contained database user credentials that are shared with external clients?

Password Management:

Can the client change their own password or username using this contained account, or does this require the database administrator to do it?

Stored Procedure Execution:

I have several stored procedures across multiple schemas — raw, cln, cln_stg, agg, and config.

Can the contained user automatically execute these stored procedures, or do I need to explicitly grant EXECUTE permissions?

If explicit permission is required, which of the following is the best practice?

GRANT EXECUTE ON SCHEMA::[cln] TO [ClientUser];

-- or

GRANT EXECUTE TO [ClientUser];

Any advice or best practices for securely managing client access in this setup would be greatly appreciated.

Thanks in advance!

2 answers

Your answer

Answer 1

Credential Management:
- To protect or rotate contained database user credentials, it is recommended to periodically change passwords and use strong, unique passwords for each user. You can also implement policies that require users to change their passwords after a certain period. Additionally, consider using Azure Key Vault to manage and store secrets securely.
Password Management:
- A contained database user can change their own password using the ALTER USER statement. However, they cannot change their username; this requires the database administrator's intervention. The command for changing the password would look like this:
```
   ALTER USER [ClientUser] WITH PASSWORD = 'NewStrongP@ssw0rd!';
```
Stored Procedure Execution:
- Contained users do not have automatic permissions to execute stored procedures. You need to explicitly grant EXECUTE permissions on the stored procedures or the schema containing them. The best practice would be to grant execute permissions on the specific schema that contains the stored procedures, as this allows for better control and security. Therefore, the preferred command would be:
```
   GRANT EXECUTE ON SCHEMA::[cln] TO [ClientUser];
```
This approach limits the permissions to only what is necessary for the user, adhering to the principle of least privilege.

By following these best practices, you can securely manage client access in your Azure SQL Database setup.

Answer 2

Is that user ClientUser supposed to be shared by many persons? That's certainly not good practice. Each person should have an contained database user. Then you can add these users to a role, and grant all permissions to the role.

It can be disputable whether it is best practice to use SQL authentication. Entra Authentication is certainly preferable since that permits for MFA. But, granted, SQL authentication is simpler to set up.

Can the client change their own password or username using this contained account, or does this require the database administrator to do it?

If users have direct access to SQL, they can change the password themselves.

Can the contained user automatically execute these stored procedures, or do I need to explicitly grant EXECUTE permissions?

You need to grant permission.

If explicit permission is required, which of the following is the best practice? GRANT EXECUTE ON SCHEMA::[cln] TO [ClientUser]; -- or GRANT EXECUTE TO [ClientUser];

That depends. I don't know why you have multiple schemas in the first place. But I guess that since you are asking, that you did not intend them to be a security boundary. In that case you might as well use the latter.

Then again, maybe at some point in the future you may decide to introduce a Management schema which is only for you as admin, and where you don't want your client to hang around. From that perspective, it is better to grant permission on schema level. And if you only have a handful of schemas, that is manageable.

Share via

Best Practices for Managing Contained Database Users and Permissions in Azure SQL

2 answers

Your answer