T-SQL pour lister tous les mappages utilisateur avec des rôles de base de données/autorisations pour une Connexion

Je suis à la recherche d'un script t-sql qui peut lister les bases de données et les et les rôles respectifs/privilèges mappé pour un utilisateur particulier. À l'aide de SQL Server 2008 R2.

OriginalL'auteur muddu83 | 2011-12-12

  1. 34
    CREATE TABLE #tempww (
    LoginName nvarchar(max),
    DBname nvarchar(max),
    Username nvarchar(max), 
    AliasName nvarchar(max)
)

INSERT INTO #tempww 
EXEC master..sp_msloginmappings 

-- display results
SELECT * 
FROM   #tempww 
ORDER BY dbname, username

-- cleanup
DROP TABLE #tempww

    OriginalL'auteur Walter Wildoer

  2. 2

    J'ai écrit un petit interroger pour obtenir l'autorisation d'un utilisateur sur une base de données spécifique.

        SELECT * FROM   
(
SELECT 
perm.permission_name AS 'PERMISSION'
,perm.state_desc AS 'RIGHT'
,perm.class_desc AS 'RIGHT_ON'
,p.NAME AS 'GRANTEE'
,m.NAME AS 'USERNAME'
,s.name AS 'SCHEMA'
,o.name AS 'OBJECT'
,IIF(perm.class = 0, db_name(), NULL) AS 'DATABASE'
FROM
sys.database_permissions perm
INNER JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id
LEFT JOIN sys.database_role_members rm ON rm.role_principal_id = p.principal_id
LEFT JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
LEFT JOIN sys.schemas s ON perm.class = 3 AND perm.major_id = s.schema_id
LEFT JOIN sys.objects AS o ON perm.class = 1 AND perm.major_id = o.object_id
UNION ALL
SELECT 
perm.permission_name AS 'PERMISSION'
,perm.state_desc AS 'RIGHT'
,perm.class_desc AS 'RIGHT_ON'
,'SELF-GRANTED' AS 'GRANTEE'
,p.NAME AS 'USERNAME'
,s.name AS 'SCHEMA'
,o.name AS 'OBJECT'
,IIF(perm.class = 0, db_name(), NULL) AS 'DATABASE'
FROM
sys.database_permissions perm
INNER JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id
LEFT JOIN sys.schemas s ON perm.class = 3 AND perm.major_id = s.schema_id
LEFT JOIN sys.objects AS o ON perm.class = 1 AND perm.major_id = o.object_id
) AS [union]
WHERE [union].USERNAME = 'Username' -- Username you will search for
ORDER BY [union].RIGHT_ON, [union].PERMISSION, [union].GRANTEE

    Les autorisations de base de données fixe les rôles n'apparaissent pas dans sys.database_permissions. Par conséquent, la base de données de la direction peuvent avoir des autorisations supplémentaires ne sont pas listés ici.

    Je ne préfère pas 

        EXECUTE AS USER = 'userName';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE')

    Parce que c'est juste de la récupération de laquelle les autorisations de l'utilisateur ne possède pas d'où ils viennent!

    Peut-être que j'ai trouver comment rejoindre la base de données fixe les rôles de l'autorisation accordée à l'utilisateur un jour...

    Pls profiter de la Vie et de la haine les Utilisateurs 😀

    OriginalL'auteur Code.IT

  3. 2
    CREATE TABLE #tempww (
LoginName nvarchar(max),
DBname nvarchar(max),
Username nvarchar(max), 
AliasName nvarchar(max)
)
INSERT INTO #tempww 
EXEC master..sp_msloginmappings 
-- display results
declare @col varchar(1000)
declare @sql varchar(2000)
select @col = COALESCE(@col + ', ','') + QUOTENAME(DBname)
from #tempww Group by DBname
Set @sql='select * from (select LoginName,Username,AliasName,DBname,row_number() over(order by (select 0)) rn from #tempww) src
PIVOT (Max(rn) FOR DBname
IN ('+@col+')) pvt'
EXEC(@sql)
-- cleanup
DROP TABLE #tempww
    Quel est le nombre, en vertu de certaines des colonnes de base de données? Assez sûr que c'est la base de données que le Nom d'utilisateur dispose des autorisations mais est-ce le nombre de fois que cette connexion est connecté?

    OriginalL'auteur Piotr Czekaj

  4. 1

    A volé cette de ici. Je l'ai trouvé très utile!

    DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)
INSERT @DB_USers
EXEC sp_MSforeachdb
'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''
SELECT
dbname,username ,logintype ,create_date ,modify_date ,
STUFF(
(
SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
FROM @DB_USers user2
WHERE
user1.DBName=user2.DBName AND user1.UserName=user2.UserName
FOR XML PATH('')
)
,1,1,'') AS Permissions_user
FROM @DB_USers user1
GROUP BY
dbname,username ,logintype ,create_date ,modify_date
ORDER BY DBName,username

    OriginalL'auteur sam yi

  5. 0

    utilisant fn_my_permissions 

    EXECUTE AS USER = 'userName';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE')
    Merci @Amon, mais je pense que je n'ai pas fait moi-même clair. La sortie du script devrait me donner la liste de la dbs, utilisateurs mappés à la dbs et le rôle de base de données les appartenances de chacun d'eux pour une connexion.

    OriginalL'auteur Arnon Rotem-Gal-Oz

  6. 0

    Est-ce le genre de chose que vous voulez?
    Vous pourriez vouloir l'étendre pour obtenir plus d'infos de le sys tables.

    utilisation de maître 
DECLARE @nom VARCHAR(50) -- nom de base de données 
DÉCLARER db_cursor CURSEUR POUR 
select nom from sys.les bases de données 
OUVRIR db_cursor 
FETCH NEXT à PARTIR de db_cursor @nom 
ALORS QUE @@FETCH_STATUS = 0 
COMMENCER 
print @nom 
exec('UTILISER' + @nom +", sélectionnez-rp.nom, mp.nom de sys.database_role_members drm 
rejoignez sys.database_principals rp (drm.role_principal_id = rp.principal_id) 
rejoignez sys.database_principals mp (drm.member_principal_id = mp.principal_id)') 
FETCH NEXT à PARTIR de db_cursor @nom 
FIN 
FERMER db_cursor 
DÉSALLOUER db_cursor
    Merci @JDC. Je crains que cela ne m'aide pas. Pour être précis, ce que je cherche, c'est la même chose appelé comme 'les rôles de Serveur" et "Mappage de l'Utilisateur" sous Propriétés de la Connexion pour une connexion particulière. J'ai besoin d'avoir concernés et les rôles de serveur de mappage de l'utilisateur des informations de connexion répertoriées.

    OriginalL'auteur JDC

  7. 0

    Avez-vous de tri? Je viens de trouver ce code ici:

    http://www.pythian.com/news/29665/httpconsultingblogs-emc-comjamiethomsonarchive20070209sql-server-2005_3a00_-view-all-permissions-_2800_2_2900_-aspx/

    Je pense que je vais avoir besoin de faire un peu de peaufinage, mais essentiellement ce a trié pour moi!

    Je souhaite, c'est pour vous aussi!

    J

    OriginalL'auteur JYatesDBA