Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

10775: information users database

Posted on 2014-10-17
1
Medium Priority
?
80 Views
Last Modified: 2014-10-17
Please a script that shows the information users database.
for example, the permissions enabled in the BD 2012 (store procedure, database, table, views)
0
Comment
Question by:enrique_aeo
1 Comment
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 40387422
--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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question