Error when deploy BizTalk application in VS if your account is removed from sysadmin role in SQL.

 The problem:

===========================

When a user account’s SQL sysadmin role is removed and only leaves it with public, the user cannot use Visual Studio to deploy BizTalk application after you edit the server name, even if the account is in BizTalk Server Administrators group. An Error <No BizTalk Configuration database(s) found on server ''.> is displayed in VS.

 

 

 

 

According to SQL profiler trace captured when the error occurs, the stored procedure sp_databases will be called to retrieve the list of databases on the SQL server.

 

 

 

On SQL server 2012, when I manually execute this stored procedure with only public role(sysadmin removed), the sp returns a blank result even if my account is in BizTalk Server Administrators group which has access to BizTalk databases.

 

exec sp_databases

 

 

 

On SQL server 2008 R2, the same sp properly returns databases with public only.

 

 

 

 

If we check the code of this sp, looks like it should at least return the databases which the account has permission to access.

 

select

        DATABASE_NAME   = db_name(s_mf.database_id),

        DATABASE_SIZE   = convert(int,

                                    case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...

                                    when sum(convert(bigint,s_mf.size)) >= 268435456

                                    then null

                                    else sum(convert(bigint,s_mf.size))*8 -- Convert from 8192 byte pages to Kb

                                    end),

        REMARKS         = convert(varchar(254),null)

    from

        sys.master_files s_mf

    where

        s_mf.state = 0 and -- ONLINE

        has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access

    group by s_mf.database_id

    order by 1

 

Workaround:

===========================

According to MSDN, the sp_databases stored procedure requires the following permissions

https://msdn.microsoft.com/en-us/library/ms176070.aspx?f=255&MSPPError=-2147217396

Permissions

Requires CREATE DATABASE, or ALTER ANY DATABASE, or VIEW ANY DEFINITION permission, and must have access permission to the database. Cannot be denied VIEW ANY DEFINITION permission.

===========================

Therefore, we can grant view any definition permission to specific user which needs to deploy BizTalk application in VS.

USE master  GO  GRANT VIEW ANY DEFINITION TO "SQLUser"

 

Best regards,

WenJun