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
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:
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.
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:
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;
.
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