Script to gett all permission in database

  • Does anyone has a SQL script that can start at the beginning of the logins (in the Security section for that server) and list, for each login, their respective server roles and database permissions? What about checking or scanning through a whole group of servers?

    I have about 1500 SQL Servers to check through.

  • Dear thank you so much for prompt reply but still i am confuse

    simply i want to make a list of all DB/Windows users having with all permission.

    for example ,

    test user have read_only permission

    monitor user have DB_owner permission

    kkj\kirate user have right_only permission

    i need to check all user with there respective rights.

  • you can try exec sp_helplogins. but you will have to run this for all the databases. may be you can write a small script and use this sp to get you the desired result.

    hope this helps.

  • thanks for this

    i am using sp_helplogins , but i need to execute on every server and database .

    i made this query but not able to get permission.

    select a.dbid,a.name as Username,b.dbid,b.name As Dataabase from

    dbo.sysxlogins a, sysdatabases b

    where a.dbid=b.dbid

    order by 1 desc

    i am still thinking that how to add permission in above query to get the actual result.

    may be you have better idea than me......

  • Hi

    Please try this for scripting out the object permissions in a database.

    SELECT

    CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) +

    perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(schema_NAME(obj.schema_id)) + '.'

    + QUOTENAME(obj.name) collate Latin1_General_CI_AS_KS_WS

    + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name)

    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

    FROMsys.database_permissions AS perm

    INNER JOIN

    sys.objects AS obj

    ON perm.major_id = obj.[object_id]

    INNER JOIN

    sys.database_principals AS usr

    ON perm.grantee_principal_id = usr.principal_id

    LEFT JOIN

    sys.columns AS cl

    ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

    ORDER BY usr.name

    Thanks

    Prem

Viewing 6 posts - 1 through 5 (of 5 total)

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