September 26, 2022 at 2:56 pm
For some folks that have gotten used to working in Oracle where a user gets assigned an schema and they have permissions to perform certain things within that schema, I would like to set up something similar access-wise.
So, the way I would want is to create individual schema for these users in a single ms sql database. Then give them permission to only be able to create tables in the schema that they would be assigned to.
Trying to figure out how to go about doing this or if there is a better way.
Thanks...
September 26, 2022 at 4:26 pm
You can definitely do that. What are the roles of these users? What are the uses of the tables they would create? Do they really need their own permanent tables, or would temp tables suffice?
Do they need to interact with tables, procedures, etc. in other schemas?
September 26, 2022 at 9:13 pm
Thank you. The role of the users are developers. I would create a database in the production database server. These users will work on creating tables in that database which they will later export to a production database in the same server. So, yes they need to be able to create permanent tables.
Since this would be first time architecting the access this way I might have to play around a bit I guess. But some tips would be appreciated.
September 26, 2022 at 9:31 pm
You said "they will later export to a production database in the same server". Are you saying they will export tables in their own schema to their own schema in a production database (in which case you also have to replicate the schemas and permissions to production)? If so, why? If not, what did that mean? Don't you want tables in production using "real" production schemas and not developer-specific schemas?
Particularly if it's only about creating tables, then in development I'd recommend assigning developers to database roles with appropriate permissions, and let developers create tables in development environment. The user-specific schema approach sounds unnecessarily complex, but maybe I'm still not understanding the concerns, intent, & workflow.
Do you source control databases (relates to next recommendation & to production deployment)?
If allowed/feasible at your company, I recommend having developers develop & test on their local sandbox environments (database & applications), and only deploying to shared dev environment when ready for dev integration testing and/or committed to source control.
September 26, 2022 at 9:54 pm
They will not have a schema in the production database. Yes, providing read write access to them in the "sample" or "sandbox" database would have made life a lot easier but they do not want to touch eachother's tables or rather they do not want other developer to accidentally write their tables. This was more so how they had control using the schema in oracle and ms sql is different.
Not sure if creating an individual database for a developer would be considered a good practice. I was hoping that after they have a working table in their schema they would export it out or recreate one in production.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply