0

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?

DaiKeung
  • 1,077
  • 1
  • 19
  • 38

1 Answers1

1

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.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • Thanks! How about if I don't want Test_Admin be a user in Master DB but only the DB admin in Target DB? (don't want Test_Admin to see other databases by execute below SQL: select * FROM sys.databases) . **If I don't create user Test_Admin in Master DB, I cannot use SSMS/MS Access to connect Target DB for querying** – DaiKeung Feb 15 '19 at 06:04
  • @DaiKeung Maybe you can reference this [document](https://learn.microsoft.com/zh-cn/azure/sql-database/sql-database-manage-logins). You can login your Target DB with ServerAdmin account with SSMS, set the Target DB name you want to connect in Connection Properties.Maybe I can't answer all your questions, and thanks for accept my answer. – Leon Yue Feb 15 '19 at 06:48