x

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.

more ▼

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

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

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

eg:

DENY ALTER ON schema_name TO user_name

or

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

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1832
x161
x85

asked: Jun 21, 2011 at 11:09 AM

Seen: 853 times

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