Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Restore (database onwer) Expand / Collapse
Author
Message
Posted Friday, May 14, 2010 9:56 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
Hi,

When i restore a tabase by using:


restore database db_name from disk ='c:\test.bak' , after the restore the owner of the database becomes the Login with witch we made the restore.


What do you guys do? do you let the owner be the login that restored the database, or do you use other method?


Thank you
Post #922164
Posted Friday, May 14, 2010 10:28 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:32 AM
Points: 4,056, Visits: 5,182
Since we like our databases to be owned by sa, I just run sp_changedbowner 'sa' after the restore.

Greg
Post #922177
Posted Friday, May 14, 2010 12:44 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 5,849, Visits: 12,587
Some third party apps require a particular ID be the dbo, so watch out for that.

Having the db owned by an ID that restored it is not in itself a problem, worse that can happen is if the person leaves the company say and their ID is removed from AD (presuming windows authentication) then the owner becomes 'unknown'. the database will continue to function, sp_helpdb fails though, which can be a PITA.

Usual standard is to make all dbs have the same owner, usually SA. There is a security risk in this though, IIRC to do with cross database chaining, our resident security expert Brian Kelley is not a fan.


---------------------------------------------------------------------

Post #922259
Posted Friday, May 14, 2010 2:58 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
george sibbald (5/14/2010)
Some third party apps require a particular ID be the dbo, so watch out for that.

Having the db owned by an ID that restored it is not in itself a problem, worse that can happen is if the person leaves the company say and their ID is removed from AD (presuming windows authentication) then the owner becomes 'unknown'. the database will continue to function, sp_helpdb fails though, which can be a PITA.

Usual standard is to make all dbs have the same owner, usually SA. There is a security risk in this though, IIRC to do with cross database chaining, our resident security expert Brian Kelley is not a fan.


What security risk is their with using SA as the owner? I am not aware of any security issues - unless you have users accessing the system using the SA account (why?).

Not sure how cross database ownership chaining would be affected by the owner of the database.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #922323
Posted Friday, May 14, 2010 3:49 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 5,849, Visits: 12,587
thanks Jeff, now I have to try and find the thread and I can't even remember what the original subject matter was!

It was something around it being easier to call objects in other databases as the owner would be the same. I used IIRC for a reason.

I'll try and find the thread. Maybe I'll ping Brian?


---------------------------------------------------------------------

Post #922352
Posted Friday, May 14, 2010 4:10 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 5,849, Visits: 12,587
This isn't the thread I was thinking of but Brian raises the same point:

http://www.sqlservercentral.com/Forums/Topic418501-149-1.aspx

so, should cross database chaining be enabled, people could access objects in other databases without permission checks. People might enable it and not realise this.

I think it is a a small risk myself but worth noting. Do with it what you will.

edit - and its not so much the owner is SA but the owner of all databases is the same.


---------------------------------------------------------------------

Post #922358
Posted Friday, May 14, 2010 4:18 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
George,

I can see how that might be an issue - if that is option is enabled. However, if you have enabled that option - then you obviously need to be able to access objects in other databases without having to add users to all databases.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #922360
Posted Friday, May 14, 2010 4:52 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 5,849, Visits: 12,587
What I take from this is if all your databases have the same owner,and a requirement for database chaining comes up, enable it at the database level, not the server level.

---------------------------------------------------------------------

Post #922369
Posted Friday, May 14, 2010 5:25 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
george sibbald (5/14/2010)
What I take from this is if all your databases have the same owner,and a requirement for database chaining comes up, enable it at the database level, not the server level.


Agreed - although there isn't a chaining option at the database level. You would set that up by granting access to the necessary users - or, create a user with no login - the appropriate certificate and access and then use execute as for those procedures that need access in the other database. Or, other options...


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #922383
Posted Friday, May 14, 2010 5:35 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 5,849, Visits: 12,587
?

http://msdn.microsoft.com/en-us/library/ms188694(SQL.90).aspx


---------------------------------------------------------------------

Post #922386
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse