USER MS SQL Database
This is for SQL 2016 but its probably all of them.
I want to hide all Databases except his own DB from a user (Created by solidcp) who login with SQL management Studio.
So i have run this on the server.
DENY VIEW ANY DATABASE TO PUBLIC
And now new/old users/database created in solidcp cant see any databases (Except Master /tempdb) it canot see even its own even if it seems like the user is dbo.
But if i create a database and user like this on the SQL server.
CREATE LOGIN TEST99 WITH PASSWORD = 'qQ12345'
CREATE DATABASE TEST99
exec sp_changedbowner 'TEST99'
Then this user (TEST99) can see its own DB in management studio so. Whats the diffrent when solidcp is creating the user?
How can i achieve my goal?
Your example allows you to have only one owner per database and only that owner/login will be able to view that database.
SolidCP allows multiple sql logins to access the same database.
We want to "deny view any database to public", then enable MULTIPLE users to view their database in SSMS. but then avoid granting database owner to users.
We are looking into this, possible scenarios and possible code changes.
If we can fix it the manual way we can change code.