Seven steps to Welcome a Consultant.

In this post I want to talk about hard life of SQL Server consultants, who are hired by companies to perform SQL Server performance troubleshooting, and their interactions with SQL DBA and SYSADMIN.

That should be the guideline for SQL DBAs on how to setup  an access for temporary hired contractors.

Step #1. Mr. Consultant came to the office. Sysadmin created AD account to log to the network. SQL DBA creates Login for consultant to log in to the server.

If SQL Server allows "SQL Server Authentication" then SQL DBA can create local SQL Login for the new user:

USE [master]

GO

CREATE LOGIN [MrConsultant] WITH PASSWORD=N'MrConsultant20160815' MUST_CHANGE,

DEFAULT_DATABASE=[tempdb], CHECK_EXPIRATION=ON, CHECK_POLICY=ON 

GO

Just note that Default Database is "tempdb", not "master". That is a good practice for any new SQL Login account.

If "SQL Server Authentication" is not allowed, then DBA can create new Login based on AD account:

USE [master]

GO

CREATE LOGIN [Domain\MrConsultant] FROM WINDOWS WITH DEFAULT_DATABASE=[tempdb]

GO

At first, Mr. Contractor wants to look through the SQL Server Log:

EXEC sys.xp_readerrorlog;

However, it generates following error:
Msg 229, Level 14, State 5, Procedure xp_readerrorlog, Line 1 [Batch Start Line 39]
The EXECUTE permission was denied on the object 'xp_readerrorlog', database 'mssqlsystemresource', schema 'sys'.

In order to give the right to see Error Log DBA has to create user in "master" database and give rights to execute "xp_readerrorlog".

Step #2. Giving rights to execute "xp_readerrorlog".

USE [master]
GO
CREATE USER [MrConsultant] FOR LOGIN [MrConsultant]
GO
GRANT EXECUTE ON xp_readerrorlog TO [MrConsultant]
GO

Then Consultant tries to execute several commands to get an idea what is going on on the server:

SELECT * FROM sys.dm_exec_connections
SELECT TOP 10 * FROM sys.dm_exec_cached_plans
SELECT TOP 10 * FROM sys.dm_exec_query_stats
SELECT * FROM sys.dm_io_virtual_file_stats(NULL, NULL)
SELECT * FROM sys.dm_server_services
SELECT * FROM sys.dm_os_sys_info
SELECT * FROM sys.dm_os_windows_info
SELECT * FROM sys.dm_os_buffer_descriptors
SELECT * FROM sys.dm_os_sys_memory
SELECT * FROM sys.dm_os_process_memory
SELECT * FROM sys.dm_os_performance_counters

However, he/she immediately gets following error for each of these selects:
Msg 300, Level 14, State 1, Line 3
VIEW SERVER STATE permission was denied on object 'server', database 'master'.
Msg 297, Level 16, State 1, Line 3
The user does not have permission to perform this action.

So, SQL DBA has to solve the problem.

Step #3. Giving "VIEW SERVER STATE" permissions.

To give "VIEW SERVER STATE" permissions DBA has to execute following statement:

USE [master]
GO
GRANT VIEW SERVER STATE TO [MrConsultant];
GO

To see list of the databases, their sizes, file names, locations consultant has to run following query:

SELECT * FROM sys.master_files

However, there is nothing in return.
DBA has to grant permissions to see databases' definitions:

Step #4. Granting databases' definition access.

USE [master]
GO
GRANT VIEW ANY DEFINITION TO [MrConsultant];
GO

The main production database in the system is "AdventureWorks2014". Consultant tries to access that database, but gets following error:
Msg 916, Level 14, State 1, Line 20
The server principal "MrConsultant" is not able to access the database "AdventureWorks2014" under the current security context.

So, DBA has to permit consultant to access the database.

Step #5. Giving database access.

Administrator has to create a user for Consultant in the Database:

USE [AdventureWorks2014]
GO
CREATE USER [MrConsultant] FOR LOGIN [MrConsultant]
GO

In order to troubleshoot a query Mr Consultant runs following statement:

DBCC SHOW_STATISTICS('Person.Person','PK_Person_BusinessEntityID');

And gets following errors:
Msg 229, Level 14, State 5, Line 34
The SELECT permission was denied on the object 'Person', database 'AdventureWorks2014', schema 'Person'.
Msg 2557, Level 16, State 7, Line 34
User 'MrConsultant' does not have permission to run DBCC SHOW_STATISTICS for object 'Person.Person'.

Consultant needs to read objects within the database.

Step #6. Granting Selection access.

Administrator has to grant rights to "SELECT" for Consultant in the Database:

USE [AdventureWorks2014]
GO
GRANT SELECT TO [MrConsultant]
GO

SORRY, contractor have to be able to see your underlying data. If you have to keep the data secured, encrypt it.

Now Mr. Consultant wants to see actual query plan generated by a query:

SELECT * FROM Person.Person;

And gets an error:
Msg 262, Level 14, State 4, Line 30
SHOWPLAN permission denied in database 'AdventureWorks2014'.
DBA has to allow to see an Execution Plan.

Step #7. Granting Showplan access.

USE [AdventureWorks2014]
GO
GRANT SHOWPLAN TO [MrConsultant]
GO

From  this point the consultant has the most rights needed for performance and error troubleshooting.
He/she does not have any rights to:
- Insert/Update/Delete any record;
- Create/Alter/Drop any database object;
- Create/Drop Login or User;
- Grant/Deny/Revoke any permissions;
- Run DBCC commands such as "CHECKDB", "FREEPROCCACHE", "DROPCLEANBUFFERS", "PAGE", Etc.

That is the minimal level of trust you can maintain as a DBA with external contractor, which will allow him/her to perform the required research.

In case Contractor will be requested doing any changes DBA can assist with Object creation or giving individual permissions to the specific objects.

At the end will combine all scripts into one. If contractor needs access to more than one database you have to repeat last three steps for all databases he/she need the access:

USE [master]
GO
CREATE USER [MrConsultant] FOR LOGIN [MrConsultant];

GO
GRANT EXECUTE ON xp_readerrorlog TO [MrConsultant];

GO
GRANT VIEW SERVER STATE TO [MrConsultant];
GO
GRANT VIEW ANY DEFINITION TO [MrConsultant];
GO
USE [AdventureWorks2014]
GO
CREATE USER [MrConsultant] FOR LOGIN [MrConsultant];
GO
GRANT SELECT TO [MrConsultant];
GO
GRANT SHOWPLAN TO [MrConsultant];
GO

Step #Last One. After Consultant is done...

After consultant helped your company to solve a problem, do not forget to clean up after him/her:

USE [AdventureWorks2014]
GO
DROP USER [MrConsultant];
GO
USE [master]
GO
DROP USER [MrConsultant];
GO
DROP LOGIN [MrConsultant];
GO