Azure sql database grant select on certain tables

Question

I have created a user in a Azure sql database in SSMS. Now trying to give select permission to certain tables in a db to this user. The security control options seems quite different than on premise sql server. How can I do these on a Azure db?

I have tried this on a on premise sql server and it works fine. But got error when run these on the azure sql db.

    USE [master]
    GO
    CREATE LOGIN [jnj_user] WITH PASSWORD=N'P2ssw0rd123', DEFAULT_DATABASE=[CRM], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [CRM]
    GO
    CREATE USER [jnj_user] FOR LOGIN [jnj_user]
    GO

error:

    Msg 40517, Level 16, State 1, Line 3
    Keyword or statement option 'default_database' is not supported in this version of SQL Server.
    Msg 40508, Level 16, State 1, Line 5
    USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

also this does not work on Azure db either:

    GRANT SELECT ON [dbo].[JJVCEtrialOB] TO  jnj_user

error:

    Cannot find the user 'jnj_user', because it does not exist or you do not have permission.

Answer 1

You need to ensure the login is added on all the levels

---- ON SERVER LEVEL
CREATE LOGIN TestUser
    WITH PASSWORD = 'ThisIsAStrongPassword!' 
GO

---- ON Master Level
CREATE USER TestUser
    FOR LOGIN TestUser
    WITH DEFAULT_SCHEMA = dbo


---- ON SQL DB LEVEL
CREATE USER TestUser
    FOR LOGIN TestUser
    WITH DEFAULT_SCHEMA = dbo
GO

GRANT SELECT ON OBJECT::dbo.Sales TO TestUser;
GO

You need to add the login on server level under the master context

then add a user to the master db under master context

and then add the user to the sqldb and grant its rights

EDIT You cannot use the use statement, so you will have to manually switch context in SSMS

Answer 2

With Azure SQL Database, accounts in the master database are needed only for administrative accounts that need access to all databases of the logical server.

For non-admin users, create a user account in the database for non-admin users so that the user is authenticated at the database level and then grant the needed permissions. See Azure SQL logins and users.

CREATE USER [jnj_user] WITH PASSWORD='<your-complex-password>';
GRANT SELECT ON OBJECT::dbo.Sales TO jnj_user;