Database / Table Permissions

Question

Database / Table Permissions

gregorym

Grasshopper

Points: 10
More actions
December 13, 2010 at 7:19 am

#230434

Hi,
What is the best method for restricting user access to a SQL database, we provide reporting in excel with ODBC connector, but would like to be able to restrict user access to only specific database and tables.
SQL2005 Std - MS Excel 2003
Is this possible? If so, then how?
Thank you.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

gregorym Grasshopper Points: 10 More actions · Answer 1

Noticed there has been quite a few views of this post.

What I'm looking to find out is whether it is possible to restrict user access to a database table? I have looked at Standard Roles and Application roles, but this does not seem to achieve my required outcome.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

It is of course possible. The mechanism to control this is user permissions. You can setup a new database user, setup whatever permissions you want and then use that user in your connection string.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gregorym Grasshopper Points: 10 More actions · Answer 3

Hi Sean,

Thanks for your reply.

So do you suggest that I create a new user to use SQL Authentication? I don't seem to see anywhere that I can allow access to selected tables only instead of the whole database?

Thanks again.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

You have to set the permissions for the user and allow them access to the database first. Then you can allow/disallow for each database object. Hope that makes sense.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gregorym Grasshopper Points: 10 More actions · Answer 5

I created a SQL Authentication user, left defaults:

server role = public,

user mappings = schema datareader on the selected database

Database Role membership = public and datareader

Securables = (is this where control of objects takes place?)

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Now you need to go the database you want to allow access to and allow the user to have whatever access you want to the individual db objects. Sounds like you are on the right path.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gregorym Grasshopper Points: 10 More actions · Answer 7

Excellent, I now see this in securables on the DB, been struggling with this one for a while thanks again for your help.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Happy to help. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pietlinden SSC Guru Points: 63503 More actions · Answer 9

If the database is complicated, you may want to create views, stored procedures, and table-valued functions to return data to the users. Then you can basically hide the complexity of the database from them. (They don't need to know and it usually just confuses people that aren't used to dealing with the database.) Also, you can limit access to columns/rows in your underlying tables.

kum12 Newbie Points: 3 More actions · Answer 10

You have to set the permissions for the user and allow them access to the database first.

Wedding gifts[/url]

gregorym Grasshopper Points: 10 More actions · Answer 11

Thank you for all the responses, the views is an interesting idea. Creating a user, giving the user access to the database, then under securables on the database, then giving access to only the tables required which seems to do the trick.