Access Controls for SQL Server

Access Controls 

Note : Below access permissions are depends on your environment . It should not be same for your client environment here I am just giving the idea how it should be and the best practice.

SQL Server Install Folder Permissions

Modify the permissions to the folder where Microsoft SQL Server was installed. Assign the SQL Server service account Full Control. Remove the Users group’s permission.

SQL Server database instance directory permissions

Delete any files used for the installation or updating of the SQL server. Protect files in the <system drive>:\Program Files\Microsoft SQL Server\MSSQL.X\MSSQL\Install, e.g., sqlstp.log, sqlsp.log and setup.iss. Run any tool (Microsoft utility Killpwd ) or manually remove  passwords that were stored or logged during the installation or upgrade of SQL server.  (For SQL server 2005 and above this is not required).

SQL Server System Administrators

Remove the Local and Domain Administers from the SQL Server System Administrators role. The only local or domain group that should be present is the Domain\ABC-MSSQL-Admins domain group. No Developers, System Administers other than Operation DBA should admin access to SQL server database.

Operating System Guests Group

Deny database login for the Guests OS group. Do not grant object permissions to PUBLIC or GUEST. Do not assign predefined roles to PUBLIC or GUEST.

Fixed Server Roles

Only use the fixed server roles sysadmin, serveradmin, setupadmin etc, to support DBA activity. Do not assign fixed server roles to application database user accounts, application administrator accounts, application developer accounts or application roles.. Operations DBA will use  AD and SQL Role to govern user access to database vs adding users directly to database. Approval must be required for any Exception or deviation.

Using the GRANT Option

Do not assign the GRANT option of object permission to a user or role.

User-defined Database Roles

Only create user-defined database roles to assign permissions to objects in the database when a pre-defined database role does not supply the appropriate permissions to a group of users. i.e. \db_datareader, \db_datawriter,  \db_adw_dbo

Developer Permissions

Do not allow developers or service accounts db_ddladmin, db_securityadmin, or db_owner permissions in a production environment.

Linked or Remote Servers

Configure linked or remote servers to use Windows authentication. Allow linked server access only to those logins that need it.

SA Privileges

Make sure that SA privileges should not be granted to individuals or service accounts other than a DBA.