Have Geek Brain

Simplify SQL Server I / O / Owners / Schemas / Roles / Executives, etc.

I often develop small web applications using ASP.NET and SQL Server 2005. My databases are always only accessible to one application via ASP.NET web service or something similar.

When I develop an application and move the database back and forth between my development machine (SQLExpress 2008) and the hosted server (SQL Server 2005), I always end up with a jumble of owners / roles / schemas / logins, etc. it seems that different parts of the database arise and control.

I only know enough about SQL Server to be dangerous and I just want the databases to be developed, but I always have to change db and end up with different error resolutions (for example when trying to open a chart after db has been moved).

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

Like some command that sets all these parts to "dbo" or something like that?

0


a source to share


4 answers


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



+1


a source


Unfortunately, there are some easy answers to your question, but you probably won't like it 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 the management tools you are looking for to manage schemas, users ... I think this is now included in Visual Studio 2008 Development Edition



The second approach is to enable SQL Server administration and get a comfertable with DDL scripting. The goal is to get to the point where you can deploy changes to SQL Server predictively from your development environment to testing and ultimately to production.

+1


a source


The crux of the problem is that you are navigating back and forth through the database itself. Databases are surprisingly tightly coupled to their host SQL Server instance, by mapping username to username and other parameters such as encryption keys, use of msdb procedures, and maintenance plans, among others. You can minimize some of the impact by carefully using SQL Exclusive Authentication, but this does not completely fix the problem. The real solution is that you should have a deploy script and apply the changes to the production database by executing the T-SQL script (s) that were previously tested on your development db, not by moving the db 'back and forth. "

0


a source


I create all objects that specifically indicate that I want them to be created as dbo.

so to create a proc i do

Create procedure dbo.myprocname

0


a source






More articles:


All Articles