Azure sql database grant select on certain tables

by thotwielder   Last Updated January 11, 2019 11:06 AM

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.


Answers 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

Stijn Wynants
Stijn Wynants
January 11, 2019 10:56 AM

Related Questions


Add Active Directory User For Azure-SQL-DB

Updated February 25, 2017 13:06 PM