asked
User security issue.
Hi 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. i used this command .. deny alter to schema :: schema_name to user i used on individial table deny alter on table table_name to username but i get this error : "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself." I am not sure what is the issue. this user is not a dbo or anything. a normal read and write access USER.
Yes Deny on schema to the user will remove permision on all tables under that schema...as mentioned by Thomas your sntax might wrong..
It looks to me as though your syntax is slightly off. I reckon the syntax should be: DENY permission ON object TO user_name eg: DENY ALTER ON schema_name TO user_name or DENY ALTER ON OBJECT::schema_name.object_name TO user_name See for more information
