Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MSSQL 2008 Management studio

Posted on 2014-02-20
25
Medium Priority
?
222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 10
25 Comments
 
LVL 23

Expert Comment

by:Patrick Bogers
ID: 39873378
you would need to revove the permission 'VIEW ANY DATABASE' from the role PUBLIC
0
 

Author Comment

by:apollo-13
ID: 39873412
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 23

Expert Comment

by:Patrick Bogers
ID: 39873426
servername->          servername\003

Hm this does only work, for me, on a locally installed database. Not over the network.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:apollo-13
ID: 39873442
any way Its not working servername\003
0
 
LVL 23

Expert Comment

by:Patrick Bogers
ID: 39873451
I tried. Only works locally yes.

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

Author Comment

by:apollo-13
ID: 39873454
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 23

Expert Comment

by:Patrick Bogers
ID: 39873480
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
ID: 39873530
sorry for many questions:

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

Expert Comment

by:Patrick Bogers
ID: 39873580
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
ID: 39876049
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 23

Expert Comment

by:Patrick Bogers
ID: 39876382
Is the user there under 003/security/login?
0
 

Author Comment

by:apollo-13
ID: 39876438
yes he is under 003 db, login is no problem ,but only cant see 003 bd and no others too offcourse
0
 

Author Comment

by:apollo-13
ID: 39876440
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 23

Expert Comment

by:Patrick Bogers
ID: 39876452
Did we solve the problem?
0
 

Author Comment

by:apollo-13
ID: 39876498
no, user still cant see db 003
0
 
LVL 23

Accepted Solution

by:
Patrick Bogers earned 1000 total points
ID: 39876503
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
ID: 39876601
unfortunatlly no success---USER LOGGED
ww
0
 

Author Comment

by:apollo-13
ID: 39876611
ADMIN LOGGED
w1
0
 
LVL 23

Expert Comment

by:Patrick Bogers
ID: 39876647
Is user dbo for 003?
0
 

Author Comment

by:apollo-13
ID: 39876653
yes
0
 

Author Comment

by:apollo-13
ID: 39876660
under 003 db -security -login-username-properties- standarschema for user dbo
0
 
LVL 23

Expert Comment

by:Patrick Bogers
ID: 39876679
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
ID: 39876692
unfortunatly now i cant because people use .But today night i can do that.
0
 

Author Comment

by:apollo-13
ID: 39884870
today i will do that
0
 

Author Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

610 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