User and Permission Management on Babelfish 4.8.0

Babelfish supports a subset of SQL Server's security model. Some T-SQL user management commands work as expected, others fail with known limitations, and a few are not supported at all. This post documents every command we tested against a live Babelfish 4.8.0 instance on FoundryDB, with exact results.

The practical takeaway: manage your database users through the FoundryDB API, which handles user creation at the PostgreSQL level and avoids the current Babelfish limitations entirely.

The Default User: app_user

When you provision a Babelfish service on FoundryDB, the platform creates a user called app_user. This user is mapped to the dbo schema owner and has sysadmin privileges.

You can verify this with standard T-SQL identity functions:

SELECT CURRENT_USER;
-- Returns: dbo

SELECT SUSER_NAME();
-- Returns: app_user

SELECT IS_SRVROLEMEMBER('sysadmin');
-- Returns: 1

All three return the expected values. app_user is the login name, dbo is the database user it maps to, and the sysadmin role membership confirms full administrative access.

Querying Existing Users and Roles

The sys.database_principals catalog view works for listing both users and roles.

Listing Users

SELECT principal_id, name, type_desc
FROM sys.database_principals
WHERE type IN ('S', 'U');

principal_id	name	type_desc
1	dbo	SQL_USER
2	guest	SQL_USER
3	INFORMATION_SCHEMA	SQL_USER
4	sys	SQL_USER

These are the four built-in database users. dbo is your working user (mapped from app_user).

Listing Roles

SELECT principal_id, name, type_desc
FROM sys.database_principals
WHERE type = 'R';

principal_id	name	type_desc
0	public	DATABASE_ROLE
16384	db_owner	DATABASE_ROLE
16385	db_accessadmin	DATABASE_ROLE
16386	db_securityadmin	DATABASE_ROLE
16387	db_ddladmin	DATABASE_ROLE
16389	db_datareader	DATABASE_ROLE
16390	db_datawriter	DATABASE_ROLE

All seven standard SQL Server database roles are present. You can also query role membership:

SELECT
    r.name AS role_name,
    m.name AS member_name
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id;

role_name	member_name
db_owner	dbo

This confirms dbo (your app_user) is a member of db_owner.

CREATE LOGIN: Works

You can create new SQL Server logins:

CREATE LOGIN report_reader WITH PASSWORD = 'Str0ng!Pass#2026';

This succeeds. The login is created at the server level and can be used for TDS connections.

ALTER LOGIN: Works

Changing a login's password also works:

ALTER LOGIN report_reader WITH PASSWORD = 'N3w$ecure!Pass#2026';

No errors. The password is updated immediately.

CREATE USER: Fails (Babelfish 4.8.0 Limitation)

Here is where things break down. After creating a login, the natural next step is to create a database user for it:

CREATE USER report_reader FOR LOGIN report_reader;

This fails with:

permission denied to revoke role "master_guest"

This is a known Babelfish 4.8.0 limitation. The internal role-mapping mechanism between T-SQL users and PostgreSQL roles triggers a permission error during the grant/revoke chain. The login exists, but you cannot map it to a database user through T-SQL.

Because CREATE USER fails, the following commands cannot be tested in a meaningful way:

• GRANT (no user to grant permissions to)
• REVOKE (no user to revoke from)
• sp_addrolemember (no user to add to roles)

DENY: Not Supported

The DENY statement is not implemented in Babelfish 4.8.0:

DENY SELECT ON sales.products TO report_reader;

Returns:

'DENY' is not currently supported in Babelfish

This is documented behavior. Babelfish supports GRANT and REVOKE but has not yet implemented DENY.

sp_helprolemember: Works

The stored procedure for listing role members runs without error:

EXEC sp_helprolemember;

It returns an empty result set, which is correct since no custom role memberships have been added beyond the built-in db_owner/dbo mapping.

The Recommended Approach: FoundryDB API

Since T-SQL CREATE USER has a known limitation, the reliable way to manage database users on Babelfish is through the FoundryDB API. The platform creates users at the PostgreSQL level, which avoids the Babelfish role-mapping issue entirely. These users work for both TDS (port 1433) and PostgreSQL (port 5432) connections.

Creating a User

Replace SERVICE_ID with your service identifier:

curl -u admin:YOUR_PASSWORD -X POST \
  https://api.foundrydb.com/managed-services/SERVICE_ID/database-users \
  -H "Content-Type: application/json" \
  -d '{"username": "report_reader"}'

The API generates a secure password automatically. To retrieve it:

Revealing a User's Password

curl -u admin:YOUR_PASSWORD -X POST \
  https://api.foundrydb.com/managed-services/SERVICE_ID/database-users/report_reader/reveal-password

Response:

{
  "username": "report_reader",
  "password": "generated-secure-password"
}

Listing All Users

curl -u admin:YOUR_PASSWORD \
  https://api.foundrydb.com/managed-services/SERVICE_ID/database-users

This returns all users on the service, including app_user and any users you have created.

Connecting with the New User

Once created via the API, the user works with standard SQL Server tooling:

docker run --rm mcr.microsoft.com/mssql-tools \
  /opt/mssql-tools/bin/sqlcmd \
  -S your-service.foundrydb.com,1433 \
  -U report_reader \
  -P 'generated-secure-password' \
  -Q "SELECT CURRENT_USER, SUSER_NAME()"

Summary

Feature	Status
CURRENT_USER / SUSER_NAME()	Works
IS_SRVROLEMEMBER	Works
sys.database_principals	Works
sys.database_role_members	Works
CREATE LOGIN	Works
ALTER LOGIN (password)	Works
CREATE USER	Fails (permission error)
GRANT / REVOKE	Cannot test (no users)
DENY	Not supported
sp_helprolemember	Works
FoundryDB API user creation	Works (recommended)

For production use, create and manage users through the FoundryDB API. The platform handles the PostgreSQL-level user creation correctly, and the resulting users work seamlessly over TDS connections.