SQL Server displayed 'Grantor' vs system tables resultCan I restore the defult SQL Server Public Role?SELECT...
What is an efficient way to digitize a family photo collection?
Abstract algebra subgroup proof verification
Short story about a man betting a group he could tell a story, and one of them would disappear and the others would not notice
When does a person lose diplomatic status?
Is layered encryption more secure than long passwords?
How do I break down the math symbols in this equation
Taking an academic pseudonym?
Can an attached stirge deal no more than 10 HP of damage before it detaches?
Can a Way of Shadow Monk use Shadow Step to teleport to a dark ceiling and then body slam another creature?
How do I narratively explain how in-game circumstances do not mechanically allow a PC to instantly kill an NPC?
Why don't you get burned by the wood benches in a sauna?
When distributing a Linux kernel driver as source code, what's the difference between Proprietary and GPL license?
What is the reward?
What really causes series inductance of capacitors?
Why is Shelob considered evil?
Why do single electrical receptacles exist?
Two oatmeal pies a day keep the doctor away?
Will the duration of traveling to Ceres using the same tech developed for going to Mars be proportional to the distance to go to Mars or not?
Why don't programs completely uninstall (remove all their files) when I remove them?
Why does a single AND gate need 60 transistors?
What does "south of due west" mean?
Do the speed limit reductions due to pollution also apply to electric cars in France?
Coworker asking me to not bring cakes due to self control issue. What should I do?
Boss asked me to sign a resignation paper without a date on it along with my new contract
SQL Server displayed 'Grantor' vs system tables result
Can I restore the defult SQL Server Public Role?SELECT permission denied trying to import data into ExcelSQL Server user cannot select from a table it just created?Grant access to all objects (with a few exceptions) to a roleCan I trust the effective permissions of default database roles?Granting minimum select permission on system “sys” tables in every databaseMinimal permissions required to read index size in SQL ServerPermissions via Trusted Connection partially workingOracle SQL: How to give user the permissions to SELECT from a table?Does SQL Server Management Studio 2017 Contain a New Explain Feature
When I select server properties -> permissions, SQL server appears to display the 'grantor' as the (sa-level) login of the person who created the login. But when I select from the system tables (see below), the 'grantor' is "correctly" displayed as 'sa'. If this is correct, where does properties -> permissions get its 'grantor' data from? Or is my join incorrect ?
My system tables join:
select prin.name [Grantor], prin2.name [Grantee], perm.*
from sys.server_permissions perm
left join sys.server_principals prin
on perm.grantor_principal_id =prin.principal_id
left join sys.server_principals prin2
on perm.grantee_principal_id =prin2.principal_id
The actual question here is: where is the 'proximate' grantor (ie, the logged-in person creating a new login) retrieved from (stored) in Server Properties -> Permissions ? Obviously not in the tables I've used in the join.
sql-server sql-server-2008-r2 permissions
edited Jan 18 '17 at 16:30
Max Vernon
50.9k13112224
asked Jan 18 '17 at 16:16
user115336user115336
411
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
When I select server properties -> permissions, SQL server appears to display the 'grantor' as the (sa-level) login of the person who created the login. But when I select from the system tables (see below), the 'grantor' is "correctly" displayed as 'sa'. If this is correct, where does properties -> permissions get its 'grantor' data from? Or is my join incorrect ?
My system tables join:
select prin.name [Grantor], prin2.name [Grantee], perm.*
from sys.server_permissions perm
left join sys.server_principals prin
on perm.grantor_principal_id =prin.principal_id
left join sys.server_principals prin2
on perm.grantee_principal_id =prin2.principal_id
The actual question here is: where is the 'proximate' grantor (ie, the logged-in person creating a new login) retrieved from (stored) in Server Properties -> Permissions ? Obviously not in the tables I've used in the join.
sql-server sql-server-2008-r2 permissions
edited Jan 18 '17 at 16:30
Max Vernon
50.9k13112224
asked Jan 18 '17 at 16:16
user115336user115336
411
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
When I select server properties -> permissions, SQL server appears to display the 'grantor' as the (sa-level) login of the person who created the login. But when I select from the system tables (see below), the 'grantor' is "correctly" displayed as 'sa'. If this is correct, where does properties -> permissions get its 'grantor' data from? Or is my join incorrect ?
My system tables join:
select prin.name [Grantor], prin2.name [Grantee], perm.*
from sys.server_permissions perm
left join sys.server_principals prin
on perm.grantor_principal_id =prin.principal_id
left join sys.server_principals prin2
on perm.grantee_principal_id =prin2.principal_id
The actual question here is: where is the 'proximate' grantor (ie, the logged-in person creating a new login) retrieved from (stored) in Server Properties -> Permissions ? Obviously not in the tables I've used in the join.
sql-server sql-server-2008-r2 permissions
edited Jan 18 '17 at 16:30
Max Vernon
50.9k13112224
asked Jan 18 '17 at 16:16
user115336user115336
411
When I select server properties -> permissions, SQL server appears to display the 'grantor' as the (sa-level) login of the person who created the login. But when I select from the system tables (see below), the 'grantor' is "correctly" displayed as 'sa'. If this is correct, where does properties -> permissions get its 'grantor' data from? Or is my join incorrect ?
My system tables join:
select prin.name [Grantor], prin2.name [Grantee], perm.*
from sys.server_permissions perm
left join sys.server_principals prin
on perm.grantor_principal_id =prin.principal_id
left join sys.server_principals prin2
on perm.grantee_principal_id =prin2.principal_id
The actual question here is: where is the 'proximate' grantor (ie, the logged-in person creating a new login) retrieved from (stored) in Server Properties -> Permissions ? Obviously not in the tables I've used in the join.
sql-server sql-server-2008-r2 permissions
sql-server sql-server-2008-r2 permissions
edited Jan 18 '17 at 16:30
Max Vernon
50.9k13112224
asked Jan 18 '17 at 16:16
user115336user115336
411
edited Jan 18 '17 at 16:30
Max Vernon
50.9k13112224
asked Jan 18 '17 at 16:16
user115336user115336
411
edited Jan 18 '17 at 16:30
Max Vernon
50.9k13112224
edited Jan 18 '17 at 16:30
Max Vernon
50.9k13112224
edited Jan 18 '17 at 16:30
Max Vernon
50.9k13112224
50.9k13112224
asked Jan 18 '17 at 16:16
user115336user115336
411
asked Jan 18 '17 at 16:16
user115336user115336
411
asked Jan 18 '17 at 16:16
user115336user115336
411
411
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
SQL Server Management Studio executes a series of dynamic SQL statements to obtain the list you're seeing.
You can capture the statements it executes with an Extended Events session, or by running a trace against a non-prod instance while viewing the permissions tab.
I just did that, and it looks like SSMS is pulling the data from the tables in a very similar way to your query:
DECLARE @_msparam_0 nvarchar(4000);
SET @_msparam_0 = '##MS_AgentSigningCertificate##';
SELECT ascii(prmssn.state) AS [PermissionState]
, null AS [Code]
, grantor_principal.name AS [Grantor]
, prmssn.type AS [SqlCodePP]
, CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN
CASE (SELECT oc.type FROM sys.server_principals AS oc WHERE oc.principal_id = prmssn.major_id)
WHEN 'R' THEN CASE prmssn.class
WHEN 4 THEN 201
ELSE 301
END
WHEN 'A' THEN 202
ELSE
CASE prmssn.class
WHEN 4 THEN 200
ELSE 101
END
END
ELSE prmssn.class
END AS [HiddenObjectClass]
FROM sys.server_permissions AS prmssn
INNER JOIN sys.server_principals AS grantor_principal ON
grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.server_principals AS grantee_principal ON
grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE (prmssn.class = 100)
AND (grantee_principal.name = @_msparam_0);
This contains the same joins as in your query. I've reformatted the query for readability.
On my SQL Server 2008 R2 instance, I see the same results from your query as I see in the Server Properties dialog.
answered Jan 18 '17 at 17:16
Max VernonMax Vernon
50.9k13112224
1
Max: Thanks for your work and explanation. Leads me to further exploration of permission.class. I've been talking out the logic of what we see in Server Properties v. sys.principals/sys.permissions with a coworker ... and I think we're almost there. Sure appreciated your help.
– user115336
Jan 18 '17 at 19:31
If an answer helped you, it's a good idea to up-vote the answer. This signals to future visitors the value of the answer, and provides incentive to the person (me) who answered the question to continue to do so.
– Max Vernon
Jan 26 '17 at 1:53
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f161483%2fsql-server-displayed-grantor-vs-system-tables-result%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
SQL Server Management Studio executes a series of dynamic SQL statements to obtain the list you're seeing.
You can capture the statements it executes with an Extended Events session, or by running a trace against a non-prod instance while viewing the permissions tab.
I just did that, and it looks like SSMS is pulling the data from the tables in a very similar way to your query:
DECLARE @_msparam_0 nvarchar(4000);
SET @_msparam_0 = '##MS_AgentSigningCertificate##';
SELECT ascii(prmssn.state) AS [PermissionState]
, null AS [Code]
, grantor_principal.name AS [Grantor]
, prmssn.type AS [SqlCodePP]
, CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN
CASE (SELECT oc.type FROM sys.server_principals AS oc WHERE oc.principal_id = prmssn.major_id)
WHEN 'R' THEN CASE prmssn.class
WHEN 4 THEN 201
ELSE 301
END
WHEN 'A' THEN 202
ELSE
CASE prmssn.class
WHEN 4 THEN 200
ELSE 101
END
END
ELSE prmssn.class
END AS [HiddenObjectClass]
FROM sys.server_permissions AS prmssn
INNER JOIN sys.server_principals AS grantor_principal ON
grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.server_principals AS grantee_principal ON
grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE (prmssn.class = 100)
AND (grantee_principal.name = @_msparam_0);
This contains the same joins as in your query. I've reformatted the query for readability.
On my SQL Server 2008 R2 instance, I see the same results from your query as I see in the Server Properties dialog.
answered Jan 18 '17 at 17:16
Max VernonMax Vernon
50.9k13112224
1
Max: Thanks for your work and explanation. Leads me to further exploration of permission.class. I've been talking out the logic of what we see in Server Properties v. sys.principals/sys.permissions with a coworker ... and I think we're almost there. Sure appreciated your help.
– user115336
Jan 18 '17 at 19:31
If an answer helped you, it's a good idea to up-vote the answer. This signals to future visitors the value of the answer, and provides incentive to the person (me) who answered the question to continue to do so.
– Max Vernon
Jan 26 '17 at 1:53
add a comment |
SQL Server Management Studio executes a series of dynamic SQL statements to obtain the list you're seeing.
You can capture the statements it executes with an Extended Events session, or by running a trace against a non-prod instance while viewing the permissions tab.
I just did that, and it looks like SSMS is pulling the data from the tables in a very similar way to your query:
DECLARE @_msparam_0 nvarchar(4000);
SET @_msparam_0 = '##MS_AgentSigningCertificate##';
SELECT ascii(prmssn.state) AS [PermissionState]
, null AS [Code]
, grantor_principal.name AS [Grantor]
, prmssn.type AS [SqlCodePP]
, CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN
CASE (SELECT oc.type FROM sys.server_principals AS oc WHERE oc.principal_id = prmssn.major_id)
WHEN 'R' THEN CASE prmssn.class
WHEN 4 THEN 201
ELSE 301
END
WHEN 'A' THEN 202
ELSE
CASE prmssn.class
WHEN 4 THEN 200
ELSE 101
END
END
ELSE prmssn.class
END AS [HiddenObjectClass]
FROM sys.server_permissions AS prmssn
INNER JOIN sys.server_principals AS grantor_principal ON
grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.server_principals AS grantee_principal ON
grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE (prmssn.class = 100)
AND (grantee_principal.name = @_msparam_0);
This contains the same joins as in your query. I've reformatted the query for readability.
On my SQL Server 2008 R2 instance, I see the same results from your query as I see in the Server Properties dialog.
answered Jan 18 '17 at 17:16
Max VernonMax Vernon
50.9k13112224
1
Max: Thanks for your work and explanation. Leads me to further exploration of permission.class. I've been talking out the logic of what we see in Server Properties v. sys.principals/sys.permissions with a coworker ... and I think we're almost there. Sure appreciated your help.
– user115336
Jan 18 '17 at 19:31
If an answer helped you, it's a good idea to up-vote the answer. This signals to future visitors the value of the answer, and provides incentive to the person (me) who answered the question to continue to do so.
– Max Vernon
Jan 26 '17 at 1:53
add a comment |
SQL Server Management Studio executes a series of dynamic SQL statements to obtain the list you're seeing.
You can capture the statements it executes with an Extended Events session, or by running a trace against a non-prod instance while viewing the permissions tab.
I just did that, and it looks like SSMS is pulling the data from the tables in a very similar way to your query:
DECLARE @_msparam_0 nvarchar(4000);
SET @_msparam_0 = '##MS_AgentSigningCertificate##';
SELECT ascii(prmssn.state) AS [PermissionState]
, null AS [Code]
, grantor_principal.name AS [Grantor]
, prmssn.type AS [SqlCodePP]
, CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN
CASE (SELECT oc.type FROM sys.server_principals AS oc WHERE oc.principal_id = prmssn.major_id)
WHEN 'R' THEN CASE prmssn.class
WHEN 4 THEN 201
ELSE 301
END
WHEN 'A' THEN 202
ELSE
CASE prmssn.class
WHEN 4 THEN 200
ELSE 101
END
END
ELSE prmssn.class
END AS [HiddenObjectClass]
FROM sys.server_permissions AS prmssn
INNER JOIN sys.server_principals AS grantor_principal ON
grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.server_principals AS grantee_principal ON
grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE (prmssn.class = 100)
AND (grantee_principal.name = @_msparam_0);
This contains the same joins as in your query. I've reformatted the query for readability.
On my SQL Server 2008 R2 instance, I see the same results from your query as I see in the Server Properties dialog.
answered Jan 18 '17 at 17:16
Max VernonMax Vernon
50.9k13112224
SQL Server Management Studio executes a series of dynamic SQL statements to obtain the list you're seeing.
You can capture the statements it executes with an Extended Events session, or by running a trace against a non-prod instance while viewing the permissions tab.
I just did that, and it looks like SSMS is pulling the data from the tables in a very similar way to your query:
DECLARE @_msparam_0 nvarchar(4000);
SET @_msparam_0 = '##MS_AgentSigningCertificate##';
SELECT ascii(prmssn.state) AS [PermissionState]
, null AS [Code]
, grantor_principal.name AS [Grantor]
, prmssn.type AS [SqlCodePP]
, CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN
CASE (SELECT oc.type FROM sys.server_principals AS oc WHERE oc.principal_id = prmssn.major_id)
WHEN 'R' THEN CASE prmssn.class
WHEN 4 THEN 201
ELSE 301
END
WHEN 'A' THEN 202
ELSE
CASE prmssn.class
WHEN 4 THEN 200
ELSE 101
END
END
ELSE prmssn.class
END AS [HiddenObjectClass]
FROM sys.server_permissions AS prmssn
INNER JOIN sys.server_principals AS grantor_principal ON
grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.server_principals AS grantee_principal ON
grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE (prmssn.class = 100)
AND (grantee_principal.name = @_msparam_0);
This contains the same joins as in your query. I've reformatted the query for readability.
On my SQL Server 2008 R2 instance, I see the same results from your query as I see in the Server Properties dialog.
answered Jan 18 '17 at 17:16
Max VernonMax Vernon
50.9k13112224
edited Jan 18 '17 at 17:25
answered Jan 18 '17 at 17:16
Max VernonMax Vernon
50.9k13112224
answered Jan 18 '17 at 17:16
Max VernonMax Vernon
50.9k13112224
answered Jan 18 '17 at 17:16
Max VernonMax Vernon
50.9k13112224
50.9k13112224
1
Max: Thanks for your work and explanation. Leads me to further exploration of permission.class. I've been talking out the logic of what we see in Server Properties v. sys.principals/sys.permissions with a coworker ... and I think we're almost there. Sure appreciated your help.
– user115336
Jan 18 '17 at 19:31
If an answer helped you, it's a good idea to up-vote the answer. This signals to future visitors the value of the answer, and provides incentive to the person (me) who answered the question to continue to do so.
– Max Vernon
Jan 26 '17 at 1:53
add a comment |
1
Max: Thanks for your work and explanation. Leads me to further exploration of permission.class. I've been talking out the logic of what we see in Server Properties v. sys.principals/sys.permissions with a coworker ... and I think we're almost there. Sure appreciated your help.
– user115336
Jan 18 '17 at 19:31
If an answer helped you, it's a good idea to up-vote the answer. This signals to future visitors the value of the answer, and provides incentive to the person (me) who answered the question to continue to do so.
– Max Vernon
Jan 26 '17 at 1:53
1
1
Max: Thanks for your work and explanation. Leads me to further exploration of permission.class. I've been talking out the logic of what we see in Server Properties v. sys.principals/sys.permissions with a coworker ... and I think we're almost there. Sure appreciated your help.
– user115336
Jan 18 '17 at 19:31
Max: Thanks for your work and explanation. Leads me to further exploration of permission.class. I've been talking out the logic of what we see in Server Properties v. sys.principals/sys.permissions with a coworker ... and I think we're almost there. Sure appreciated your help.
– user115336
Jan 18 '17 at 19:31
If an answer helped you, it's a good idea to up-vote the answer. This signals to future visitors the value of the answer, and provides incentive to the person (me) who answered the question to continue to do so.
– Max Vernon
Jan 26 '17 at 1:53
If an answer helped you, it's a good idea to up-vote the answer. This signals to future visitors the value of the answer, and provides incentive to the person (me) who answered the question to continue to do so.
– Max Vernon
Jan 26 '17 at 1:53
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f161483%2fsql-server-displayed-grantor-vs-system-tables-result%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
