CODEWITHSUNDEEP
sql-serversql-server-data-tools
SAS
SAS

Reputation: 4045

Error generating publish script: the select permission was denied on the object __refactorlog?

In my SSDT Project, why could I be getting this error?

"the select permission was denied on the object __refactorlog?"

There is a refactorlog item in the Project.

I select to generate a publish script from inside VS.

Edit: the target is a production server, so I don't have permissions to create tables or select data there, I just have to create the deployment script, and there is no table __refactorlog anyway..

Upvotes: 3

Views: 1988

Answers (2)

Kevin
Kevin

Reputation: 61

Here is how I handle this, even though it is a poor substitute and is not useful for automating database publishing. I write the publish script, then do a global search and replace, changing "dbo" to a schema that I can access. That works for me, because I have removed all the options that attempt to script objects I do not control, and the dbo schema is completely out of my hands, same as you.

I am not proud of this, and I wish that I had the option of controlling where that table could reside, but at least the Visual Studio features of refactoring are completely supported.

Upvotes: 0

Kevin Cunnane
Kevin Cunnane

Reputation: 8110

You need greater permissions than you currently have on that server in order to generate a publish script. I verified that there is a call "SELECT OBJECT_ID(N'dbo.__RefactorLog')" in the code handling refactor operations that checks if the table exists. It's likely that the code is failing at that point as you don't have the permissions to even ask to select the object. You can check this yourself by running that query against the database and seeing if you get an error.

Suggestions for determining the permissions you need:

  • Try right-clicking on the database and choosing "Create New Project". If you can't extract the database definition to a project then it's a clear sign that you don't have View Definition privileges on the database
  • If that's not the case, then the issue is that you need read permissions on the __refactorlog table and don't have it. The solution would be to get access to that table, or else you'd have to verify none of the refactorings are needed and delete the refactorlog from the project (not recommended as generally you want to preserver refactorings to avoid possible data loss and other issues).

Upvotes: 6

Related Questions