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.



        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


        REMARKS         = convert(varchar(254),null)


        sys.master_files s_mf


        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




According to MSDN, the sp_databases stored procedure requires the following 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.



Best regards,