Principle of Least Privilege

An important DBA security management skill  is the principle of least privilege. According to BOL    “ a system should allow for only the required level of access to a securable object.”   Once you get your head around the concept , it  will be come one of the first considerations you’ll make every time there is a request for a security change.

A classic SQL Server security scenario: An inexperienced DBA creates a sql login, which by default doesn’t have any  privileges . The DBA notices no authority to any objects, so to get the application working – they assocatiate db_owner or sysadmin.   That could solve the immediate problem , but creates all sorts of problems in the future plus added risk of data corruption,data theft, and a whole range of intentional and accidental consequences.

For a practical application of Principle of least privilege Focus on :

1) Grant only necessary privileges. Giving the least mount of privileges required.

2) Roles – they will lessen the pain when it comes to  troubleshooting and auditing

3) Elevated prvileges within a database or server roles are lethal in the wrong hands. Even in the right hands , errors can easily occur. To safeguard the data , identify the privileges to get the job done. If temporary elevated privileges are required develop a system that temporarily elevates the users privilege and then reverses it automatically.

4)Regular sql server security audit to confirm on-going requirement

Read More on SQL Security management

SQL Server - Database Server Security Audit Process (SQL Server ...

Security audit for SQL public role - SQL Server DBA

SQL Server Security Violations Report for t-sql tuesday - SQL