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
452 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
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

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

SHARE your personal details only on a NEED to basis. Take CHARGE and SECURE your IDENTITY. How do I then PROTECT myself and stay in charge of my own Personal details (and) - MY own WAY...
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

830 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