May 25, 2012 at 8:44 am
I have a requirement to periodically check the user and connection status of a legacy client server application for user roles and connection ssl encryption. I can easily get the information I need with a simple stored procedure from sys.dm_exec_connections. The problem is the requirement or VIEW SERVER STATE for the login making the call. With that I created a proc with the EXECUTE AS OWNER option, a caller still gets the output below. I suppose the question is shouldn't sa and dbo already have VIEW SERVER STATE? I do not want to grant all users VIEW SERVER STATE
Msg 297, Level 16, State 1, Procedure spConnectionState, Line 14
The user does not have permission to perform this action.
Diagnostic Output:
SUSER_NAMEUSER_NAMEORIGINAL_LOGIN
sa dbo TestUser
ALTER PROCEDURE [dbo].[spConnectionState]
WITH EXECUTE AS OWNER
AS
BEGIN
-- DIAGNOSTIC MESSAGE
SELECT RTRIM(SUSER_NAME())AS [SUSER_NAME], RTRIM(USER_NAME()) AS [USER_NAME],RTRIM(ORIGINAL_LOGIN()) as[ORIGINAL_LOGIN]
SELECT session_id as SessionId,
cast(encrypt_option as bit) as EncryptionEnabled,
original_login() as UserId,
CAST(ISNULL(IS_SRVROLEMEMBER('sysadmin',original_login()),0) AS bit) as IsSysAdmin,
auth_scheme as Authentication,
net_transport as Protocol
FROM [sys].[dm_exec_connections]
WHERE [session_id]=@@spid
END
May 26, 2012 at 8:11 am
Well, I am able to answer my own question...
"The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. By default, this setting is OFF, but can be set to ON by using the ALTER DATABASE statement. For example, ALTER DATABASE <DBNAME> TRUSTWORTHY ON;."
May 26, 2012 at 5:28 pm
Yes, that's right (sorry I didn't see this sooner).
One note is that you should make sure that the DB in question really IS trustworthy, as there are security implications to this setting (which is why it's off by default).
In particular, what you want to be concerned with is that no non-SA user can get direct access to or control over any other user identity in the DB, because Trustworthy will allow them to extend the User (DB principals) rights back up to the corresponding Login (server principals) rights.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply