@celko

The code I posted is a drastic simplification and incomplete implementation of the overall problem and doesn't show all of the relationships nor best practices. Thanks for you...

Many, MANY thanks!

Many thanks Syed!

Maybe this conversation is a little too broad for this thread. I'll start a new one for specific topics.

Thanks for the input everyone!

I was also wondering what types of things you always do on your databases. Obviously things like normalization, clustered indexes, indexes, and smallest datatypes needed are...

@Dwayne Dibley

Thanks for the suggestion. That had been suggested at one point, but our network is managed by a contract company and I don't think we can ask them to...

What reason? Everything is all in discussion at this point on my end, so I'm not really tied to either solution and neither is the business. External access is not...

@tom.Thomson

You are correct. The security permissions for users will likely be controlled via table entries that grant access to various features or controls. It seems like the least...

Clarification on the user id...

Each user has a domain account, but the connection to the database will not use their account. It will use a single SQL account to connect...

@lowell

This requirement would mean that all insert, update, and delete sprocs would need to get the user id from the application or data layer. I won't be able...

I should also probably clarify the business a bit. This is not a high transaction environment. There are ~500 users and it's mostly reads (80% probably).

How did you determine that there should be only one user? Is this a Windows application or a Web Application?

Whether you have one or more schemas should be based on...

Thanks for the assistance to the both of you! It's just going to be more work than originally intended.

I'm not setting up a new system and it is incredibly large, so altering design is not an option at this point.

They're not using all the same Windows IDs though, so I would want to know what Windows domain account made a change. Also, we are not on static IPs, so...