SQL Login and User for Performance Tuning

I've run into an interesting dilemma: as a new staff member in a security conscious organisation, I can't really get any work done. There is, however, a lot of work a DBA can do in terms of analyzing SQL Server instances right from day one. There are lots of best practice recommendations that can be made that are application or db design agnostic. Alternately, it can be possible to investigate the database design without access to the underlying data itself. With that all in mind, I have put some scripts together that creates login/user permissions and roles that can give a SQL consultant useful, but security minded, access to a SQL instance.

I will break down my scripts into two: 2005-2008R2 and 2012-2014. The main reason for this is down to the introduction of User Defined Server Roles in SQL Server 2012. So, there will be some duplication between the two sets of scripts below.

SQL Server 2005 to 2008R2

First we need to execute the Server level scripts
If the SQL login(s) doesn't yet exist execute the following for as many logins as needed:

Now, give the appropriate login(s) the server level permissions

Second the database level scripts
Create the user defined database roles for master and your user database(s).
For the master database the script checks for some common diagnostic stored procs, like sp_blitz and sp_whoisactive and grants exec rights to the database role.

For the user database(s) the new db role is granted the showplan privilege and, optionally, added to the db_datareader role.

For msdb the user is given membership to the db_datareader db role

SQL Server 2012 to 2014

First we need to execute the Server level scripts

With SQL Server 2012 and above, we can now create User Defined Server Roles. The script below creates a new server role with connect to sql, view server state and view any definition permissions granted. And the login(s) created is added as a member.

Second the database level scripts

These are the same as those defined above. First, create the user defined database roles for master and your user database(s).

For the master database the script checks for some common diagnostic stored procs, like sp_blitz and sp_whoisactive and grants exec rights to the database role.

For the user database(s) the new db role is granted the showplan privilege and, optionally, added to the db_datareader role.

For msdb the user is given membership to the db_datareader db role