User security issue.


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.

more ▼

asked Jun 21, 2011 at 11:09 AM in Default

avatar image

Katie 1
1.4k 132 164 205

Yes Deny on schema to the user will remove permision on all tables under that schema...as mentioned by Thomas your sntax might wrong..

Jun 22, 2011 at 12:42 PM Manikreddy
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It looks to me as though your syntax is slightly off. I reckon the syntax should be:

 DENY permission ON object TO user_name


 DENY ALTER ON schema_name TO user_name


 DENY ALTER ON OBJECT::schema_name.object_name TO user_name

See http://msdn.microsoft.com/en-us/library/ms173724.aspx for more information

more ▼

answered Jun 21, 2011 at 11:16 AM

avatar image

ThomasRushton ♦♦
42.3k 20 57 53

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 21, 2011 at 11:09 AM

Seen: 1102 times

Last Updated: Jun 21, 2011 at 11:09 AM

Copyright 2018 Redgate Software. Privacy Policy