Simplifying SQL Server's Logins/Owners/Schemas/Roles/Principals etc...

Question

I frequently develop small internet applications using ASP.NET and SQL Server 2005. My databases are always only accessed by one application through an ASP.NET web service or something similar.

When I'm developing an application and moving a database back and forth between my development computer (SQLExpress 2008) and hosted server (SQL Server 2005) invariably I end up getting a hodgepodge of owners/roles/schemas/logins etc that seem to crop up and take control of various pieces of the database.

I only know enough about SQL Server to be dangerous, and I just want to get databases designed, but I invariably have to change the db and end up getting various permissions errors (for example when trying to open a diagram after transferring a db).

Is there a way to simply tell SQL Server Management Studio "Hey, I'm the only guy who's going to use this stupid db, so just let me do it?"

Like some command that sets all of these pieces to 'dbo' or something like that?

Answer 1

I am assuming you are using Windows. Make sure you are connecting to SQL with Windows authentication and give your account Sysadmin permissions. Now everything you create (Create Table tablename, Create View viewname) will automatically have dbo as the owner.

Answer 2

The crux of the problem is that you move back and forth the database itself. Databases are surprisingly tightly coupled to their host SQL instance, by the login to user mapping and by other settings as well, like encryption keys, usage of msdb procedures and maintenance plans among other. You could minimize some of the impact by carefully using exlclusively SQL Authentication, but that won't eliminate the problem completely. The real solution is that you should have a deployment script and apply changes to your production database(s) via executing T-SQL script(s) that you have previously tested on your development db, not by moving a db 'back and forth'.

Answer 3

Unfortunatly there is a couple of easy answers to your question, but you are probably not going to like them because they will require you to invest in SQL Server skills.

The first tool you should look at is Visual Studio 2008 Database Developer Edition. This version has management tools you are looking for to manage schemas, users, ... I thinks this is now included with Visual Studio 2008 Development Edition

The second approach is to embrace SQL Server administration and get comfertable with writting DDL scripts. The goal is to get to the point where you can deploy changes to SQL Server in a predicatble manner from your development enviornment to test and ultimatly to production.

Answer 4

I create all objects specifically specifying I want them created as dbo.

so to create a proc I do

Create Procedure dbo.myprocname