selectazure-sql-databaseschemassmsdbo

Azure SQL Database new created user with dbo schema but cannot search the tables under dbo schema


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?


Solution

  • 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.