SQL server Windows Authentication is Broken

[Indika]

I have a SQL server 2000 with ONLY windows authentication is enabled.

Domain Users are part of the public role and a custom database role that has permissions only for few tables.

For some reason SQL server allows ALL Domain users to have Admin RIGHTS on the server.

Anyone has any suggestions to fix this problem? Thanks

 

[SQLBill]

Are you positive they have ADMIN rights?

Do your users have DOMAIN ADMIN rights for the domain (not on SQL Server, but on the domain itself)?

What are they doing that makes you believe they have admin rights?

-SQLBill



Posting advice: FAQ481-4875

 

[Indika]

Domain users do not have admin rights in the domain. They are able to create users, create DTS packages, run jobs, modify tables, etc on SQL server. I tried assigning different roles like “db_datareader”, db_datawriter”, etc, but nothing seems to work.

 

[SQLBill]

It sounds like they have been given db_owner permissions. Check their login permissions and make sure db_owner is not granted.

Don't just check the permissions on the ROLE you created but also on the individual logins.

-SQLBill

Posting advice: FAQ481-4875

 

[Indika]

I haven’t given permissions to individual users. “Domain Users” group do not have any permissions other than they are part of the “public” and one custom database role which allows access to few tables.

 

[JamesLean]

Check the Administrators group on the local machine. By default the local admin group has sysadmin privileges to SQL Server - check that the Domain Users group has not been added to the local admin group.

--James

 

[Indika]

domain users" group is not added to any local groups other than local "users" group

local "users" group doesn't have permissions to sql server

 

[JamesLean]

OK, these users must be getting their permissions from somewhere!

Check the System Administrators fixed server role and look at which logins are members of this.

How are the domain users gaining access to SQL Server? Have you actually granted the Domain Users group access? If so, look at the properties and see which Server Roles it is a member of?

--James

 

[SQLBill]

Also, check that 'custom role' you created. Check what permissions have been given on the Server Role tab and the Database Role tab.

-SQLBill

Posting advice: FAQ481-4875

 

[miq]

HI,
also make sure Public role is still public someone hasn't alter its permissions.

B.R,
miq