Database Owner cannot connect

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

Re: Database Owner cannot connect

(mplpost@yahoo.com) writes:
> 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.

Was the database originally created on the same server or on a different
server? Who is the owner of the original database?

> We are using SQL Server 2005 Enterprise Edition

Which Service Pack are you on? (There is some new commands in SP2
that may be useful.)

> 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.

You could put ALTER AUTHORISZATION in a stored procedure that is signed
by a certificate, and then you grant a login created from that certificate
the rights change database owner. That login is not a real login that
can actually connect. For more information about this, see an article
on my web site: http://www.sommarskog.se/grantperm.html.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Database Owner cannot connect

Thanks for the reply.

> Was the database originally created on the same server or on a different
> server? Who is the owner of the original database?

The Database was created on the same server. The owner of the original
database is 'sa'.

> > We are using SQL Server 2005 Enterprise Edition
>
> Which Service Pack are you on? (There is some new commands in SP2
> that may be useful.)

No service pack yet applied, its RTM. Pls suggest the commands in SP2,
will try it out.


> You could put ALTER AUTHORISZATION in a stored procedure that is signed
> by a certificate, and then you grant a login created from that certificate
> the rights change database owner. That login is not a real login that
> can actually connect. For more information about this, see an article
> on my web site:http://www.sommarskog.se/grantperm.html.

Will go throught the site, thanks.

Thanks,
MPLPost

Re: Database Owner cannot connect

> > Who is the owner of the original database?
This made me think about creating the database with the xy(the login
that connects from the web application) login and then backup the
database and then restore using the RESTORE command with the xy
login(xy is assigned to dbcreator role). This allowed me to connect
to the NewOrgDB with xy login.

But again I encountered the block when I tried restoring on a server
other than the server that I took the orginal DB backup from. Yes I
understand that this is due to the lost login-user mapping in the new
database.

Would like to restore the privilege of xy to be able to login to the
NewOrgDB without assigning it to sysadmin role. Can i make use of
sp_change_users_login in this case, but again I think we need to login
as a user with sysadmin role to NewOrgDB database and only then can i
execute this procedure, which I wouldnt be able to. Pls suggest any
solution.

Re: Database Owner cannot connect

(mplpost@yahoo.com) writes:
> This made me think about creating the database with the xy(the login
> that connects from the web application) login and then backup the
> database and then restore using the RESTORE command with the xy
> login(xy is assigned to dbcreator role). This allowed me to connect
> to the NewOrgDB with xy login.
>
> But again I encountered the block when I tried restoring on a server
> other than the server that I took the orginal DB backup from. Yes I
> understand that this is due to the lost login-user mapping in the new
> database.

I investigated this, and the situation is the same in both cases:
you end up with the xy login being the owner according to master, but
in the database, sys.database_principals.sid is the SID for the original
owner. Be that the local sa or the xy login on the original server.

It doesn't seem that sp_change_users_login would work, although I did
not try it. ALTER USER WITH LOGIN (new command in SP2) was not accepted
for dbo. What did work was sp_changedbowner.

> Would like to restore the privilege of xy to be able to login to the
> NewOrgDB without assigning it to sysadmin role. Can i make use of
> sp_change_users_login in this case, but again I think we need to login
> as a user with sysadmin role to NewOrgDB database and only then can i
> execute this procedure, which I wouldnt be able to. Pls suggest any
> solution.

I can see two ways out: one is write a signed stored procedure as I
discussed in my previous post. The great thing with this is that
you can package the database creation into this procedure as well,
and thereby the login does not even need dbcreator. The link again:
http://www.sommarskog.se/grantperm.html.

The other way is to create the source database with the xy login. Then
to avoid the login/user mapping problem on the other servers, create
the login on these servers with same SID as on the source server. This
is possible with

CREATE LOGIN xy WITH PASSWORD = 'Sehr hemlig!', SID = 0x....

Get the SID on the source server from sys.server_principals. If the login
already exists on the server, you need to drop it first.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Database Owner cannot connect

Thanks for your time and suggestions.

> I can see two ways out: one is write a signed stored procedure as I
> discussed in my previous post. The great thing with this is that
> you can package the database creation into this procedure as well,
> and thereby the login does not even need dbcreator. The link >again:http://www.sommarskog.se/grantperm.html.

Will go through the URL in detail. Did u mean that I need to include
the 'RESTORE DATABASE' command within the stored procedure.

> The other way is to create the source database with the xy login. Then
> to avoid the login/user mapping problem on the other servers, create
> the login on these servers with same SID as on the source server. This
> is possible with
>
> CREATE LOGIN xy WITH PASSWORD = 'Sehr hemlig!', SID = 0x....
>
> Get the SID on the source server from sys.server_principals. If the login
> already exists on the server, you need to drop it first.


Wow thats a good idea! But the problem is that we already have the
main database already created in the client site. The separate
database creation feature is being included as an enhancement.

Re: Database Owner cannot connect

(mplpost@yahoo.com) writes:
>> I can see two ways out: one is write a signed stored procedure as I
>> discussed in my previous post. The great thing with this is that
>> you can package the database creation into this procedure as well,
>> and thereby the login does not even need dbcreator. The link
>again:http://www.sommarskog.se/grantperm.html.
>
> Will go through the URL in detail. Did u mean that I need to include
> the 'RESTORE DATABASE' command within the stored procedure.

You don't need to. I just suggested that this could be a good idea,
as you then may have to add the xy login to the dbcreator role.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx