locked
Multihost SQL server security RRS feed

  • Question

  • Hello

    We have a SQL Server 2008, where there is allots of databases. One database to each customer.

    Each customer do have SQL Management Studio installed. When a customer connects can he see all the databases on the big SQL 2008 multihost servers, but only make changes to the database that he has been mapped to and have the right over ("db_owner").

    Is there a way to make all the databases invisible except for the database the customer have the permission over?
    Thursday, March 11, 2010 2:26 PM

Answers

  • You can play with the VIEW ANY DATABASE priviliges for the login, but I can't say off the top of my head whether you should REVOKE or DENY it and whether you then have to GRANT something else to actually see the database you should see....


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, March 11, 2010 3:57 PM
  • In this case revoke view any database will only help you partly.

    Running the command

    Revoke view any database from [login]

    Will only let the user see master and tempdb and not the other dbs where he even has a db_owner role. Now to see the databases that he has access in , he must be the database owner.

    Use dbname

    go

    sp_changedbowner 'sa','username'


    -- if the login is already mapped as a user the above will throw an error The proposed new database owner " is mapped as user "" in this database.

    By this change he can see master, tempdb and userdb which are owned by him.
    Owning the database automatically gives him db_owner role at the db level.

     

    ·                                 There is a bug filed in connect site https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=182665%20&wa=wsignin1.0  for the same issue , where the user says - after I run "REVOKE VIEW ANY DATABASE TO Public" doesn't show database that I have access. After this I see master and tempdb only!

    Comment from SQL server product specialist from Microsoft

    Please note that it is by design behavior that if the login connecting does not have any permissions on the database (including VIEW permission), then they will not see the database in the object explorer. This is as per catalog security rules enforced since SQL Server 2005.

    Still if you are keen at viewing the database that you have access , the way would be to have the owner changed. Also remember that by setting the user as database owner , the user gets added under the fixed database role db_owner which might be a higher privilege for your application .

     


    Thanks, Leks
    Thursday, March 11, 2010 8:09 PM
  •   Just a quick note. Sp_changedbowner is a deprecated feature and it will be removed in future versions of SQL Server. I strongly recommend using
     ALTER AUTHORIZATION ON DATABASE::<<db_name>> TO <<new owner login name>>.

      For more details please visit BOL:
    * sp_changedbowner http://msdn.microsoft.com/en-us/library/ms178630.aspx

    * ALTER AUTHORIZATION http://msdn.microsoft.com/en-us/library/ms187359.aspx

     -Raul Garcia
      SDE/T
      SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, March 12, 2010 1:50 AM

All replies

  • You can play with the VIEW ANY DATABASE priviliges for the login, but I can't say off the top of my head whether you should REVOKE or DENY it and whether you then have to GRANT something else to actually see the database you should see....


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, March 11, 2010 3:57 PM
  • In this case revoke view any database will only help you partly.

    Running the command

    Revoke view any database from [login]

    Will only let the user see master and tempdb and not the other dbs where he even has a db_owner role. Now to see the databases that he has access in , he must be the database owner.

    Use dbname

    go

    sp_changedbowner 'sa','username'


    -- if the login is already mapped as a user the above will throw an error The proposed new database owner " is mapped as user "" in this database.

    By this change he can see master, tempdb and userdb which are owned by him.
    Owning the database automatically gives him db_owner role at the db level.

     

    ·                                 There is a bug filed in connect site https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=182665%20&wa=wsignin1.0  for the same issue , where the user says - after I run "REVOKE VIEW ANY DATABASE TO Public" doesn't show database that I have access. After this I see master and tempdb only!

    Comment from SQL server product specialist from Microsoft

    Please note that it is by design behavior that if the login connecting does not have any permissions on the database (including VIEW permission), then they will not see the database in the object explorer. This is as per catalog security rules enforced since SQL Server 2005.

    Still if you are keen at viewing the database that you have access , the way would be to have the owner changed. Also remember that by setting the user as database owner , the user gets added under the fixed database role db_owner which might be a higher privilege for your application .

     


    Thanks, Leks
    Thursday, March 11, 2010 8:09 PM
  •   Just a quick note. Sp_changedbowner is a deprecated feature and it will be removed in future versions of SQL Server. I strongly recommend using
     ALTER AUTHORIZATION ON DATABASE::<<db_name>> TO <<new owner login name>>.

      For more details please visit BOL:
    * sp_changedbowner http://msdn.microsoft.com/en-us/library/ms178630.aspx

    * ALTER AUTHORIZATION http://msdn.microsoft.com/en-us/library/ms187359.aspx

     -Raul Garcia
      SDE/T
      SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, March 12, 2010 1:50 AM