Database Roles and Schemas in SQL 2005

[eggy168]

I am totally confused after reading the Book Online when I distinguished the Database Roles and Schemas in 2005.

In version 2000, I used to be the Owner of the database, then I had the 2 Database Roles with bunch of users in either role:
Marketing can only Insert/Update/Select
Senior Mgr can only Insert/Update/Select/Delete

Now, I am in 2005. First, I place myself into the sysadmin role in all the databases, so I assume I don't need to pay attention am I the "Owner" of a particular database or not. Or is there any suggestion to ignore the "owner" problem?

Second, I am totally confused should I create a schemas or just create a database role as verion 2000? I tried to create both schemas and database role, but it seems like a reduntant, am I correct? Can anyone clearly explains what is the different between Schemas and Database Role? It is very confusing.

Thanks for the helps.

 

[mrdenny]

Schemas and roles are completely different. Roles are just like there were before. They are groups of users, and then the role is assigned rights to objects.

Schemas function as the owner of the object. dbo for example is a schema. You can create your objects in the dbo schema, or in another schema, and then use the roles to grant users rights to the schema. Do keep in mind that if the users default schema is something other that the schema that the objects are in, then the users will need to use the two part name to reference the object (which is always the preferred method to reference objects).

For example if you have a user who needs to access the table Employee in the schema HR the user should use full name when selecting data from the table.

SELECT *
FROM HR.Employee

Now if the users default schema is HR, then they do not need to specify the schema name which would make this code work.

SELECT *
FROM Employee

However if the default schema for the user is ever changed they will need to edit their queries and add the qualifying schema name into the query.

Does that clear it up any, or have I simply muddied up the water even more?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[eggy168]

Thanks for your reply. It looks like the Role is the same as version 2000.
Schemas is like if I have a table (Table Q) assigned to a schema (Schema X), and one of the employees (Employee A)belongs to other schemas (Schema Y) or other roles (Roles B), then Employee A needs to access Table Q, this is the script to write,
Select * from X.Q
What happens if the Employee A can't access it, is that mean I have to first grant the pemission to Employee A to "select" the Table Q?
Thanks again.

 

[mrdenny]

Yes you have to grant rights to objects before the user will be able to see the objects.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[eggy168]

I created two schemas, but I can't grant permission in individual table but the entire database, did i do something wrong?

Also, I use Access 2000 as a front end, and I tried to use ODBC to link tables, I see all the system tables/views/schemas in the list table, I am wondering how can I hide those in the SQL Server 2005 for individual schemas/database role?

Thanks

 

[mrdenny]

but I can't grant permission in individual table but the entire database, did i do something wrong?

What?

I see all the system tables/views/schemas in the list table, I am wondering how can I hide those in the SQL Server 2005 for individual schemas/database role?

The user will be able to see what ever objects they have rights to use. If the user doesn't have rights to a table it will not show up. As you are either dbo or sa you will be able to see all the objects.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[eggy168]

i solved the schema problems. however, I am still confusing why people in a database role can still see the system views. I never grant the permission to any user(s). Any suggestion, mrdenny?
Thanks.

 

[mrdenny]

Generic uses can see all the system views. You will notice that when they query them they will only see the objects which they have access to. In the event they query sys.sysprocesses (or other performance related views) they will either get no data, or data related to that specific session.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com