0

how to deny acces to a specific user in the database to alter any tables or columns or relationships on a sql server database. is it possible, if we deny alter access of 'schema' to the user does it mean that the user is denied alter access all the objects related to that schema. For example, i have a schema called sample. and there are 21 tables in the schema sample. if i deny alter on sample to the user. does it mean the user will not be allowed to alter the 21 tables. Please let me know what is the better way to do it.

Thanks.

iims
  • 313
  • 1
  • 5
  • 12

1 Answers1

2

If you DENY ALTER SCHEMA then the user won't be able to issue any DDL changes (CREATE, ALTER, DROP) that affect objects in that schema.

Note: they should never have GRANT ALTER SCHEMA in the first place

If the user is db_owner then they can change permissions anyway. If sysadmin, they can do anxthing

gbn
  • 422,506
  • 82
  • 585
  • 676