27
votes
4answers
25k views

Should dbo schema be avoided?

When it comes to the dbo schema: Is it a best practice to avoid using the dbo schema when creating database objects? Why should the dbo schema be avoided or should it? Which database user should own ...
8
votes
5answers
3k views

Over use/correct use of schemas?

Having asked This question on Stackoverflow, I wondered where what I have done is correct/best practise. Basically, every object that I create is going into a schema with the schema name reflecting a ...
18
votes
2answers
1k views

Decision criteria on when to use a non-dbo schema vs a new Database

I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
8
votes
3answers
15k views

SQL 2008 R2 creates user/schema when Windows user creates tables

We added a server login and database user that map a Windows Group to a SQL 2008 R2 instance using the following script, with the names changed for anonymity: USE master go CREATE LOGIN [DOMAIN\...
4
votes
3answers
3k views

Are there ways to only replace SQL Server stored procedures when the definition has changed?

I know the following two patterns to write scripts for creating stored procedures, which can be executed repeatedly without throwing errors. if object_id('my_proc') > 0 drop procedure my_proc go ...
18
votes
2answers
410 views

Is it more secure to go through a 3rd database to connect two databases using the same login?

We have the following setup: Multiple production database containing private data which is used by desktop software A web database for a public website which needs some data from the private ...
0
votes
2answers
2k views

How database users see their objects in sql server

I have a large SQL Server 2008 database that has many tables, stored procedures, and functions. This database also has several users. How can I make it so that each user, after connecting to the ...
2
votes
1answer
770 views

Going from separate databases to shared databases and schemas (Multi Tenant Data Architecture)

I am eagerly trying to design the perfect multitenant data architecture for my database. I am getting inspiration from an article posted by Microsoft, based on scenario 3. I have been given all my ...
0
votes
4answers
89 views

Setting Select Permission on 100+ tables in a database

I got the 'Select permission denied' error for some of my tables in a database. In MS SQL Server 2012, setting Select Permission on 1 table is easy. I just right click on the table > Properties &...
1
vote
1answer
47 views

Read Only Access Except Stored Procedures in Specific Schema

I'm building an application that is attached to a legacy application database. The application has it's own database that is on the same server as the legacy database. My application is ASP.NET and ...