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
  • 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.
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.


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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

896 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now