I have noticed that some folks create a user to own a schema, with both having the same name (ex: userowner = pfm schema = pfm) and others make dbo the owner for all their schemas. From what I can tell, it doesn't really matter; what does matter is if your database users are granted access to the schema. Since most users only have public rights and dbo has database owner rights, is there any particular reasoning to use one scenario over the other?
Besides better granularity in your DB application management, one very good reason is to have better isolation between different applications. For example, if all the schemas (and therefore all tables and modules by default) are owned by the same principal (i.e. dbo) it would be very difficult to control access to the data on principals who can create/execute modules (i.e. SPs) as ownership chaining will be available for all tables in the database.
-Raul Garcia
SDE/T
SQL Server Engine
|||Thank you Raul, I agree this does make sense. I just tried to create a user (ampfm) and it failed due to their not being a login named ampfm. However, when I looked at logins, there does not seem to be one for sys or INFORMATION_SCHEMA. How were those users created without also requiring login accounts?
|||Those are builtin-in principals, and they are not intended for any interactive usage. Because they are built-in and not intended for interactive usage the SIDs are set to NULL. I strongly recommend against reusing these built-in principals in any way other than the supported scenarios. Please consult BOL for more information on them.
If you want to create a user without a login you can use the WITHOUT LOGIN syntax:
CREATE USER [ampfm] WITHOUT LOGIN
go
I hope this information helps.
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks again Raul. That is exactly what I needed and it worked great!
