Solved

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

Posted on 2007-11-28
7
449 Views
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.
0
Comment
Question by:dmateer
  • 4
  • 3
7 Comments
 
LVL 6

Accepted Solution

by:
messen1975 earned 75 total points
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:messen1975
Comment Utility
Example Code Below:
USE [master]

GO

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'

GO

sp_denylogin @loginame = 'RFC\Domain Users'

Open in new window

0
 

Author Comment

by:dmateer
Comment Utility
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.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 6

Expert Comment

by:messen1975
Comment Utility
Here is a good article on securing your SQL Server 2000 installation: (Free Registration Required)
http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1151649,00.html

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

Author Closing Comment

by:dmateer
Comment Utility
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.
0
 

Author Comment

by:dmateer
Comment Utility
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.
0
 

Author Comment

by:dmateer
Comment Utility
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.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

744 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

16 Experts available now in Live!

Get 1:1 Help Now