Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
monty
Posting Yak Master
130 Posts |
 Posted - 2007-04-20 : 02:45:56
|
| All,I've noticed recently that the owners of several databases has been changing. what could be the possible reasons for the owners being changed?is It likely that the ownership change was inadvertent and occurred if database was added to the server using the "attach database" wizard through Enterprise Manager, which would automatically default the owner to the first user in the alphabetized list of logins?does database refresh or detached/reattached, the DBID has the possibility of changing which is how the default database is identified for a login (DBID). is default database for logins, be set to 'master' for all users recommended? i belive if it is done then on system tables objects can be modified correct me if iam wrong?what are all the best practices regarding database ownersipits me monty |
|
|
craig79
Starting Member
33 Posts |
Posted - 2007-04-20 : 08:59:38
|
| Hi,I only have few lines to add.Setting the default database to master for all logins is not recommended.If u have a login dedicated to a group of Developers or data readers then assign that particular database to be the default for that login.And u can restrict access to system objects by unchecking the "Allow modification to be made directly to the system catalogs" in the Server Setting Property of ur Server. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-20 : 09:19:47
|
| "Setting the default database to master for all logins is not recommended"The problem with not setting the default to Master is that if the actual default database is "unavailable" the user won't be able to connect ...Kristen |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2007-04-20 : 15:36:29
|
quote:
Originally posted by monty
is default database for logins, be set to 'master' for all users recommended? i belive if it is done then on system tables objects can be modified correct me if iam wrong?
Setting a default database only sets the database that the user opens when the connection is established. It does not apply extra permissions to the user allowing them to modify system tables / objects. If the user has a default database of master and they attempt to select from sysxlogins, for example, they will be denied unless the user has been granted to do so. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-04-20 : 16:15:55
|
| I believe the login that restores or attaches a database is made the owner of that database. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-20 : 16:46:35
|
| Yes for db attach, not for restore. The db will have blank db owner if original owner doesn't exist on target server. |
|
|
|
|
|
|
security