T-SQL for Database mapping

Question

Hi,

I am looking for a T-SQL/PowerShell script to know the associated logins for given Database Name, not for mapped users for given login.

I don't worry about permissions , just mapped logins for given database.

Answer 1

What do you mean by "not for mapped users for given login"?

Does this do what you want?

EXEC Adventureworks2008.dbo.sp_helpuser

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 2

;with ServerPermsAndRoles as
(
    select
        spr.name as principal_name,
        spr.type_desc as principal_type,
        spm.permission_name collate SQL_Latin1_General_CP1_CI_AS as security_entity,
        'permission' as security_type,
        spm.state_desc
    from sys.server_principals spr
    inner join sys.server_permissions spm
    on spr.principal_id = spm.grantee_principal_id
    where spr.type in ('s', 'u')

    union all

    select
        sp.name as principal_name,
        sp.type_desc as principal_type,
        spr.name as security_entity,
        'role membership' as security_type,
        null as state_desc
    from sys.server_principals sp
    inner join sys.server_role_members srm
    on sp.principal_id = srm.member_principal_id
    inner join sys.server_principals spr
    on srm.role_principal_id = spr.principal_id
    where sp.type in ('s', 'u')
)
select *
from ServerPermsAndRoles
order by principal_name

---

Vote As Helpful if it helps to solve your issue

Answer 3

may be this

sp_msforeachdb "use [?] select db_name() DbName,b.name [Login],a.name as [DBUser] from sys.sysusers a inner join sys.syslogins b on A.sid=B.Sid"

---

Hope it Helps!!