System.Data.SqlClient.SqlException: User does not have permission to perform this action.

A quick post around granting administrators  to the search service application in SharePoint 2013. I used AutoSPInstaller to install and configure a set of SharePoint Lab,Dev,Test and Prod SharePoint environments.

Fast forward a few months and I needed to grant a user the ability to administer the SharePoint Search Service Application by adding them to Administrators via central administration:

2015-12-16_11-52-54

but when I tried to grant the permission, I received the following error message:

User does not have permission to perform this action.

Tracing it back to the ULS Logs, this was the message:

System.Data.SqlClient.SqlException (0x80131904): User does not have permission to perform this action.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at Microsoft.Office.Server.Data.SqlSession.ExecuteNonQuery(SqlCommand command)

After referencing this post with a similar stack trace:

Link

which identified that fact that I had used AutoSPInstaller to install and configure SharePoint (and the search databases).

A little experimentation and it turned out the solution was the the farm account needs to be DB owner on each Search database (all 4). This obviously makes sense but I used the following SQL command to grant those permissions: (notice my filter on the db name to only include the search databases)

use master;
go
DECLARE Databases_CTE Cursor
FOR
— Selecting user database names.
select Name FROM master.dbo.sysdatabases WHERE name like ‘%_CFG_Search%’

OPEN Databases_CTE
DECLARE @dbName varchar(100);
DECLARE @DynamicSQL varchar(500);

Fetch NEXT FROM Databases_CTE INTO @dbName
While (@@FETCH_STATUS <> -1)
BEGIN
— SQL statement
set @DynamicSQL = ‘USE ‘ + @DBName + ‘ ALTER ROLE [db_owner] ADD MEMBER [THISISMYLAB\svc-SPFarm-L]’
— Print SQL statement
print @DynamicSQL
— Execute script
EXEC (@DynamicSQL)
— Get next database
Fetch NEXT FROM Databases_CTE INTO @dbName
END
CLOSE Databases_CTE
DEALLOCATE Databases_CTE
GO

 

 

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s