Cloud SQL - SQL server roles and permissions

[jatin30naudiyal]

Hi Team

I have created a sql server 2019 express instance and sqlserver (default user) has been created with it but when I login into the default user, it has only dbconnect permission and I not able to grant dbadmin permission to default user as it throws an error "You do not have permission". 

Also, I have created a new user and try to give the permission to that user but the error is same.

Can anyone faces the same issue?

Thanks

[ms4446]

In Google Cloud SQL for Microsoft SQL Server, the default sqlserver user is a member of the db_datareader and db_datawriter roles, which means this user can perform basic read and write operations. However, this user doesn't have administrative permissions such as db_owner or db_ddladmin.

If you are trying to assign db_owner or db_ddladmin roles to sqlserver or a new user, and you're getting the error "You do not have permission", it means you're trying to perform an action for which the user doesn't have sufficient privileges.

In Cloud SQL for MS SQL, there are two types of accounts:

1. SQL Server authentication users: These are users like the sqlserver user that you mentioned. These users can perform basic operations, but they don't have administrative privileges.

2. Windows authentication users: These are users that can authenticate using a Windows account. These users can have the sysadmin server-level role, which has all administrative privileges.

To perform administrative operations like assigning db_owner or db_ddladmin roles to a user, you would need to connect as a user with the sysadmin role. In Google Cloud SQL, this would be the cloudsqladmin Windows authentication user.

Here are the steps to connect with the cloudsqladmin user and assign db_owner or db_ddladmin roles to a user:

1. Connect to the SQL Server instance using the cloudsqladmin user. You can do this through the SQL Server Management Studio (SSMS), Azure Data Studio, or any other SQL Server client. Here's an example connection string: Server=your_instance_ip;Integrated Security=True;.

2. Once connected, you can execute the following command to assign the db_owner role to a user:

USE your_database; ALTER ROLE db_owner ADD MEMBER your_user;

Replace your_database with the name of your database, and your_user with the name of the user to whom you want to assign the db_owner role.

Please note that you must have the cloudsql.instances.connect permission to connect as the cloudsqladmin user. If you don't have this permission, you would need to ask your Google Cloud administrator to grant it to you