How to create login that has access to only certain tables in a database

[dgillz]

SQL 2008R2. The title is pretty self explanatory. I need a SQL login that could run select and update queries, but be restricted from doing so on certain tables within a database.

I can grant access to the database under "user mapping", then I went to "secureables" to try to restrict access to certain financial tables. I couldn't find anything that looked promising. Surely there is a way to do this? Any insights appreciated.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[ElEye]

Have you tried creating a DB that only contains links to the specific tables?

Dave
[

http://flashspot.com

]

 

[dgillz]

No, because that is not the requirement. I'll consider that if nothing else works.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[fredericofonseca]

create login
create user on the database - do not give any of the permissions listed if doing it through the wizard
set the user default schema - either dbo or its own.

create a role and assign the user to it (best practice)

grant select and update (delete also????) on the individual tables to the role or the user if you decide not to create a role

all done throuth T-sql would be the easiest


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[SQLBill]

Yet another option if this has to be done for several users...create views of just the tables/data the users need to see and only give them access to the views.

But Frederico's suggestion may be the best. Create a role in the database like LimitedUser. Grant it the appropriate rights to the tables and then add the users to that role.

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875