Greetings all!
In our database environment, we create a dedicated database whenever we get a new case. Each case has a project team assigned to it and the members of the project team are assigned read-only access to their databases.
I have written an automated process that creates each new database, loads the source data, runs some standard clean-up procedures and generates exception reports. One of the catches is that we have to manually set up permissions for each database each time a new one is created. This can be a tedious process as members of the project teams can change with each case. What I would like to do is add the creation of a database role for each new database and assign the role to the members of the project team. My question is can this be automated through SQL script? I have some metadata tables that capture the Server Name, the Database Name, the Table Names and the UserIDs of the members of the project team working on the case, so the necessary information is all readily available. If anyone has ever automated something like this or perhaps has a better approach, I would certainly appreciate your feedback.
Thanks in advance,
Nate
In our database environment, we create a dedicated database whenever we get a new case. Each case has a project team assigned to it and the members of the project team are assigned read-only access to their databases.
I have written an automated process that creates each new database, loads the source data, runs some standard clean-up procedures and generates exception reports. One of the catches is that we have to manually set up permissions for each database each time a new one is created. This can be a tedious process as members of the project teams can change with each case. What I would like to do is add the creation of a database role for each new database and assign the role to the members of the project team. My question is can this be automated through SQL script? I have some metadata tables that capture the Server Name, the Database Name, the Table Names and the UserIDs of the members of the project team working on the case, so the necessary information is all readily available. If anyone has ever automated something like this or perhaps has a better approach, I would certainly appreciate your feedback.
Thanks in advance,
Nate
