Setting up SQL Server repository

[skiwi]

Setting up a SQL server repository has steps

1) get SQL Server database created
2) run script to set up schema
3) transfer a project into the schema
and
4) use the project

Steps 1 and 2 require ddladmin rights.

You might think that steps 3 an 4, being user activities, require only datareader and datawriter.
But no, step 3 requires permission to execute SET INDENTITY INSERT ON/OFF and therefore ddladmin rights.

It seems to me that the initial script to set up the schema should have done this. Thoughts?

[Eve]

In our instructions it's part of the db setup instructions.

Data transfer copies everything from the source database, including ids. To do that it needs to control identies more finely. It's never going to work without admin rights. Same goes for Project | Data Management | Reset IDs.

[skiwi]

Thanks, i presume the instructions are:
http://www.sparxsystems.com/enterprise_architect_user_guide/12.0/projects_and_teams/upsizingtosqlserver.html

In our environment of DBAs are not EA users, and our EA users are not DBAs. We only give our EA users permissions read and write.


My feedback is to be more explicit about the permissions required for each step, e.g. under the heading "Transfer the project data to the repository"

I further note that http://www.sparxsystems.com/enterprise_architect_user_guide/12.0/projects_and_teams/performadatatransfer.html does not mention database schema permissions at all. In fact the implication of the last bullet point to me is that it is something a regular user can do.

[skiwi]

I have just tripped across
http://www.sparxsystems.com/enterprise_architect_user_guide/12.0/projects_and_teams/permissionlist.html

It doesn't mention what DB permissions might be required, just EA permissions.

[Eve]

http://www.sparxsystems.com/enterprise_architect_user_guide/12.0/projects_and_teams/upsizingtosqlserver.html

...


My feedback is to be more explicit about the permissions required for each step, e.g. under the heading "Transfer the project data to the repository"

To be fair, it does list the pre-requisite of "obtain access permissions to create a new database". There's nothing to indicate that has been revoked between stage 2 and stage 3.

I further note that http://www.sparxsystems.com/enterprise_architect_user_guide/12.0/projects_and_teams/performadatatransfer.html does not mention database schema permissions at all. In fact the implication of the last bullet point to me is that it is something a regular user can do.

That's logical. That page is not specific to any particular dbms, so it would hide the purpose of the page trying to describe the required db permissions for every supported dbms.

I have just tripped across
http://www.sparxsystems.com/enterprise_architect_user_guide/12.0/projects_and_teams/permissionlist.html

It doesn't mention what DB permissions might be required, just EA permissions.

That's because it's a list of EA permissions. Not a list of db permissions.

[skiwi]

OK, points taken, thanks.
Perhaps a page with a list of DB permissions is required.