I have an azure SQL Database. I already created a new login and user on the database. Question is i want to assign permission on the said user. Permission that will restrict the user to drop table, update data, truncate and create another role?
I do not have an azure database server right now to check, but looking at the regular SQL server instance it is pretty easy. I hope, or think it is the same for an azure DB If you have created the user already, then you can map the user to the database.
CREATE USER [User] FOR LOGIN [user]
It will get public access automatically, meaning it can connect to the database. With this public access not much can be done, but to give extra Easy way to grant only read access on the complete database is to grant db_datareader access.
on the DB in a query
ALTER ROLE [db_datareader] ADD MEMBER [User]
If per table access is needed, you need to play with the security within the database itself. for example Connect to the database you want to do security
DENY DELETE ON [schema].[tablename] TO [user]