August 8, 2011 at 5:41 am
tried this on sql 2005 security but no response yet, so ...
Hi
I am having the following situation and not quite sure how to get on with it, would be grateful for any assistance provided.
DB details are:
SQL 2005 SP4
a DB migrated from SQL 2000
contains all the objects in one schema, which is DBO
Front end is ASP Web application
currently used by admin staff in the organization to record infomation about the non admin staff who belongs to the organization.
I need to extend this system so that non admin staff can access this DB and view certain information recorded by the admin staff but they should not alter those info. Also they should be able to record other info about them in the DB in other tables.
I am planning the following to achieve this:
create a new schema in which create all the non admin user's tables
create a login with only db reader role - which will be used in my web application to log into the DB when non admin users accessing the system
create a user in the DB and assign above login to it and make this user as owner of the above schema
By this I am hoping the new user can write any tables in the new schema, and read (but not write) all the objects in the dbo schema
Please let me know whether this is the right way to go, if not please let me know how to do it?
Thanks.
August 10, 2011 at 9:03 pm
Sounds like a good starting point to me. Some things to evaluate:
- Consider fully-qualifying all object references from your application. It is a best practice from a readability standpoint and has a small performance benefit. This will be especially important for the non-admin users since they may be interacting with multiple schemas.
- Check the Default Schema option at the user level for existing and new users and decide on a standard approach.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply