Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

a question about sys.server_principals and sys.server_permissions Expand / Collapse
Author
Message
Posted Tuesday, February 18, 2014 6:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:25 AM
Points: 38, Visits: 172
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 ;

100 SERVER 0 0 296 1 COSQ CONNECT SQL G GRANT
105 ENDPOINT 1 0 296 1 AL ALTER G GRANT
105 ENDPOINT 1 0 296 1 CL CONTROL W GRANT_WITH_GRANT_OPTION
105 ENDPOINT 1 0 296 1 CO CONNECT G GRANT
105 ENDPOINT 1 0 296 1 TO TAKE OWNERSHIP D DENY

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.
Post #1542542
Posted Tuesday, February 18, 2014 8:49 AM This worked for the OP Answer marked as solution


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 6:52 AM
Points: 179, Visits: 821
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 ;

100 SERVER 0 0 296 1 COSQ CONNECT SQL G GRANT
105 ENDPOINT 1 0 296 1 AL ALTER G GRANT
105 ENDPOINT 1 0 296 1 CL CONTROL W GRANT_WITH_GRANT_OPTION
105 ENDPOINT 1 0 296 1 CO CONNECT G GRANT
105 ENDPOINT 1 0 296 1 TO TAKE OWNERSHIP D DENY

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
Post #1542622
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse