How do I deny a Windows user/group permission to view the tables and columns in SQL Server 2000?

Posted on 2007-11-28
Last Modified: 2012-05-05
We have a SQL Server 2000 database server with several databases running on it. On one of these databases (call it "VulnerableDatabase"), certain users are able (using SQL Server Management Studio Express) to view the database, table, and column objects. They are NOT able to view the actual data in the tables, but can still dig down at least this far into the database schema itself. On other databases (call them "SecureDatabases") on the same server, they are getting the "Access denied" message as soon as they click on the database itself. I want the same behavior on the "VulnerableDatabase" as on the "SecureDatabases." These users should not be able to access the database at all; they don't want access, as it opens up accountability issues.

I have watched over their shoulders; they are connecting using Windows Authentication. They belong to several domain groups, none of which (nor inherited groups) have been given any rights on the "VulnerableDatabase."

I'm stumped as to why they should be able to get even that far into the database. Any ideas? Thanks for your time.
Question by:dmateer
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3

Accepted Solution

messen1975 earned 75 total points
ID: 20366648
Create a Login using those user's groups and deny them access to the database / tables that you need to deny them login rights to the database.

Expert Comment

ID: 20366719
Example Code Below:
USE [master]
EXEC master.dbo.sp_grantlogin @loginame = N'Domain\Domain Users'
EXEC master.dbo.sp_defaultdb @loginame = N'Domain\Domain Users', @defdb = N'master'
EXEC master.dbo.sp_defaultlanguage @loginame = N'Domain\Domain Users'
sp_denylogin @loginame = 'RFC\Domain Users'

Open in new window


Author Comment

ID: 20367186
Thank you for the reply. I need to clarify the problem. I can use the explicit deny login solution for those users who know they have (or who we know to have) elevated priviledges, but it still leaves open the security vulnerability--SOMETHING is causing certain users to have elevated privileges on this database, and there are likely unidentified users who have the elevated permissions. I guess my question needs rephrased as, "How can I determine why (and then deny) certain users/groups are able to view the tables and columns in SQL Server 2000?"

I did some testing with a dummy account and it is related to a specific domain group, call it "RandomGroup." If my dummy user is added to this group, he can view the tables and columns. If he is removed from the group, he cannot. This group has absolutely no permissions (that I can see) on the Sql Server or "VulnerableDatabase" itself. I cannot deny permissions to the group because some of them have legimatate access needs to the database. I could deny each individual in the group but (a) it's a fragile solution--the explicit deny must be remembered to be done every time a new user is added, and (b) it still leaves open the question, "What is causing this?" and what other groups out there might be getting permission.
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.


Expert Comment

ID: 20367235
Here is a good article on securing your SQL Server 2000 installation: (Free Registration Required),295582,sid87_gci1151649,00.html

It suggests several tools that can help you assess your Security configuration and point out some areas of concern.

Author Closing Comment

ID: 31412316
Thank you for your help. This solution, indeed, technical solves the problem as originally stated. As amended (trying to track down WHY the problem is there and how to fix it directly), we are still working on it. If it's any consolation, we put in a case with Microsoft and have thus far stumped them as well.

Author Comment

ID: 20395361
We have put in a case with Microsoft to track down why this is occuring. If the solution is something that might be generally applicable, I will post it once we have it resolved.

Author Comment

ID: 20950665
In case it's of help to other users, here was the problem. We had RENAMED a group in active directory, of which the user in question was a member. However, apparently SQL Server is not notified of group name changes. So while it appeared that the user was not a member of any group with access to SQL Server, in fact he was, but the group names were different. We had to look at the SIDs to determine which group was granting access.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This story has been written with permission from the scammed victim, a valued client of mine – identity protected by request.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question