January 14, 2011 at 9:51 am
I have created new database and create new user and assign it to the dabase leaving default 'pblic' rights. I want to use this useid to use in connection string to link database in access application. When I use Linked Table manager to connect sql server it connects the server and database but can't see any table. Do I need to grant permission for each individual table?
January 14, 2011 at 11:19 am
The public role is a role every user is a member of. It has permissions to hit some things, but no user defined objects (like created tables) by default. And you shouldn't change this, because then anyone who ever gets made a member of the database gets that access, whether you wanted them to or not.
In SQL Server 2005 and above, if you don't have permission to an object (such as SELECT permission against a table), you can't see the object. This is a change from SQL Server 2000. So yes, the user would have to have permission first.
The best way to do this is to create a role with a name that is fitting to what the role will do. For instance, Analyst or Admin or Customer. Give permissions to the role. Make the user a member of the role. That way, if you ever have to give someone else identical permissions, you can simply make them a member of the role.
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply