Sql-server – What’s the point in mapping users in SQL Server Data Collection

data collectionmonitoringsql server

In SQL Server 2012 R2 Enterprise, whenever I check Management\Data Collection, right-click –> Configure Management Data Warehouse , I see whatever users-accounts-logins except the one I am logged in

Map Logins and Users

I cannot understand what's the point in "Map Logins and Users" in Management\DataCollectiion of SQL Server?

Best Answer

When you create the management data warehouse, the current login will be added to all three roles, making it an administrator of the database.

The dialog above lets you add other logins to the roles, to make those people readers, writers or administrators of the database.

Related Solutions

Sql-server – Data Collection reports fail because login is from an untrusted domain

I have confirmed this is an issue with how the reports get executed on the server -- I reproduced the issue exactly by using two virtual machines, neither of which was in a domain.

Moreover, I created a test user (member of the Users built-in group and nothing more) on the SQL box, ran Management Studio locally as that user, logged in with sa, and attempted to run the report. As I expected, it failed, and returned the error Failed to connect to server MDWTestSQL. --> Login failed for user 'MDWTESTSQL\TestUser'.

This is clear indication that the reports are implemented to only use Windows authentication for some portion of it. Therefore, sufficient SQL-level permissions must be granted to the Windows principal running Management Studio, even if you authenticate to the server using a SQL login.

There are two workarounds:

Use pass-through authentication: create a matching (user name and password) local login on the workstation, and impersonate the local login to run Management Studio remotely. (I tested that this works.)
Use pass-through authentication by creating new matching local accounts on both boxes, and set up SQL permissions for the new account.

As I mentioned in chat, this problem is "fixed" in the 2012 Management Studio -- the reports in the menu are hidden if the Windows account doesn't have permissions to run the reports.

SQL Server – Re-Add User After Moving Database

This is the difference between logins and users and how they relate to each other:

Logins - Instance level principals that allow an entity to connect to the SQL Server instance. They do not, by their nature, grant any access to databases on the instance. The exception to this is a login with sysadmin rights can use a database because they are sysadmin, but because of sysadmin level permissions.
Users - Database level principals that allow an entity to connect to a SQL Server database. Users are associated with logins via SIDs, creating a relationship between the two and allowing a login to connect to the instance and then use the associated user to connect to the database.

What commonly happens with SQL authenticated logins and database users on a restore is that the SIDS will be out of sync, thus breaking the relationship. This relationship must be repaired before you can connect to the database using that login, because in the eyes of SQL Server those principals are no longer connected. You can fix this with the following SQL:

ALTER USER [foo] WITH LOGIN=[foo]

You can use the following query in the context of your database to check for orphans:

select
    dp.name [user_name]
    ,dp.type_desc [user_type]
    ,isnull(sp.name,'Orhphaned!') [login_name]
    ,sp.type_desc [login_type]
from   
    sys.database_principals dp
    left join sys.server_principals sp on (dp.sid = sp.sid)
where
    dp.type in ('S','U','G')
    and dp.principal_id >4
order by sp.name

Best Answer

Related Solutions

Sql-server – Data Collection reports fail because login is from an untrusted domain

SQL Server – Re-Add User After Moving Database

Related Question