Why a Single SQL Login
Contents
Why a Single SQL Login?
We recommed a single SQL login for all WireCAD clients simply for ease of use.
Background
Everytime a user creates a new WireCAD project a new database is created on the server. Hence the requirement that the WireCAD user must be granted db_creator permissions.
Once the new project database is created it is owned by the creator. Other users will not be able to access the data unless granted permission to do so.
It gets more complicated when you consider that WireCAD also requires a Global Equipment database that may be running on the SQL Server as well.
So any given client will be connected to two databases concurrently, the Global Equipment database and the database for the current project.
Single User Scenario
Let's say that we allow the database to accept SQL and Window logins and that we have a single user login call wirecad_user with an appropriate password.
Further let's give that user ownership of the Global Equipment database.
Still further let's have a client login in using that user and create a new project called Test Project. In so creating Test Project wirecad_user becomes the owner of the database Test Project.
Now let's let user b open Test Project from his machine. No problem because as far as the SQL Server is the same owner is accessing the database.
Windows Authentication Scenario
Let's say that we only allow Windows authentication and that we have two users named Fred and Barny.
The dba will need to grant permissions on the Global Equipment database to Fred and Barny to allow them to read and write.
In addition both Fred and Barny will need to be granted db_creator permissions so they can both create new WireCAD projects.
So let's have Fred create a new project called Freds Project. Fred is now the owner of the project and can work without issue. But now let's have Barny open the new Freds Project. No bueno. Until the dba grants permissions on the Freds Project to Barny, Barny is locked out.
This can be circumvented with the clever us of groups. Grant the group ownership of the Gobal Equipment Database and the appropriate read/write permissions and all can be well.
To Sum Up
Some dba's and sysadmins require a Windows Authentication only approach. This can be done as shown above but it creates a lot more work for the dba and potential frustration for the users.
