User & Their Databases access with Access levels

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » User & Their Databases access with Access...

User & Their Databases access with Access levels

Rate Topic

Display Mode

Topic Options

Author

Message

Pulivarthi Sasidhar

Posted Wednesday, March 31, 2010 4:10 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:30 AM
Points: 870, Visits: 522

hi,

I want to get the Result that all Users existed in Database ,
Accessible to Databases, REad or write Permissions.

Thanks,
Sasidhar P

Post #893600

Pulivarthi Sasidhar

Posted Wednesday, March 31, 2010 5:14 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:30 AM
Points: 870, Visits: 522

http://www.sqlservercentral.com/articles/Administration/listofdatabaseuserswithdatabaseroles/1545/

It helped me ......

Post #893640

Mr James Howard

Posted Thursday, April 01, 2010 2:55 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 17, 2012 1:13 AM
Points: 190, Visits: 9,356

Give this a try...

WITH perms_cte as

( select 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)

--users

SELECT 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
--role members
SELECT 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

James Howard

Post #894521

« Prev Topic | Next Topic »

Permissions