Need to know userlogin and Roles in DB

  • HI,

    I have a Database and having around 100 users and couple of User Roles,i just want to know each users and roles what kind of permissions are given

    Reagrds,

    Shine

  • exec sp_helplogins

  • IF this is a SQL 2005 instance then the following may help

    select suser_name(role_principal_id), suser_name(member_principal_id) from sys.server_role_members where member_principal_id = suser_id('LOGIN')

    select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('LOGIN')

    Note: LOGIN refers to the login name of the user that you wish to audit

    CodeMinkey

  • The attached proc will script all your DB roles and their permissions, and your DB users.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply