SQL 2005 Security - Users: What are they used for? (and other various questions)

Question

Hello

I am not a DBA and so dont really know anything about SQL 2005 security settings, etc. I am attempting to set up an empty copy of our database by generating the full database from SQL Management Studio generated scripts. Unfortunately I don't know what many of the options do and the MSFT documentation of this processes isn't great.

There is an option to generate script for Schemas, Tables, Views and Users. It is the users I am confused about, because I don't understand how they affect the usage of the database. We have some developers in the team who are in this list and some who are not, yet everyone can do anything on the database, at least when they are hosting it on their own machines.

Do I need to keep these Users in my new generated database and what do they do?

We also have a dbo User who is a db_owner and owns many of our schemas. What is this dbo User? What is the significance of a user Owning Schemas? We use Schemas as "namespaces" to group logically related tables in our database but I take it there is more to them than that?

There is also a username tied to this dbo User, its the windows NT login of one of our developers, but he doesn't have his own User object in the list...is there any significance to this? Is this a bad thing?

Other Users are guest, INFORMATION_SCHEMA and sys, but I think these are all defaults?

Sorry but I am a SQL admin ignoramus and usually left these things to the DBA in my previous job!

Thanks for any help.

Answer 1

Do I need to keep these Users in my new generated database and what do they do

The answer is it depends. If your applications use a mixed mode authentication then you will need the user accounts created in SQL. If you use windows authentication (and it sounds like you are) then you might not need them.

What is the significance of a user Owning Schemas?

In SQL Server 2005 all schemas must be owned by a user. Schemas can be used to group functionality but they can also be used to group security. For example a user account may only have access to a specific schema (or multiple schemas).

Hopefully that helps answer some of your questions