0

I am trying to create a user who can select all tables within a DB in AzureSQL. but has privileges to Insert,delete, update on his own schema(ODS) within the DB?

Gokul Krishnan
  • 1
  • Stackoverflow is for programming questions, not admin ones. Maybe you should try this question at dba.stackexchange.com. – The Impaler Oct 11 '19 at 17:34
  • Hi ,If my answer is helpful for you, you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. – Leon Yue Oct 17 '19 at 02:07

1 Answers1

0

Here is the example about create a user who can select all tables within a DB in Azure SQL database (readonly):

--running in master db
USE [master]
GO
CREATE LOGIN [sagarreadonly] WITH PASSWORD='password'
GO
-- running in Azure SQL DB
USE [DataEncryptDemo]
GO
CREATE USER [sagarreadonly] FOR LOGIN [sagarreadonly] WITH DEFAULT_SCHEMA = Marketing; 
GO
EXEC sp_addrolemember 'db_datareader', 'sagarreadonly';
GO

For more details about database roles, please see Database-Level Roles.

Since the user is added to the 'db_datareader' role, it's a readonly user and doesn't have the permission to insert, delete, update on his own schema(ODS) within the DB. You can not achieve that and it's contradictory.

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23