Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
457 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 225 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.
0
 
LVL 6

Expert Comment

by:messen1975
ID: 20366719
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
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.
0
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!

 
LVL 6

Expert Comment

by:messen1975
ID: 20367235
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
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.
0
 

Author Comment

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

Author Comment

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

Featured Post

 The Evil-ution of Network Security Threats

What are the hacks that forever changed the security industry? To answer that question, we created an exciting new eBook that takes you on a trip through hacking history. It explores the top hacks from the 80s to 2010s, why they mattered, and how the security industry responded.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Viewers will learn how the fundamental information of how to create a table.

877 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