MSSQL 2008 Management studio

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
apollo-13Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Patrick BogersConnect With a Mentor Datacenter platform engineer LindowsCommented:
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
 
Patrick BogersDatacenter platform engineer LindowsCommented:
you would need to revove the permission 'VIEW ANY DATABASE' from the role PUBLIC
0
 
apollo-13Author Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
Patrick BogersDatacenter platform engineer LindowsCommented:
servername->          servername\003

Hm this does only work, for me, on a locally installed database. Not over the network.
0
 
apollo-13Author Commented:
any way Its not working servername\003
0
 
Patrick BogersDatacenter platform engineer LindowsCommented:
I tried. Only works locally yes.

Did you remove the VIEW ANY DATABASES from the PUBLIC role?
0
 
apollo-13Author Commented:
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
 
Patrick BogersDatacenter platform engineer LindowsCommented:
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
 
apollo-13Author Commented:
sorry for many questions:

Under security --- mean under database 3 ?
0
 
Patrick BogersDatacenter platform engineer LindowsCommented:
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
 
apollo-13Author Commented:
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
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Is the user there under 003/security/login?
0
 
apollo-13Author Commented:
yes he is under 003 db, login is no problem ,but only cant see 003 bd and no others too offcourse
0
 
apollo-13Author Commented:
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
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Did we solve the problem?
0
 
apollo-13Author Commented:
no, user still cant see db 003
0
 
apollo-13Author Commented:
unfortunatlly no success---USER LOGGED
ww
0
 
apollo-13Author Commented:
ADMIN LOGGED
w1
0
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Is user dbo for 003?
0
 
apollo-13Author Commented:
yes
0
 
apollo-13Author Commented:
under 003 db -security -login-username-properties- standarschema for user dbo
0
 
Patrick BogersDatacenter platform engineer LindowsCommented:
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
 
apollo-13Author Commented:
unfortunatly now i cant because people use .But today night i can do that.
0
 
apollo-13Author Commented:
today i will do that
0
 
apollo-13Author Commented:
after new start the server no changes ,still user cant see 003 db
0
All Courses

From novice to tech pro — start learning today.