Question on "Deny Access"

Question

Question on "Deny Access"

Steve DiDomenico

Ten Centuries

Points: 1138
More actions
April 16, 2004 at 9:11 am

#85176

Can an NT Domain Account over-ride the Security in SQL Server?
I have Users getting access in QA even though I have denied them permissions.
Thanks, Steve DiDomenico, Nashua, NH

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

No, Windows permissions can't override SQL Server security. Are you saying you have SQL Server logins and have denied users access based on those SQL Server logins or you've applied deny specifically to the Windows logins (Domain\User or Domain\Group) they are using?

K. Brian Kelley
@kbriankelley

Steve DiDomenico Ten Centuries Points: 1138 More actions · Answer 2

Hello, Here is what I have tried:

1. Database: Company_Test

2. Create a Role called: db_deny_IUD

3. Using Database Role db_deny_IUD, deny permissions on table1 (with a red X in the check box) for Insert, Update, and Delete.

4. Add both SQL Server and Windows logins to the Database Role db_deny_IUD.

5. Login to DB using QA with a SQL Server Auth. that is in the Database Role db_deny_IUD and the Deny works on table1.

6. Login to DB using QA with Windows Auth. that is in the Database Role db_deny_IUD and the Deny does not work on table1.

7. Add the Windows group to the role, Login to DB using QA with Windows Auth. that is in the Database Role db_deny_IUD and the Deny still does not work table1.

I have logged off the domain and then back on, I have logged off and restarted my computer and it still does not work. Also as a suggestion from another sqlservercentral.com member I tried the DENY command. It cannot find the Windows Logins. I have checked the syntx over and over again, the Windows Logins exist in SQL Server.

I have no idea what else to do.

Thanks

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

The Windows users, they wouldn't happen to be members of the local Administrators group on that system (Operating System) would they?

K. Brian Kelley
@kbriankelley

Steve DiDomenico Ten Centuries Points: 1138 More actions · Answer 4

I'm told they have "God Access." I sent an email to the Network Group. I'll post their answer when I can.

Thanks for your posts bkelly.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

If they have local Administrative rights, then likely the login corresponding to their Windows access is BUILTIN\Administrators. Assuming a default security setup, BUILTIN\Administrators is a member of the sysadmin fixed server role. That's the same rights as sa. Members of the sysadmin fixed server role map into the databases as dbo and security is NOT checked on them (because it's not checked on dbo). If this is the case, the DENY permissions settings at the database level are being bypassed. Either you'll have to reduce the permissions BUILTIN\Administrators have or they will have to be removed from the local Administrators group. If you choose the former, I've written an article about the gotchas involved in doing that.

K. Brian Kelley
@kbriankelley

Steve DiDomenico Ten Centuries Points: 1138 More actions · Answer 6

Well at least it makes sence as to why the DENY is bypassed. Thanks for all of your help.