question

learnSql avatar image
learnSql asked

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.
schemauser
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you mean staging/Temp tables or # tables? Would they need access all tables or just a selection?
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

learnSql avatar image
learnSql answered
ok . let me try this ..
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rydall2000 avatar image
rydall2000 answered
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?.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.