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.
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
else sum(convert(bigint,s_mf.size))*8 -- Convert from 8192 byte pages to Kb
REMARKS = convert(varchar(254),null)
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.