|
|
Posted by Erland Sommarskog on 05/16/07 22:26
(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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|