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!
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):
Security
> Logins
.Properties
.Login Properties
dialog box, go to the Securables
page.Explicit
tab, find the VIEW ANY DATABASE
permission and set it to Deny
.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>;
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
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
User | Count |
---|---|
1 | |
1 | |
1 | |
1 | |
1 |