GCP SQL Sqlserver user permission

[RDsean]

HI  Team
If I have many database and many useraccount and i what to make sure different user only can see their database when they use ssms
Is that possible on GCP sql sqlserver
like deny  "VIEW ANY DATABASE" for user?

Thanks a lot!

[ms4446]

Yes, it is possible to restrict the databases that a user can see in SQL Server on Cloud SQL. You can do this by denying the user the "VIEW ANY DATABASE" server-level permission. Once denied, the user will only be able to see the databases to which they have explicit access.

To deny the "VIEW ANY DATABASE" permission via SQL Server Management Studio (SSMS):

1. Connect to the SQL Server instance using SSMS.
2. In the Object Explorer, expand the server node, then expand Security > Logins.
3. Right-click the user that you want to restrict access for and select Properties.
4. In the Login Properties dialog box, go to the Securables page.
5. In the Explicit tab, find the VIEW ANY DATABASE permission and set it to Deny.
6. Click OK to save your changes.

Alternatively, you can use the following T-SQL query to deny the "VIEW ANY DATABASE" permission for a user:

USE MASTER;

DENY VIEW ANY DATABASE TO <username>;

[RDsean]

HI @ms4446 
My account's serverrole  is CustomerDbRootRole 
and I use the T-SQL it show the message 'Grantor does not have GRANT permission.' the user's serverrole is public 


Thanks for your reply

[Alex_SC]

Hi @RDsean
I have the same problem.
Did you find a solution?

@ms4446 could you maybe help?

Thanks!

[Alex_SC]

I found a soution:

1. Create a new user "admin2" in GCP Console
2. Connect to the SQL Server instance using SSMS as "sqlsever" user
3. Execute

USE master; 
GRANT VIEW ANY DATABASE TO [admin2] WITH GRANT OPTION as [CustomerDbRootRole];

4. Connect to the SQL Server instance using SSMS as "admin2" user
5. Execute

USE master;
GRANT VIEW ANY DATABASE TO [sqlserver] WITH GRANT OPTION as [CustomerDbRootRole]

6.  Connect to the SQL Server instance using SSMS as "sqlsever" user
7. Execute

USE master;
DENY VIEW ANY DATABASE TO [user1] CASCADE

8. Delete the "admin2" user
9. Done