Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore (database onwer)


Restore (database onwer)

Author
Message
river1
river1
SSC Eights!
SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)

Group: General Forum Members
Points: 952 Visits: 1357
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
Greg Charles
Greg Charles
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4089 Visits: 5823
Since we like our databases to be owned by sa, I just run sp_changedbowner 'sa' after the restore.

Greg
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6334 Visits: 13687
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.

---------------------------------------------------------------------
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9833
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6334 Visits: 13687
thanks Jeff, now I have to try and find the thread and I can't even remember what the original subject matter was! Ermm

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?

---------------------------------------------------------------------
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6334 Visits: 13687
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.Cool

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

---------------------------------------------------------------------
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9833
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6334 Visits: 13687
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.

---------------------------------------------------------------------
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9833
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6334 Visits: 13687
?

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

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search