x

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.

more ▼

asked Jan 30 at 04:08 AM in Default

avatar image

learnSql
1

Do you mean staging/Temp tables or # tables? Would they need access all tables or just a selection?

Jan 30 at 07:31 AM sp_lock
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Jan 30 at 08:29 AM

avatar image

Magnus Ahlkvist
22.3k 20 43 43

(comments are locked)
10|1200 characters needed characters left

ok . let me try this ..

more ▼

answered Jan 31 at 05:46 AM

avatar image

learnSql
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x44
x32

asked: Jan 30 at 04:08 AM

Seen: 39 times

Last Updated: Jan 31 at 05:46 AM

Copyright 2018 Redgate Software. Privacy Policy