help to find role permission

• rpatil22

  Ten Centuries

  Points: 1371

  More actions

  May 12, 2011 at 7:49 am

  #238029

  I created database role and granted select permission to it

  Create role db_testrole

  go

  Grant select to db_testrole

  go

  However following sp not showing that role and permission

  EXEC sp_dbfixedrolepermission;

  is there any other sp to check this?

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  May 12, 2011 at 9:37 am

  #1323765

  Try this code to determine if it is what you require.

  SELECT

  u.name COLLATE Latin1_General_CI_AS AS 'User',

  schema_name(o.schema_id) As 'Schema',

  o.name COLLATE Latin1_General_CI_AS AS 'Object' ,

  p.permission_name COLLATE Latin1_General_CI_AS AS 'Action'

  into #tmp

  FROM sys.database_permissions p, sys.database_principals u, sys.all_objects o

  WHERE o.object_id = p.major_id

  AND u.name = 'Db_TestRole'

  SELECT * FROM #tmp

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]