a question about sys.server_principals and sys.server_permissions

• Akayisi

  Right there with Babe

  Points: 735

  More actions

  February 18, 2014 at 6:47 am

  #288241

  Hi guys I am testing users' rights in sql server. However; I would like to add another column to sys.server_permissions which describes the name of endpoints. For instance;

  I connect database engine -> From logins (SERVER BASE)->click a user->securable tab-> add endpoint (which is DAC)-> give some rights

  But once i run this command (select * from sys.server_permissions) it gives just class_desc ;

  100SERVER002961COSQCONNECT SQLGGRANT

  105ENDPOINT102961AL ALTERGGRANT

  105ENDPOINT102961CL CONTROLWGRANT_WITH_GRANT_OPTION

  105ENDPOINT102961CO CONNECTGGRANT

  105ENDPOINT102961TO TAKE OWNERSHIPDDENY

  I want have one more column which says the name of ENDPOINT such as TSQL Named Pipes,DAC etc...

  I could not figure out where i can find it. Thanks in advance.

• Ness

  SSCarpal Tunnel

  Points: 4282

  More actions

  February 18, 2014 at 8:49 am

  #1689950

  Akayisi (2/18/2014)

  ---

  Hi guys I am testing users' rights in sql server. However; I would like to add another column to sys.server_permissions which describes the name of endpoints. For instance;

  I connect database engine -> From logins (SERVER BASE)->click a user->securable tab-> add endpoint (which is DAC)-> give some rights

  But once i run this command (select * from sys.server_permissions) it gives just class_desc ;

  100SERVER002961COSQCONNECT SQLGGRANT

  105ENDPOINT102961AL ALTERGGRANT

  105ENDPOINT102961CL CONTROLWGRANT_WITH_GRANT_OPTION

  105ENDPOINT102961CO CONNECTGGRANT

  105ENDPOINT102961TO TAKE OWNERSHIPDDENY

  I want have one more column which says the name of ENDPOINT such as TSQL Named Pipes,DAC etc...

  I could not figure out where i can find it. Thanks in advance.

  Try :

  SELECT

  SP.name

  , E.protocol_desc

  , E.type_desc

  , s.*

  FROM

  sys.server_permissions s

  JOIN sys.server_principals AS SP ON SP.principal_id = s.grantee_principal_id

  JOIN sys.endpoints AS E ON E.endpoint_id = s.major_id

  SQL DBA
  Every day is a school day, and don't trust anyone who tells you any different.
  http://sqlblogness.blogspot.co.uk