[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Restrict what tables users can see in SQL Server 2005

Posted on 2009-12-28
14
Medium Priority
?
689 Views
Last Modified: 2012-05-08
I have a database called demand_plan.  In this database is about 70 tables/views.  I have about 70 users worldwide currently that access the tables in this SQL Server 2005 database by linking to the tables in Access 2000.  So SQL Server 2005 is the backend and Access 2000 is the frontend for the users.

Without changing the userid and password that is already setup on all these users systems, I would like to restrict what tables/views are available to the end users when they try to link to the database from Access 2000.  In essence, there are about 20 tables they should be able to see.  The list of what is available will frequently change, so I am hoping this is something simple where I can just go in and add tables/views as they are available, and not have to recreate this constantly.

I am new to SQL Server, so struggling with this, even though I would think it would be simple.  I am not a stranger to programming or databases, just new to SQL Server, so past experience would tell me this is going to come down to some type of User permissions.  I thought possibly also it could fall into the text of a schema, but many of these tables have the generic 'dbo' in front of them and a ton of programming already in place, so was hoping to avoid any changes there.

Help??

Cheers,
Matt
0
Comment
Question by:dabdowb
  • 7
  • 5
  • 2
14 Comments
 
LVL 13

Expert Comment

by:Torrwin
ID: 26131256
Instead of granting the users permissions on the database (i.e. checking a box in the "Database Role Membership" box), try granting them permissions on the tables themselves.  Click "Securables" on the left hand side and then "Add".  Choose "Specific Objects", then "Tables" under "Object Types".  Press "Browse" and pick your tables.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26131292
create database roles for the the group of operations, or departments, etc.... grant permissions to these roles ...
add your users to the appropriate roles.
remove permissions defined for the users.
0
 

Author Comment

by:dabdowb
ID: 26131354
I am assuming "Securables" is under Security->Users->right click on userid->Properties??
If so, I just tested that, but when I went to link the tables in my Access database, even after refreshing the SQL Server, I still could see and link to any of the tables in the database.
Also, it does not let me Remove any of the tables from the "Securables" section, which may need to happen from time to time.  I would have assumed I could have clicked on one of the Securables I put there, and then the Remove button would be available, but it always remains grayed out.
Thoughts?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:dabdowb
ID: 26131456
tigin44,
There is a role already assigned to this userid.  It is called db_datareader, but I don't see where I can add Permissions to that role.  Or where to remove permissions defined for the users.
Recall...new to SQL Server.
0
 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 200 total points
ID: 26131523
its a good practice to manage user permissions over database roles in sql server..  You define for your needs as many roles as you needed... Grant enaugh permissions to the roles. Add users to the roles. One user may be a member of one or many roles. Do not manage permissions over users.. that will be error prone and hard to manage. db_datareader is a sytem defined role. it gives the users to access the contents of tables or views.. You cant distinguish your groups by that role... instead build your own roles. If you follow th esteps and  build your roles you would do any restriction on the users. But you should have at least dba privileges to do this. As I understand from your post your account has limited privileges.
0
 
LVL 13

Accepted Solution

by:
Torrwin earned 1800 total points
ID: 26131530
dabdowb,

Yes, you are in the correct section.  On the tables the user isn't supposed to seeing, try specifically denying all permission to.
0
 

Author Comment

by:dabdowb
ID: 26131600
tigin44,
If I am reading your post correctly, it sounds like you are advising against the idea from Torrwin due to ease of management?  I can relate to this thought, and still will be testing both ideas as I do have full dba access to the server...I am currently signed on as the system administrator, hence why I am unclear about the "Remove" feature being missing from Torrwin's proposal.
I am attempting to create  a New Role, but who would the Owner and Owned Schemas be?  On the Owned Schema, would I go to the default db_datareader, or should I create a seperate Schema as well?
Sorry for all the questions, just trying to understand it all and set it up correctly.
Thanks
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 26131707
No, our ideas are not mutually exclusive.  Yes, you can and should move to using groups instead of specific users, but as you are new to SQL Server I did not want to confuse you with all of that.

However, whether you use a single user or a group, the permissions applied to the object will be the same as suggested above.  If you use db_datareader, the users will be able to see all your tables.  The theory here is to deny all permission to the user except the ones you specifically grant.
0
 

Author Comment

by:dabdowb
ID: 26132149
Torrwin, understanding conceptually, just not able to execute idea yet.  I went back to try to deny the specific tables as you mentioned previously, but I don't see where I can do that.  If I go to the same area, I can only add more tables to the list...as for deny, grant, etc, that is specific to ALTER, SELECT, UPDATE, etc., not the tables themselves.  I do remember seeing something like this somewhere before when I was totally just exploring a few weeks ago, but I can't refind my way.

Thoughts?
0
 

Author Comment

by:dabdowb
ID: 26132324
Update...so I found if I go into the properties on a table itself, I can go to Permissions, and Deny the select feature to the table (which is the only thing the current ID is allowed to do) and it removes it from the selection list when linking via Access.  
There has got to be an easier way I would imagine, to do this in bulk....likely the more complex solution that tiggin44 mentioned, but just wanted to check.  Any thoughts Torrwin?
Thanks
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 26132374
Yes, you could create a stored procedure to run the permissions with variables for the user/group name and table name.

I've not done this for a table specifically, but i've done it for other stored procedures.  Here's a sample to prod you in the right direction.  (This may be worth another EE question)  For example, I could run this to grant permissions for "dbo", "procedure1", and "user1".
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:	
-- Create date: 
-- Description:	This procedure grants the given user
--              permission to Execute the given
--              stored procedure and denies all other
--              permissions
-- =============================================
CREATE PROCEDURE [dbo].[usp_Grant_SP_Permission](@ownerName VARCHAR(50), @spName VARCHAR(50), @userName varchar(50))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @CMD1 varchar(8000)

	SELECT @CMD1 = 'GRANT EXECUTE ON [' + @ownerName + '].[' + @spName + '] TO ' + @userName + ';'
	EXEC (@CMD1)

	SELECT @CMD1 = 'DENY ALTER ON [' + @ownerName + '].[' + @spName + '] TO ' + @userName + ';'
	EXEC (@CMD1)	

	SELECT @CMD1 = 'DENY TAKE OWNERSHIP ON [' + @ownerName + '].[' + @spName + '] TO ' + @userName + ';'
	EXEC (@CMD1)
	
	SELECT @CMD1 = 'DENY VIEW DEFINITION ON [' + @ownerName + '].[' + @spName + '] TO ' + @userName + ';'
	EXEC (@CMD1)

END

Open in new window

0
 

Author Comment

by:dabdowb
ID: 26139464
While the script is a nice touch, a bit over and above where I am at currently.  I managed to go in and change access to all the tables and views in my database, demand_plan, painfully, but all the "system" views are still showing up.  If I try to "deny" them like I did with all the other tables, it throws up error messages and will not let me do it.  Is there a way to get the System views to not show up in Access 2000 when trying to link?

Thanks
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 26139989
I didn't think that this was possible, so I did a little more research to confirm and here's a snippet of what I found:

One of the fundamental design criteria behind the ODBC standard is the ability to run ad-hoc queries. In order to do this, the ODBC driver needs to access certain system tables. It is therefore impossible to block access to these tables if you are connecting via ODBC - if you were successful in blocking access to the tables then ODBC would no longer work.

You can read more here:
http://www.sqlservercentral.com/Forums/Topic671366-359-1.aspx
0
 

Author Closing Comment

by:dabdowb
ID: 31670387
Torrwin, thank you for all your work on this one and sticking with the solution to the end.  Tigin44, thanks for discussing the better way of doing what I needed, but as Torrwin noted, being new to SQL, it was/is a more complex solution than I needed for this situation.  I did give some credit to your reply though since even Torrwin agreed it was a viable solution.  Happy New Year!!!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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