October 24, 2007 at 4:38 pm
I've been maintaining a SQL Server 2005 installation for a while now, but my understanding of security is lacking. Our database is set up for that a domain user with a particular membership can access the database just fine. I'm sure this could be better (and I'm presently taking a class), but it works just fine.
However, I recently got the request to create a sql server user (not domain) that only has access to one table & view, but none others. I thought I could achieve this by creating the user and mapping them directly to the database, then setting db_denydatareader and db_denydatawriter for that user for the entire database. After doing that, I would grant select/insert specifically on the objects (1 view and 1 table) in question.
I was thinking that the individual object's security could override the database policies, but I was wrong. No matter what I do, I can only seem to allow access to the entire database or none of it for this one particular user. The only other thing I could think of would be to specifically deny access on every other object in the entire database, but that would be undesirable.
Does anyone have any suggestions as to where I'm going wrong? I was at a small users meeting the other day and I swore I saw the presenter denying select access to a user at the database level only to grant it at the view level, and it worked. Thanks in advance!
October 24, 2007 at 5:17 pm
The first thing to remember about SQL Server security is that unless permission is explicitly granted, SQL Server defaults to no access. Therefore, you don't usually have to use db_denydatareader and db_denydatawriter. By default, a user does not have rights to any tables.
With that said, check the permissions assigned to the public role. Every user within the database is a member of this role and gets all the permissions of the public role. If no permissions have been assigned against the public role, you're in great shape. In that case, create a new database role, grant rights to the table to that role, and then make your user a member of the role.
If rights are assigned against the public role, you either have to rework the permissions so the public role has no permissions (create a database role and assign the permissions to it, and make all appropriate users a member of that role, then take away the permissions from the public role), or you have to create a case where there is an explicit DENY against all the objects that public has access to, with the exception of the one table you want to grant access to. While the former case is preferable (taking permissions away from public), if you go down the second road, create a database role and assign it all the DENYs (don't assign to public or you affect everyone). Then make the SQL Server user a member of that role.
K. Brian Kelley
@kbriankelley
October 25, 2007 at 9:27 am
Thanks so much! That cleared up a few things I was always wondering about our setup.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply