Skip to main content
3 votes
Accepted

How do I prevent a DB user to change table contents in SQL Server?

throw away db_owner permission and set a role with exactly what you need CREATE ROLE myspecialrole; GRANT SELECT ON schema :: [dbo] TO myspecialrole; GRANT INSERT ON schema :: [dbo] TO myspecialrole;...
MBuschi's user avatar
  • 4,615
2 votes

How do I prevent a DB user to change table contents in SQL Server?

This code will do it. DENY UPDATE ON dbo.AspNetUserRoles TO [Dbuser] GO DENY INSERT ON dbo.AspNetUserRoles TO [Dbuser] GO Details about deny syntax. Complete test code here: --Create a test database ...
SqlWorldWide's user avatar
  • 13.5k
2 votes
Accepted

error when re-applying the permissions script into the original database - sql server

You need to exclude internal tables using is_ms_shipped = 0. You also need to quote names correctly using QUOTENAME. You need to handle WITH GRANT OPTION correctly. You probably want to add AS ...
Charlieface's user avatar
  • 13.7k
1 vote

Need to add user and assign roles automatically, using DDL Trigger, when database is created

Adding a user to a model database and assigning it to required database roles would be easiest, but since you have a requirement: ...whenever a database starting with PA is created... Means you don'...
Aleksey Vitsko's user avatar
1 vote
Accepted

How can I allow a user to alter tables they do not own in Postgres?

No, there is no way around that: only a member of the owning role or a superuser can ALTER or DROP an object. Perhaps you can make the user that is to drop the objects a member of all other users?
Laurenz Albe's user avatar
  • 54.1k

Only top scored, non community-wiki answers of a minimum length are eligible