Re: Need help on how to organize users and objects

Henrik Nordgren (HenrikNordgren@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
1) I definetly dont want every developer to become a sysadmin, possible a
dbcreator. I could assign a custom database role to them though.

sysadmin may be to go over the board. But you make life easier, if they
have CONTROL privileges in databases where they do development. Then again
in our shop, everyone knows the sa password. But all our databsaes are
development databases, and the crucial thing is not the SQL Server
databases - it's the version-control system.

2) I dont think its a good idea to assign the db_datareader to the
viewers, then they can access ANY table. The best thing is for them to
tell me what tables they need and I grant them select permissions to
them. Or if the users are not too technical, I create views for them.

But it will be difficult to keep track of whom has access to what.
Schemas *can* be used to simplify things, but using scheams affects the
application. And it only helps if you can identify good grouping of the
tables.

3) One thing that still confuses me with schemas is... in sql server
2000, if a user had permissions to create stored procedures, he became
the owner by default. In 2005, if a user creates an sp, which schema
does it belong to? For me the most logical thing is if a user has a
default schema.

A user does indeed have a default schema. If you create a users with
CREATE USER you can specify the default schema for the user, else it
will be DBO. If you create the user with sp_adduser, you will get a
schema with the user's name and that will be default schema for that
user.

If user creates an object, and does specify which schema it belongs
to, the object will be created in his default schema. Note that the
owner of the object will be the schema owner - not the creator! This is
very good to keep ownership chaining alive.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Viewing object owner in SQL 2005 - ownership chaining
    ... preferred it if SQL 2005 supported EXECUTE AS for views). ... I'm beginning to understand that OWNER lives somewhere between the ... the owner of the schema the object was created it. ... In many databases, dbo owns everything. ...
    (microsoft.public.sqlserver.security)
  • Re: How to access SQL Server 2005 with Query Analyzer (SQL 2000)
    ... Query Analyzer does not fully support new SQL Server 2005 features. ... it does not recognize schema names in the Object Browser. ... > SQL Server databases use the concept of schemas. ...
    (microsoft.public.sqlserver.clients)
  • Re: CD inventory database
    ... can post the schema if anyone's interested (it's going to end up ... The online CD databases are wholly ... CDs. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Access & OleDb - generating schema changes, problem with identity/counter fields.
    ... Being unable to work agaisnt other databases. ... The new database exists as schema, ... CompareLib and Klik! ... >> (SQL State 3259). ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Permissions
    ... Do the objects have the same owner ... I have a table, myTable, in a schema named Schema1. ... The permissions on Schema1.myTable are left unchanged (no ... This is Sql Server 2005 Standard, ...
    (microsoft.public.sqlserver.server)