|
|
Posted by mplpost on 05/15/07 05:05
Our web application requires that when a new Organization is created,a
separate Database is to be created for the corresponding organization,
so that each organization remains a separate entity with their
corresponding employees(This is our clients requirement, so that they
can later provide the backup of the database if the organization
decides to move out). And for that what we intend to do is restore the
backup of database which already has the objects created in it.So we
intend to restore a database for the new organization from the web
page.
We are using SQL Server 2005 Enterprise Edition and the
authentication is SQL Server authentication, and the 'xy' login has
been assigned to the 'dbcreator' fixed server role only(This may be
not relevant but just in case, the 'xy' login has been assigned
'db_owner' fixed db role
in the main db. The web application uses this login to connect to the
database). When a new Organization is created, the database owner of
the new DB should be xy, and the user mapping for the login to the
'NewOrg' database should be 'dbo'. But in this case after restoring
the database
template using the SQLSMO from the application(same thing happens
after restoring the database from query analyzer using the xy login)
there is no user mapping to be seen and we are not able to connect to
the 'NewOrg' database using the xy login.
I think most will suggest to use 'exec sp_changedbowner' to update
the dbo, but for this we need sysadmin privileges, which we do not
have for the xy login.
Please suggest the best way to go about this particular
scenario(currently we are running the object creation script after
creating the database but this is time consuming, so pls suggest only
database restore method) and the also please suggest if any other
fixed server roles can be used without compromising security. Seems
this is a design issue, pls also suggest if this post is to be made in
some other appropriate groups.
Thanks,
MPL
Navigation:
[Reply to this message]
|