Create Sql Server User in Production with Limited Access
There is a requirement to create a user in Production Server and assign some space to them to create teamp tables if they need. we want to provide only read only access to the user to access data from Production Database . Could you please assist . I am not sure if i Need to create one schema for each user and how the space will be managed.
I would create a new database where the users can store their own objects, and give them a personal schema each in that database. And in the production database assign them to the db_datareader role (and perhaps even db_denydatawriter to ensure they don't get any write access rights through inheritance from other windows groups). To simplify login management, I would create a windows AD group and make that group a login in your sql server instance, and create a user in the production database from that login (and assign db_datareader and db_denydatawriter). In the user database, where the users are to store their own tables, there will be some more management to do. Create a user in the database for each windows user. Then create a schema for each user, with AUTHORIZATION = that user. That way, the user becomes schema owner and gets full permission in their own schema, but no permissions to other users' schemas.
can i ask a question , we have a SQL database running on a group share, the user has access to that database but that user has only limited access to some items, the main user with full access wants her rights changed. we have a SQL instance on the local computer with a mapped drive and they access the database via MS Access 2010 and above. i can see her in the SO_DB.accde file if i go to db_t_person at the left of the open database, but i need some guidance on how to change the permissions?.