March 31, 2011 at 12:14 pm
We are testing ways to migrate SQL 2000 databases to SQL 2008.
We tested detaching the sql2000 from the 2000 server and copying them to the sql 2008 server. After running fix logins, we could login fine and see ONLY the tables allowed by the database roles. Note: at this point the database is running in 2000 (80) mode.
We ran checkdb, updateusage and updatestatistice. all went fine.
Then when we changed the compatibility to 10 (sql2008) the user who had permission to read only 3 tables could read all tables!
It looks like it read the old security model in 80 level but when change to 10 it ignores it.
Where can I find out how to map the security roles?
thanks, Dana
March 31, 2011 at 12:32 pm
How are you assigning permissions?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 31, 2011 at 12:41 pm
User ID's and Roles were assigned in the SQL 2000 server database.
Users were assigned to a role in sql2000 (role1 - 3 tables read only)
We copied the db's over to the new server, fixed logins, and security worked fine at 80 level.
The Role security worked until we set the compatility level to 100 from 80.
March 31, 2011 at 1:07 pm
The only thing I would think might be the culprit could be the dbo schema. When a user is created in SQL 2005 and later it will be assigned the dbo schema by default. Which I believe the dbo schema would be able to see all tables in the database.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 31, 2011 at 1:53 pm
Examine the permissions granted to the data base role "Public". From Books On Line (BOL)
Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.
To determine what rights are granted to the Public role execute the following in the database in the database you upgraded:
sp_helprotect @username='public'
Be very, very careful in changing these permissions, it could have unforseen serious consequences for every user.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply