Technical Article

Relationship between users and Databases

,

This script shows the users inside the databases, to easy view and documentation.

Este script mostra os usuários que estão dentro da base, para uma facil vizualização e documentação.

 

Para mais... http://leka.com.br

create table #UsrDataMapping(
[database_name] [sysname] NULL,
[name] [sysname] NULL,
[schema] [sysname] NULL
)
EXEC sp_MSforeachdb 'insert into #UsrDataMapping SELECT ''?'' as DBNAME,
--u.name AS [Name],
SUSER_SNAME(sid) AS [Name],
ISNULL(u.default_schema_name,N'''') AS [DefaultSchema]
FROM
[?].sys.database_principals AS u
LEFT OUTER JOIN [?].sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id 
WHERE
(u.type in (''U'', ''S'', ''G'', ''C'', ''K''))
and dp.state = ''G''
'
select * from #UsrDataMapping order by database_name
drop table #UsrDataMapping

Read 479 times
(5 in last 30 days)

Rate

3.86 (7)

You rated this post out of 5. Change rating

Share

Share

Rate

3.86 (7)

You rated this post out of 5. Change rating