Reputation: 21
I would like to know how can we restrict a user from deleting the database object which is in Azure SQL Database.
I Created a new user with the below script
-- Create a user for the server who has access for Read and Write in the Database
-- Use Master DB
-- Create SQL User for login
USE master
GO
CREATE LOGIN arzscosqlsrvproddev with PASSWORD = 'pass@rprodsrv'
GO
-- Add the user to each database which you are working
CREATE USER [arzscosqlsrvproddev] FOR LOGIN [arzscosqlsrvproddev] WITH DEFAULT_SCHEMA = dbo;
GO
-- Use Specific database
-- Add the user to each database which you are working
CREATE USER [arzscosqlsrvproddev] FOR LOGIN [arzscosqlsrvproddev] WITH DEFAULT_SCHEMA = dbo;
GO
-- Add Role for Reading and Writing to the database
CREATE ROLE [db_executor] AUTHORIZATION [dbo];
GO
GRANT EXECUTE TO [db_executor]
GO
ALTER ROLE db_datareader ADD MEMBER [arzscosqlsrvproddev]
GO
ALTER ROLE db_datawriter ADD MEMBER [arzscosqlsrvproddev]
GO
ALTER ROLE db_executor ADD MEMBER [arzscosqlsrvproddev]
GO
GRANT ALTER ON SCHEMA::dbo TO arzscosqlsrvproddev
GO
Once after creating I logged with the same user in SQL Server Management Studio and found the Delete option is enabled for an database object.
Please refer the Image in the link : https://i.sstatic.net/N2exS.png
Our requirement is to create a user who should have rights for
Upvotes: 1
Views: 2184