Question on Cloud SQL Server admin user rights

[jcook119]

Good afternoon,

I have recently begun working with GCP and Cloud SQL, but have over 20 years experience with SQL Server. I am looking to set up an account on a new Cloud SQL Server instance that will allow the DBA team to support the system, but I don't want to give them access to the default admin account for the instance. From the Cloud SQL documentation, I see that the default admin account is a member of a role called CustomerDbRootRole. Is it possible to add a new account to that role, so that it has the same rights as the default admin account? If so, where is this role accessible? A similar setup in SQL Server would be to create an account and add it to the SA server role, so that I don't have to give out the default SA account password.

[ms4446]

No, it is not possible to directly add users to the CustomerDbRootRole role in Cloud SQL for SQL Server. This role is a managed role by Google Cloud, and it is exclusively assigned to the default sqlserver user account.

In Google Cloud SQL for SQL Server, the traditional sysadmin role is not supported. Therefore, you cannot create another user with the exact same privileges as the sqlserver user by assigning the sysadmin role, as you would in a traditional SQL Server setup.

If you want to provide elevated privileges to another account:

1. Connect to your Cloud SQL instance using a SQL client tool like SQL Server Management Studio (SSMS) or Azure Data Studio.
2. Create a new SQL login:
   CREATE LOGIN [NewAdminUser] WITH PASSWORD = 'YourStrongPassword';
3. Grant specific permissions to the new login based on the tasks they need to perform. Note that you'll need to grant permissions manually, as there's no role equivalent to sysadmin or CustomerDbRootRole that you can assign.

Once you have created the new login and granted the necessary permissions, the user can connect to the SQL Server instance using the new login and password. However, they won't have the exact same level of access as the default sqlserver user with CustomerDbRootRole.

Important: Always be cautious when granting permissions. It's essential to follow the principle of least privilege, granting only the permissions that are genuinely needed.

Always refer to the official Google Cloud documentation or consult with a Google Cloud specialist to ensure accurate configurations.

[cbuckley]

This is misleading. If you create a user through the google console, the user will be added to the CustomerDbRole.

[ms4446]

Cloud SQL - SQL Server roles can function as either individual roles or groups of roles. A user, essentially, is a role that possesses the ability to log in, granted by the LOGIN permission. Since all roles created by Cloud SQL hold the LOGIN permission, the terms "role" and "user" are used interchangeably in this context.

Superusers and System Stored Procedures

Given that Cloud SQL for SQL Server operates as a managed service, it restricts access to certain system stored procedures and tables that require elevated privileges. Superuser permissions, which would allow for unrestricted access, cannot be created or utilized within Cloud SQL. Consequently, the sysadmin role is not supported, preventing the execution of system stored procedures that demand sysadmin privileges.

Default SQL Server Users

Upon setting up a new Cloud SQL for SQL Server instance, the default sqlserver user is automatically created. This user is assigned to the CustomerDbRootRole, which grants a comprehensive set of permissions, including ALTER ANY CONNECTION, ALTER ANY LOGIN, CREATE ANY DATABASE, and more. However, the sysadmin and dbcreator roles are not supported in Cloud SQL for SQL Server.

Granting Server Permissions

When employing any GRANT command to grant privileges, it is mandatory to specify CustomerDbRootRole as the value of grantor_principal.

Creating Additional SQL Server Users

The creation of additional SQL Server users or roles is permitted within Cloud SQL. All users created through Cloud SQL are bestowed with the same database permissions as the sqlserver login. However, if a user is created using a different method (not through Cloud SQL), the user will not possess the same permissions as the sqlserver user or customer administrator accounts.

Database Imports and User Permissions

During the process of importing a database, Cloud SQL manages ownership based on the owner type. For existing logins that differ from sa, Cloud SQL maintains that ownership and establishes a sqlserver user with specific permissions.

Modifying User Permissions

To alter user permissions, the ALTER ROLE command can be utilized. If a new user is created using a client, it can be linked to a different role or granted distinct permissions.

While Cloud SQL allows for the creation of additional SQL Server users, these users, when created through Cloud SQL, inherit the same database permissions as the sqlserver login. However, if created using a different process, these users will not possess the same permissions as the sqlserver user or customer administrator accounts. Additionally, the sysadmin role is not supported in Cloud SQL for SQL Server.

[jcook119]

Thank you for the response! I will go this route.

[warutp]

Is it grant SQLAgentUserRole on new SQL user ?

[ms4446]

In Cloud SQL for SQL Server, the concept of SQL Server Agent and its associated roles, like SQLAgentUserRole, is not directly applicable as it would be in a traditional on-premises SQL Server environment. This is because Cloud SQL is a fully managed service, and certain features and roles that are available in a self-managed SQL Server are either managed differently or not available in Cloud SQL.