1

I see permission error when i tried to shrink files/db in CloudSQL sql server.

I know its a managed service and do not have sa user and super privileges.

But is there any similar user like sa (or) alternate ways to carry out such tasks. enter image description here

Dothertechie
  • 21
  • 4

3 Answers3

1

No, it will not be possible to do it on your own.

In order to perform shrink operations on your db you will need to contact the GCP support.

Deniss T.
  • 255
  • 1
  • 8
1

I think i just figured it out, we are able to enable the sa user with the default sqlserver superuser login and then grant sysadmin to the desired user accounts.

sa user enabled

https://cloud.google.com/sql/docs/sqlserver/users

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql?view=sql-server-ver15

I think we need the necessary access for performing cloudsql operations, which could be obtained from IAM similar to below,

  • Cloud SQL Admin
  • Cloud SQL Client
  • Cloud SQL Editor
  • Cloud SQL Viewer

https://cloud.google.com/iam/docs/understanding-roles#cloud-sql-roles

Dothertechie
  • 21
  • 4
  • Hello Kiran, Following the steps you described were you able to shrink your db already? All users you create using Cloud SQL are created as part of the **cloudsqlsuperuser** role. The [GCP documentation](https://cloud.google.com/sql/docs/sqlserver/users) is pretty straightforward on that - your users will not have the **SUPERUSER** or **REPLICATION** attributes. – Deniss T. Feb 21 '20 at 15:12
  • I agree, but enabling sa user we can grant the required and it worked. – Dothertechie Feb 26 '20 at 11:22
0

select suser_name()
ALTER LOGIN [sa] ENABLE

sqlserver
Cannot alter the login 'sa', because it does not exist or you do not have permission.

@Dothertechie can you please clarify how exactly did you enable sa? Or did they just fix the loophole?

crazybert
  • 1
  • 1