Creating a database who is the owner?

Hi there

I running into some serious problems trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. In most cases I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication. eg:Data Source=ComputerName\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".

From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName]   Go   CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code? Sorry for multiple question, but pulling hair out trying to get this working.

June 29th, 2015 5:59pm

By default, if you're connected via an owner (or better) account, the default schema is dbo, and you could admit it.

HOWEVER, you're better of providing explicit paths, and this way you can be completely sure where your objects are.

Free Windows Admin Tool Kit Click here and download it now
June 29th, 2015 6:04pm

Patrick, confused! If I'm connected via an owner or better account, what does that mean? I'm connecting to a brand new SQL Server Express engine as the Windows user who installed it. As such I connect to the SQL Server via Windows Authentication. Does that mean any object created via script while connected via Windows Authentication has dbo as a default? Is that the owner account or better? And, sorry but explicit paths? Not sure I understand what you are saying? Example please.

Thanks

June 29th, 2015 7:09pm

Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?

When you create a new database, the database owner is the individual who created the database (the Windows account in this case).  The database owner is by definition mapped to the dbo user.  The default schema of the dbo user is always the dbo schema so the dbo schema is used for unqualified object names.

Regardless of the default schema, I suggest schema-qualifying objects in all cases to avoid ambiguity.

Free Windows Admin Tool Kit Click here and download it now
June 29th, 2015 9:18pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics