User rights management for Cloud SQL - SQL Server

[Rollthehardsix]

Hello,

I am fairly new to GCP and their implementation of SQL PaaS, am familiar with other cloud vendors offerings. I have run into an issue where an application needs to connect to SQL Server and unfortunately I am unable to grant the login permissions - in this case DBO to that database. The default login that comes with SQL Server does not even have permissions to see that database. I understand the need to do this but this is traditionally the role of a DB admin. I have looked at documentation and unless I am missing something how are you supposed to manage users in Cloud SQL.

[ms4446]

Hello,

In CloudSQL-MSSQL the The sysadmin role is not supported. However, there are custom roles created for you to perform management and user/group entitlements in SQL Server. In the case of GRANT the Login that will be using this command must be part of the CustomerDbRootRole

Please refer to the following link for information - 

https://cloud.google.com/sql/docs/sqlserver/users#grant-command

[Rollthehardsix]

it looks like the command does not work GRANT ALTER ANY [loginA] AS CustomerDBRootRole; 

Is that command deprecated in SQL Server 2019?

[ms4446]

The correct syntax is:

GRANT ALTER ANY LOGIN TO [Account] AS CustomerDbRootRole

[Rollthehardsix]

that works thank you one other question if i may, why even when i grant the account the customerdbrootrole, it is unable to see some databases. I am really just trying to add DBO to a service account to a database.

[ms4446]

The CustomerDbRootRole by default has the VIEW ANY DATABASE permission so any account that is a member of this role should be able to see all databases. You can add a service account to the dbo role in each user databases by running the below syntax (provide the its run the context of account that is a member of the CustomerDbRootRole)

EXEC sp_adduser 'user';
EXEC sp_addrolemember 'db_owner', 'user'