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:
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:
At first, Mr. Contractor wants to look through the SQL Server Log:
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".
Then Consultant tries to execute several commands to get an idea what is going on on the server:
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:
To see list of the databases, their sizes, file names, locations consultant has to run following query:
However, there is nothing in return.
DBA has to grant permissions to see databases' definitions:
Step #4. Granting databases' definition access.
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:
In order to troubleshoot a query Mr Consultant runs following statement:
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:
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:
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.
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: