Solved

MSSQL 2008 Management studio

Posted on 2014-02-20
25
207 Views
Last Modified: 2014-04-23
I have MSsql 2008 and Management studio.

I creted one user and i want the user connect to only mssql database (3) not the 1 and 2 .
but every time when connect can see all of them .

is  it possible user see only 3 db?

Thanks
11.png
0
Comment
Question by:apollo-13
  • 15
  • 10
25 Comments
 
LVL 19

Expert Comment

by:Patricksr1972
Comment Utility
you would need to revove the permission 'VIEW ANY DATABASE' from the role PUBLIC
0
 

Author Comment

by:apollo-13
Comment Utility
can be my connection is not right to the server ?
i write under Management stuido connection:

databankmodul
servername     ------ may be here only database 3 should write?
windowsauthentication
domain\user
password

how can i connect direct to database 3?
0
 
LVL 19

Expert Comment

by:Patricksr1972
Comment Utility
servername->          servername\003

Hm this does only work, for me, on a locally installed database. Not over the network.
0
 

Author Comment

by:apollo-13
Comment Utility
any way Its not working servername\003
0
 
LVL 19

Expert Comment

by:Patricksr1972
Comment Utility
I tried. Only works locally yes.

Did you remove the VIEW ANY DATABASES from the PUBLIC role?
0
 

Author Comment

by:apollo-13
Comment Utility
can you please tell me how can get this? 'VIEW ANY DATABASE' from the role PUBLIC

because i can find.
My problems is I have many databases on my msqlserver ,i do not want new user to see all
0
 
LVL 19

Expert Comment

by:Patricksr1972
Comment Utility
Under security, logins. Double click the user and go to securables.

Under the tab EFFECTIVE you probably now see, SQL Connect and VIEW ANY DATABASE.

VIEW ANY DATABASE
Configure like above and press apply.
Check tab Effective again and VIEW ANY should be gone.
0
 

Author Comment

by:apollo-13
Comment Utility
sorry for many questions:

Under security --- mean under database 3 ?
0
 
LVL 19

Expert Comment

by:Patricksr1972
Comment Utility
Nope under general security.

security
Next you can check if this user if listed under database 001 and 002 (database-security) and if it is present delete the user there.
0
 

Author Comment

by:apollo-13
Comment Utility
Under the tab EFFECTIVE you probably now see, SQL Connect and VIEW ANY DATABASE.--i did

super now all databases not shows -But how can i db 3 connecr because its not showing too.
0
 
LVL 19

Expert Comment

by:Patricksr1972
Comment Utility
Is the user there under 003/security/login?
0
 

Author Comment

by:apollo-13
Comment Utility
yes he is under 003 db, login is no problem ,but only cant see 003 bd and no others too offcourse
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:apollo-13
Comment Utility
what i did:
1.new user login created under db 003(security/login)
2.new user login created under databases(security/login)
        Under the tab EFFECTIVE you probably now see, SQL Connect and VIEW ANY DATABASE
--- i  did deactive.
then SUPER! user can login but no can see all databases--THIS IS WHAT I WANTED.

only think he has to see only db 003 but not

Thank you so much
0
 
LVL 19

Expert Comment

by:Patricksr1972
Comment Utility
Did we solve the problem?
0
 

Author Comment

by:apollo-13
Comment Utility
no, user still cant see db 003
0
 
LVL 19

Accepted Solution

by:
Patricksr1972 earned 500 total points
Comment Utility
I figured we werent ready yet.

Can you run this query (where customerlogin need to be changed)

USE 003
ALTER AUTHORIZATION ON DATABASE::003 to <customerlogin>

Then login as <customerlogin> and check?
0
 

Author Comment

by:apollo-13
Comment Utility
unfortunatlly no success---USER LOGGED
ww
0
 

Author Comment

by:apollo-13
Comment Utility
ADMIN LOGGED
w1
0
 
LVL 19

Expert Comment

by:Patricksr1972
Comment Utility
Is user dbo for 003?
0
 

Author Comment

by:apollo-13
Comment Utility
yes
0
 

Author Comment

by:apollo-13
Comment Utility
under 003 db -security -login-username-properties- standarschema for user dbo
0
 
LVL 19

Expert Comment

by:Patricksr1972
Comment Utility
Strange.. if he is owner he should see his database.

Can you restart SQL server and check or maybe post a screen for the roles of this user?
0
 

Author Comment

by:apollo-13
Comment Utility
unfortunatly now i cant because people use .But today night i can do that.
0
 

Author Comment

by:apollo-13
Comment Utility
today i will do that
0
 

Author Comment

by:apollo-13
Comment Utility
after new start the server no changes ,still user cant see 003 db
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
View Sql in stored procedure 13 20
SQL 2012 Syntax Error 5 22
SQL Help - 12 34
Sql query 34 14
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now