January 15, 2010 at 5:02 pm
I have a SQL 2008 permissions related issue which is currently driving me crazy.
A DB restored, from a SQL 2000 or 2005 server onto my 2008 server, when I port the logins to the SQL box and match them up, the Non DB_OWner users, cannot see any of the objects in my three main schema's.
We have roles for the schema's which provide access to the tables within them, but when a non DB_Owner user logs into Management Studio, and expands the list of tables, only a handful of DBO tables appear.
Granting access to the tables, has allowed us to work with the tables in queries, but we still cannot see them in the listing in Management Studio.
Stored Procedures however, still refuse to execute..
I tried creating a new DB on 2008, from scripts, but I get the same issues.
Does anyone know of a change to the role based security which would block access to other schema's, even if the users role provided access for them to get to it.
Thanks,
Jon
February 4, 2010 at 4:11 am
Have you fixed the ophan users?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 4, 2010 at 6:17 am
Yeah, we fixed the orphans, and reset the db owner to sa.
We still seem to have the issue for non-db owner users, not being able to see the list of tables in SQL Studio, but we found the cause of the problems in our application. We were testing for existence of a stored procedure, before executing it, to handle errors at that stage, but SQL was reporting to the non DB Owner, that they did not exist.
If we removed the existence check however, and just executed them, they worked fine.. Fortunately this code was in a common library so it was a minor change..
Still curious if anyone knows why the schema isn't visible to a Non DBO, (It wouldn't bother me so much, if we didn't have another (at least what we have considered checking, near identical) database on the same box, which doesn't behave the same way.
Thanks
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply