10775: information users database

Please a script that shows the information users database.
for example, the permissions enabled in the BD 2012 (store procedure, database, table, views)
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
--The script below will show are permissions/rights in the database  where is executed:


WITH    perms_cte as
      (
                  select db_name() as db_name,USER_NAME(p.grantee_principal_id) AS principal_name,
                              dp.principal_id,
                              dp.type_desc AS principal_type_desc,
                              p.class_desc,
                              OBJECT_NAME(p.major_id) AS object_name,
                              p.permission_name,
                              p.state_desc AS permission_state_desc
                  from    sys.database_permissions p
                  inner   JOIN sys.database_principals dp
                  on     p.grantee_principal_id = dp.principal_id
      )
      SELECT p.db_name,p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
      FROM    perms_cte p
      WHERE   principal_type_desc <> 'DATABASE_ROLE'
      UNION
      SELECT p.db_name,rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
      FROM    perms_cte p
      right outer JOIN (
            select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name
            from    sys.database_role_members rm
            INNER   JOIN sys.database_principals dp
            ON     rm.member_principal_id = dp.principal_id
      ) rm
      ON     rm.role_principal_id = p.principal_id
      order by 1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.