question

Budorat avatar image
Budorat asked

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
sql serversecuritysql server 2014schemaroles
10 |1200

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

0 Answers

·

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.