Unable to view databases according to user login

• azadrazvis

  Grasshopper

  Points: 13

  More actions

  December 10, 2013 at 3:40 am

  #301217

  Hi

  I am trying to create logins for multiple windows users to login in to sql server 2012 but after creation of login and database user , i am unable to login to sql server with run as different user option. I am facing problems in

  1) windows user login

  2) only showing databases related to user and hiding all other databases

  Kindly Let me know any proposed solutions

  Thanks

  Razvi444

• Lowell

  SSC Guru

  Points: 323507

  More actions

  December 10, 2013 at 6:04 am

  #1672278

  azadrazvis (12/10/2013)

  ---

  Hi

  I am trying to create logins for multiple windows users to login in to sql server 2012 but after creation of login and database user , i am unable to login to sql server with run as different user option. I am facing problems in

  1) windows user login

  2) only showing databases related to user and hiding all other databases

  Kindly Let me know any proposed solutions

  Thanks

  Razvi444

  pretty much, the ability to view the list of databases is an all-or-nothing proposition; remember just because they can see a database, doesn't mean they can access it.

  the default PUBLIC group that everyone belongs to has permissions to see sys.databases, so unless you take away that permission, all users can see all databases.

  if you take away that right, then the users can only see the databases they OWN (that's NOT the same as databases they have access to!)

  since there is only one owner, that leaves All other users without the ability to see the database; if they create a connection to go to the database(like in an application, they are fine , but in SSMS, they cannot see the database.

  in most other threads on the same subject you see here, most recommend not worrying about the ability to see other databases.

  here's a coding example for reference:

  IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'DbViewerTesting')

  BEGIN

  EXEC MASTER.dbo.sp_addlogin @loginame = N'DbViewerTesting', @passwd = 'NotARealPassword', @defdb = N'master', @deflanguage = N'us_english'

  --add this user to permit read and write

  END

  CREATE DATABASE DbViewer

  USE DbViewer

  GO

  CREATE USER DbViewerTesting FOR LOGIN DbViewerTesting

  ALTER AUTHORIZATION ON DATABASE::DbViewer TO DbViewerTesting

  EXECUTE AS LOGIN='DbViewerTesting'

  GO

  USE master

  GO

  select * from master.sys.databases -- see them all!

  REVERT;--turn back into SuperMan

  DENY VIEW ANY DATABASE TO DBVIEWERTESTING

  EXECUTE AS LOGIN='DbViewerTesting'

  USE master

  GO

  select * from master.sys.databases -- see master and temp only!

  REVERT;--turn back into SuperMan

  DROP LOGIN DbViewerTesting

  DROP DATABASE DbViewer

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!