In Master DB:
CREATE LOGIN Test_Admin
WITH PASSWORD = 'xxxx';
GO
CREATE USER Test_Admin
FOR LOGIN Test_Admin
WITH DEFAULT_SCHEMA = [guest]; --want to grant minimum access rights
GO
In Target DB:
CREATE USER Test_Admin
FOR LOGIN Test_Admin
WITH DEFAULT_SCHEMA = [dbo];
GO
However, I could not search dbo's existing tables by below SQL:
SELECT * FROM INFORMATION_SCHEMA.TABLES
Why and how can I see back dbo's tables?
You create a user in master DB, and you want the user Test_Admin can access Target DB, am I right?
If you want to do this, you need to give database users "db_owner" permissions by joining the database role.
You can modify you code like this an try again:
In Master DB:
CREATE LOGIN Test_Admin
WITH PASSWORD = 'xxxx';
GO
CREATE USER Test_Admin
FOR LOGIN Test_Admin
WITH DEFAULT_SCHEMA = [guest]; --want to grant minimum access rights
GO
EXEC sp_addrolemember 'db_owner', 'Test_Admin'
In Target DB:
CREATE USER Test_Admin
FOR LOGIN Test_Admin
WITH DEFAULT_SCHEMA = [dbo];
GO
EXEC sp_addrolemember 'db_owner', 'Test_Admin'
GO
Hope this helps.