user permissions on new tables 2008 r2

[wbodger]

So, we have a system that creates new tables from metadata given form requirements (basically). Now, we recently upgraded from 2000 to 2008 r2 and it seems that the users do not new tables being created do not inherit the permissions of the database that they are created in. Is this something that we have to setup manually in Model for each database?

wb

 

[gmmastros]

The Model database only applies to newly created databases. If you already have a database and you are adding new tables to it, the model database is not involved.

What is the database role for the users that cannot access the newly created tables?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

datareader

 

[SQLBill]

check how the tables are being created. Let us say a new table is MyNewTable and is created by JSmith. When you expand the list of tables in SSMS, does MyNewTable look like this:

Name Schema
MyNewTable DBO

or does it look like this:

MyNewTable JSmith


If the latter, then only JSmith can view the table. If the table is owned by DBO, then anyone can view it.

If you need everyone to be able to view objects, make sure they are creating them in the DBO schema:

CREATE TABLE dbo.MyNewTable

This goes for any object.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[wbodger]

That is part of what is odd, everything is created in the dbo schema, so should be visible.

wb