SQL Server 2005 asign permissions to users

[jpreciado]

I'm trying to asign permissions to users on tables.
I've been using Querys like:

DENY DELETE ON [Schema].

TO [User Name]
GO

Then the "Query Excecuted Succesfully" is shown.

When the user not allowed to delete is connected. He can Delete! Rows from the table he is supoused not allowed to Delete.

In other words the query does not do what it supoused to.

Does any one know what is the procedure to assign permissions to users?
I Don't know if I'm doing something wrong.

Thanks in advanced.

 

[Catadmin]

Check to make sure the user in question isn't a member of a group that has inherited permissions that overrides the DENY statement. For instance, you can't deny permissions for someone who is a member of the SysAdmin ServerRole.

Oh, This might be your problem too. Just found it in BOL:

Deny will fail if CASCADE is not specified when denying a permission to a principal that was granted that permission with GRANT OPTION specified.
.
.
CAUTION:
In SQL Server 2005 a table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for the sake of backward compatibility. It will be removed in a future release

Also, make sure the User name you're deny permissions on is spelled exactly like it is in the database's Users section. I.E., if you're using TED and the name listed under Users is actually MyDomain\TED, you'll need to add in the MyDomain\ part of the user name.

Does any of that help?







Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

If the user is a membor of the sysadmin fixed server role as Catadmin said you won't be able to deny them rights. Also if they are a member of the db_owner, db_datawriter, db_ddladmin fixed database roles you won't be able to deny them rights either.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com