SQL Server Express Chicken or Egg issue with logins

tags:

sql-server-express

views:

answers:

+2 Q:

SQL Server Express Chicken or Egg issue with logins

This seems like a silly question, but it has me stumped.

When setting up a new Database in SQL Server Management Studio Express (for a SQL Server 2005 Express DB) you pick a default owner.

When you create a new Login in it, you pick a default database.

Wouldn't the owner of the DB also be a 'login'? Or am I confusing those two concepts? If I'm not, it seems like a bit of a chicken and egg issue...how to I assign a default owner to a DB if I have to first create the Login. But the login requires a default DB.

+4 A:

Overy db owner is a login, but not every login is an db owner.

There are a number of pre-deployed logins, most notably the sysadmin members, one of them being the user that installed the SQL Express isnstance, which is provissioned during instalation. This login can initiate the process by creating the first login to own the first database.

Also there are a number of databases that are deployed initially (master, tempdb, model, msdb, mssqlsystemresource) which are owned by 'sa', another built-in login.

Remus Rusanu 2009-12-15 01:31:45

So, maybe I need to ask a different question. How do I create a new DB owner?

DA 2009-12-15 02:26:32

'db owner' is not an object per se, like a 'login'. DB Owner is an after-fact: is the login that owns a specific db. To create a login, use `create login [domain\user] from windows`. To change the owner of a database, use `alter authorization on database::[dbname] to [domain\user]`. Any database created by a login automatically belongs to that login, so he will become the DB owner for that particular db.

Remus Rusanu 2009-12-15 03:08:01

@Remus in SQL Management Studio, when you create a LOGIN, it wants to associate that login with a default DB. When you create a DB, it wants to associate an owner. That's where I'm confused. To create the login, i need the DB setup. To create the DB, I need the login setup.

DA 2009-12-15 04:39:21

I'm sure I'm just not understanding something obvious. ;O)

DA 2009-12-15 04:39:52

The default DB is optional. You can leave it at master.

Remus Rusanu 2009-12-15 05:02:59

And you can change it *after* you create the database: create login, create db, change default db for login.

Remus Rusanu 2009-12-15 05:03:56

ansaurus

tags:

views:

answers:

SQL Server Express Chicken or Egg issue with logins

related questions