March 4, 2010 at 7:17 pm
I have created a new sql user - lets call him user01. I used SQL 2008 management studio, logged in as sa. (No scripts used)
When looking at the user in <Security> <Logins>
His server role = public
Is user mapping for specific databases is ticked and his role for the specific databases is public and data_reader
He has a default schema of scheme (Thrust me the scheme user is correct and not the problem so don’t suggest any changes to that)
When logged in as this user (user01) it can select data from views & tables. It cant update or delete. This is all as it should be and as suspected
When using a ODBC connection from excel user01 can connect and all table/views can be seen to select one as required.
All background to here
The problem is when I am logged in as user01 and in Management studio I go to the <Databases><database name><Tables or View> “path”
No tables or views are displayed. (Only System Tables/Views)
When I give user01 db_owner rights I can see the views/tables but I cant grant him such rights.
What am I missing here. Please help me
SP1 for SQL2008 64 bit I installed
March 4, 2010 at 11:38 pm
Thrust me the scheme user is correct and not the problem so don’t suggest any changes to that
I'll "trust" you rather and not suggest any changes.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 4, 2010 at 11:52 pm
Has anybody locked down the public role?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 5, 2010 at 12:08 am
Hi Jason
I'm not a SQL power user :unsure: so what do you mean with "lock down" the public user and how would i do it if it is not done.
Regards
Burger
March 5, 2010 at 12:12 am
What you describe is similar to what would happen if the public role had been locked down.
Here is an article that discusses security and locking down sql server.
http://duartes.org/gustavo/articles/Lock-Down-SQL-Server-2005.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 5, 2010 at 4:26 am
Hi Jason
I went through the link you provided. Did the opposite of lock down. Gave public full view access. Still did'nt help.
Im starting to think it might be a bug in 64 bit
Regards
Burger
March 5, 2010 at 6:52 am
b_jvr (3/4/2010)
When logged in as this user (user01) it can select data from views & tables. It cant update or delete. This is all as it should be and as suspectedWhen using a ODBC connection from excel user01 can connect and all table/views can be seen to select one as required.
All background to here
The problem is when I am logged in as user01 and in Management studio I go to the <Databases><database name><Tables or View> “path”
No tables or views are displayed. (Only System Tables/Views)
When I give user01 db_owner rights I can see the views/tables but I cant grant him such rights.
SP1 for SQL2008 64 bit I installed
I don't think that the problem is with SQL 2008. If this is a SQL Problem I think that you would probably experience the same problems regardless of the version (2000,2005, 2008, Azure)
However, I'm confused on one point. In one sentence you said that when logged in as this user you can select data, but then later you say logged in as this same user in Management Studio that no tables or views are displayed?
I tried the same scenario as described. I created a new user (user01), granted the user Public to the Server (Login) and Public and Db_Datareader (User Mapping) to a database setting the default schema to "dbo" as that is only schema in the database.
I logged into SQL Server Management Studio as user01 and using the object explorer I can see all of the views in the database without issue. I also ran a select on the views and tables to be sure.
I tried this on both SQL2005(9.0.4230) and SQL2008(10.1.2714). No problems. If it were a bug, I would be able to duplicate it no problem.
Check the credentials on the application and use Profiler to see what is being passed.
Regards, Irish
March 7, 2010 at 7:55 pm
Hi
Jeffrey
I have solved this. To clarify a few things for other people that might use this post.
You said
However, I'm confused on one point. In one sentence you said that when logged in as this user you can select data, but then later you say logged in as this same user in Management Studio that no tables or views are displayed?
When in studio and you look at the table tab or view tab there is no tables or views displayed for one to see.
If in the script box you can still type > select * from table_name/view_name < and a result set is returned even though nothing is shown in management studio.
As mentioned I have found what was wrong.
SOLUTION.
The user had to be given “View definition” privileges on a DATABASE level.
In Management studio:
Right click databse – properties – permissions – view definition.
When loggon in now with the mentioned use all tables and views in the table/view tab are displayed
Hope this saves someone the 3 days I spend on this
Regards
Burger
March 7, 2010 at 8:33 pm
Thanks for posting back with your resolution.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 28, 2010 at 1:06 pm
This did save me time. Thanks for posting the fix!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply