SQL Server 2016 User accounts permissions across multiple schemas

Hi, I am new to SQL Server DBA role, I need help understanding the best way to approach my problem.

The Database: Database: DEV1 Security: Mixed mode, but all users described below are using local SQL accounts (I would prefer Windows accounts - but not possible for this implementation) Users / groups: DataDevs: User1, User2, User3 DbDevs: UserX, UserY Schemas: DG, ER, SAND

The Problem: How do I set up my security so that:

Schema DG: User1, User2 and User3 have db_Datareader, UserX has db_ddladmin Schema ER: User1, User2 and User3 have db_Datareader, UserX has db_ddladmin Schema SAND: User1, User2 and User3 have db_ddladmin, db_datareader, db_datawriter, UserX has db_ddladmin

Ideally, I would have one or more groups or roles defined that new users would be added into which automatically grants them the appropriate permissions. So that when I get a new user User4, I just add them to a group to automatically inherit the required permission structure.

I am really new to SQL Server, so detailed instructions would be appreciated. Thanks in advance

more ▼

asked Jul 01, 2017 at 03:30 AM in Default

avatar image


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

0 answers: sort voted first
Be the first one to answer this question
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



Answers and Comments

SQL Server Central

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



asked: Jul 01, 2017 at 03:30 AM

Seen: 51 times

Last Updated: Jul 01, 2017 at 03:30 AM

Copyright 2018 Redgate Software. Privacy Policy