Sreenivasan
Sreenivasan

Reputation: 21

Remove permission for Delete database object for a User in Azure SQL Server

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

  1. Execute Queries like Select,Update,Insert.
  2. Execute Stored Procedure,Function,Trigger,Views
  3. Create and Modify Stored Procedure,Function,Trigger,Views
  4. Execute Alter table scripts [ For including new columns,for adding/removing constraints]

Upvotes: 1

Views: 2184

Answers (0)

Related Questions