How to check which permissions are granted to me

• Santhoshkumar KB

  SSCertifiable

  Points: 5066

  More actions

  August 12, 2009 at 1:07 am

  #209402

  Hi,

  At my work place, they say, they have granted us db_ddladmin role and datawriter permissions.(Also we have granted security_admin role)

  How to confirm this that I've been granted those permissions.

  Note: My ID is in group "XXX_Group" and permissions are granted to that group

  Is there any script to find it out?

  Thanks,

  KB

  Thanks,
  Santhosh
  

  ---

  Human Knowledge Belongs To The World !!

• Chris Chilvers

  SSCrazy

  Points: 2260

  More actions

  August 12, 2009 at 7:00 am

  #1037967

  You can use fn_my_permissions to check your permissions for a specific object in the database. http://msdn.microsoft.com/en-us/library/ms176097.aspx

  If you're after the current logins, roles, etc. that you are a member of this should work:

  SELECT

  dp.[name],

  type_desc

  FROM sys.database_principals AS dp

  WHERE is_member(dp.[name]) = 1

  UNION ALL

  SELECT

  sp.[name],

  type_desc

  FROM sys.server_principals AS sp

  WHERE is_srvrolemember(sp.[name]) = 1

  OR is_member(sp.[name]) = 1

  Edited, added script to display logins and roles for the current user.

• f_ernestog

  Ten Centuries

  Points: 1217

  More actions

  August 12, 2009 at 7:40 am

  #1037978

  You can also use the sp_helprotect stored proc to get the information about the objects you have granted or denied access to.

  sp_helprotect

  Something that I usually do is to put that into a temp table I created before, so I can apply filters to it.

  Frank.