permissions for developers not working after 2005 upgrade

Prior to our move to 2005...permissions were granted to developers by adding them to the following fixed database roles...db_ddladmin, db_datareader, db_datawriter, and db_securityadmin. They created their objects using 'dbo' as the owner.

After upgrading to 2005, suddently they are having difficulty accessing their objects with this same security. Do they need permissions on the dbo schema?

Can you please elaborate on the access difficulty? Are you encountering errors and, if yes, what are those errors? It would help if you could give us an example of some action that used to work and now doesn't, and of what is the system response in this case.

Thanks
Laurentiu

|||

They are making a connection through Visual Studio using the MS OLE DB Provider for SQL Server with their domain account and receiving the following error...

SELECT permission denied on object 'Contact_Info', database 'GetLean', schema 'dbo'.

|||

Could you check what is the current execution context at the time when this error is obtained? You can use Profiler to figure out what is the current execution context.

There are two possibilities:

(1) current execution context is not a member of db_datareader, so you do not have SELECT permission.

(2) current execution context is explicitly denied SELECT permission on the Contact_Info table.

If you determine the current execution context, then you can check whether it's a member of db_datareader by looking at the sys.database_role_members catalog. You can check for the SELECT permission being denied by looking at the sys.database_permissions catalog.

Thanks
Laurentiu

|||

They are a member of the db_datareader, db_datawriter, db_ddladmin, and db_securityadmin roles. There are no permissions explicitly denied in this database.

Even though they have db_datareader, db_datawriter, db_ddladmin, and db_securityadmin rights....must I still assign them to the dbo schema?

|||

db_datareader grants select on the entire database, hence on the dbo schema as well (http://msdn2.microsoft.com/en-us/library/ms189612.aspx). You don't need to do a special permission grant for the dbo schema.

Can you try a little experiment? Create a test table in the dbo schema and then verify if those members of db_datareader can access it. Also, create a separate schema and a table in it and see if there is the same behavior for it as for the dbo schema.

Also, does this happen for all those developers or only for some? If some of them were added to db_denydatareader role, then that would prevent them from selecting from anything. Are they members of other roles than those four that you mentioned?

Thanks
Laurentiu

|||

It turned out not to be a DBA problem. Sorry, should have posted the resolution earlier.

It turned out to be a developer issue...a combination of failing to practice current standards and inexperience with Visual Studio. Aargh!