4

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.

MrLane
  • 247
  • 1
  • 3
  • 9

1 Answers1

1

To set up a clone of your database, generally you would want to copy the users. A User in SQL Server is allows you to assign privileges; for instance, there might be an application that uses your database that has the right to read and modify data, but not create or drop tables.

The developers you mention who have unrestricted access are probably accessing SQL Server from a Windows account in the Administrators group. This by default in SQL Server is mapped to the sysadmin Server Role, which is a highly privileged role.

dbo, guest, sys and INFORMATION_SCHEMA are all default accounts. I wouldn't worry too much about them.

The answers to your questions all boil down to - these features are used to control permissions in the database. You want to give users the least permissions necessary so they can't mess with what they don't need to. In this simplest case, this means developers get full access to development databases, and each system or end-user that needs to connect gets an account with just the permissions they need.

Andrew Strong
  • 187
  • 1
  • 9
  • "dbo, guest, sys and INFORMATION_SCHEMA are all default accounts. I wouldn't worry too much about them." This is a pretty lazy answer in my opinion. I came here hoping to learn the purpose of these default accounts. – Dan Feb 23 '15 at 21:39