Configure Database Owner Account - SQL-FineBuild/Common GitHub Wiki
| Previous Configure xp_cmdshell Proxy Account | Manual Configuration | Configure User Accounts Next |
|---|
FineBuild can configure a User Database Owner Account as part of the SQL Server install process.
A SQL Server login is created that should be used as the owner account for User databases.
In the past, it was considered best practice for all databases to be owned by the sa account. However, if the user database is owned by the sa account and has ownership chaining enabled, then users in the db_dbowner role in the user database will gain elevated privileges in the system databases. To avoid this, it is now considered best practice for all user databases to be owned by a low privilege account.
Additionally, if a set of user databases have ownership chaining enabled, then they should be owned by a different account to other user databases. This is to prevent users in the chained databases from gaining privileges in the unchained databases.
In order to identify which account should be used as the standard user database owner account, a Credential is created to hold this metadata. The Credential is used in a similar manner to an Endpoint. It allows code elsewhere in SQL FineBuild to find an unknown value (the DB owner account) by looking at a known value (the Credential) and extracting the account name.
Security Compliance
Database Owner Account configuration helps to reduce the impact of a Raised Priviledge attack. If you setup Security Compliance then Database Owner Account configuration will always be implemented.
FineBuild Configure Database Owner Account
The Database Owner Account configuration relates to Process Id 5CF. It is controlled by the parameters below:
| SQL Version | Parameter | FULL Build | WORKSTATION Build | CLIENT Build |
|---|---|---|---|---|
| SQL2019 | /SetupStdAccounts: | Yes | Yes | N/A |
| SQL2017 | /SetupStdAccounts: | Yes | Yes | N/A |
| SQL2016 | /SetupStdAccounts: | Yes | Yes | N/A |
| SQL2014 | /SetupStdAccounts: | Yes | Yes | N/A |
| SQL2012 | /SetupStdAccounts: | Yes | Yes | N/A |
| SQL2008R2 | /SetupStdAccounts: | Yes | Yes | N/A |
| SQL2008 | /SetupStdAccounts: | Yes | Yes | N/A |
| SQL2005 | /SetupStdAccounts: | Yes | Yes | N/A |
In order to maintain compatibility with older versions of SQL FineBuild, the parameter /ConfigStdAccounts: can also be used.
FineBuild also uses the following parameters to help Configure Database Owner Account:
| Parameter | Default Value | Description |
|---|---|---|
| /DBOwnerAccount: | DBOwner | Name of DB Owner account |
| /SAPwd: | (none) | Password for sa account |
FineBuild will automatically:
- Create a Credential for the user database owner account
- Create the user database owner account login
- Mark the account as Disabled to prevent people logging on with the account
- Change the ownership of all user databases that are owned by an account with Sysadmin rights so they are owned by the user database owner account
Manual Configure Database Owner Account
The following steps show what you would have to do for manual Database Owner Account configuration. FineBuild does all of this work for you automatically.
- Within a query window enter the following command to create the Credential, replacing DBOwnerAccount with the value used for the /DBOwnerAccount: parameter.
CREATE CREDENTIAL StandardDBOwner WITH IDENTITY='DBOwnerAccount'
- Enter the following command to create the user database owner account.
CREATE LOGIN [DBOwnerAccount] WITH PASSWORD='SAPwd', CHECK_POLICY=ON, CHECK_EXPIRATION=OFF, CREDENTIAL=StandardDBOwner
- Enter the following commands to ensure no-one can exploit the database ownership privileges that will be assigned to this account.
REVOKE CONNECT SQL TO [DBOwnerAccount]
ALTER LOGIN [DBOwnerAccount] DISABLE
Copyright FineBuild Team © 2011 - 2020. License and Acknowledgements
| Previous Configure xp_cmdshell Proxy Account | Top | Configure User Accounts Next |
|---|