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
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
Cannot find the user 'jnj_user', because it does not exist or you do not have permission.
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
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