March 20, 2020 at 3:23 pm
I modified a proc that returns a result set of all user emails to the application in question, with one column that displays a sundial if that user is logged in.
I did this by doing a left outer join to sys.dm_exec_sessions
Left join sys.dm_exec_sessions b
on login_name = Su.name
and program_name like '<my app name>%'
It works perfectly when I log in into the app, because I DO have sysadmin priv
The users that would run this proc from the UI do not have sysadmin priv.
When a regular user tries it, all they get is a sundial next to ONLY their name.
The proc will be called by managers within the application and need to see if their workers are logged in
select * from sys.dm_exec_sessions
where program_name like '<myapp name>%'
in the past to allow a lower user to run a system stored procedure we implemented a certificate, created a wrapper proc to the system proc and then "signed" the proc using the newly created certificate
ALTER PROC [dbo].[sp__fileexist]
@FileName varchar(255)
, @File_Exists INT OUTPUT
AS
EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT
These are the steps taken to do that...APassword is not really the password
-- execute these command as a user with sysadmin role
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'APassword';
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'APassword';
GO
CREATE CERTIFICATE [cert_administration]
WITH SUBJECT = 'administration signing cert'
GO
CREATE LOGIN [cert_administration] FROM CERTIFICATE [cert_administration]
go
EXEC sp_addsrvrolemember 'cert_administration', 'sysadmin'
go
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'APassword';
GO
ADD SIGNATURE TO dbo.sp__fileexist
BY CERTIFICATE [cert_administration]
so for my proc I tried to 'sign" the proc (in a different DB) with that same cert
Use master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'APassword';
GO
Add signature to OtherDB.[dbo].[usp_get_user_email]
by certificate cert_administration
I get the following error
Msg 15151, Level 16, State 1, Line 34
Cannot alter the object 'OtherDB.dbo.usp_get_user_email', because it does not exist or you do not have permission.
so ended up creating the certificate in the OtherDB (using the same commands as above - no errors), retried the Add Signature command it didn't get me an error, but when I run the proc I still only get the SunDial for the current user NOT all the users, like I see when I signon and call the proc or run the app and launch the window.
For warned I'm not a DBA and this certificate and impersonation stuff is beyond my current bailiwick.
BTW tried to add at the top of the proc
Execute as Login = 'superduperlogin'
and get an error when trying to run from the lower level user
Msg 15517, Level 16, State 1, Procedure usp_bto_get_user_email, Line 37 [Batch Start Line 2]
Cannot execute as the database principal because the principal "superduperlogin" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Anxiously waiting for the brainiacs to help me.
March 20, 2020 at 7:19 pm
You need to use the same certificate in both the master database and the user database, not just a certificate with the same name.
Here are the steps, with the changes to your process in bold:
Eddie Wuerch
MCM: SQL
March 20, 2020 at 7:45 pm
Seems like a lot of work. Why not just use WITH EXECUTE AS OWNER at the beginning of the proc?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2020 at 1:04 am
hey guys didn't want to leave you hanging, but Jeff's suggestion was the easiest fix.
In the query window, signed on as user A, running the proc it showed the users logged into the app correctly, no matter if the user was a member of sysadmin or not.
But from the app, User A could see that user B was signed on, but not himself....and user B could see user A was signed on but not himself.
within the Development Tool (powerbuilder), when debugging the datawindow (a data grid), signed on as lower user, I would see both users logged in
But when debugging the app, I would get the same issue - can see the other person not myself
through debugging and adding more columns to the datawindow I could finally see that calling the stored procedure as either User A or User B, it got impersonated to SA (the owner), and thus the code to make visible the sundial,
if ue.login=loggedin then 1 else 0 end if would not show.
I changed the proc's resultset for the column to be
, case when ue.login = @login then ue.login
else b.original_login_name
end as loggedin
As far as the certs method...I got stuck with the CREATE statement
CREATE CERTIFICATE cert_administration_asibig6
from File = '\\<servername>\userdata\chrisf\cert_administration.cer'
WITH PRIVATE KEY
(
FILE ='\\<servername>\userdata\chrisf\cert_administration_skey.pvk',
Decryption BY PASSWORD ='APassword'
)
the error was
Msg 15208, Level 16, State 1, Line 106
The certificate, asymmetric key, or private key file does not exist or has invalid format.
March 23, 2020 at 3:18 pm
Were you able to create this certificate in master or on any instance? I've had some issues with certificates in SQL Server as it doesn't seem to support every format that other programs can output.
March 23, 2020 at 3:41 pm
I was able do create the cert in the master DB,
the backup of cert to file didn't yell at me
(from here on down I couldn't do as the CREATE cert in the TheOtherDB threw an error)
March 23, 2020 at 4:01 pm
Hmmm, I would lean towards some rights here in some way. Maybe try moving the cert backup to a local drive. Ensure you're sa/dbo here as well. Try a different backup and be sure the file didn't corrupt somehow.
March 23, 2020 at 6:11 pm
I'll try the backup to a local drive to the server and then CREATE from file from that same local drive ...in a bit
March 31, 2020 at 7:57 pm
UPDATE: so with the proc working on DEV and passing QA testing(not against the QA server) it was deployed to PROD on Saturday.
Whilst the proc is not bombing, the expected results are not happening. Thankfully this is not a show stopper, just a bummer.
PROD sqlserver 13.0.5366.0 (unexpected results)
DEV sql server 9.00.5000.00 (where we tested, and got expected results)
QA sql server 13.0.5101.9 (unexpected results)
Does anybody have a clue as what could be the issue?
I suspect something to do with Sqlserver 2016 compared to sqlserver 2005
March 31, 2020 at 8:38 pm
We have no clue by what you mean by "unexpected results".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2020 at 3:52 am
Not what was expected?
April 1, 2020 at 2:57 pm
To reiterate the desired results.
any user who has access to the application and access to the window that lists out the users of the app, should see an icon next to the users' name that indicates that they are logged in.
The window's grid is based on a stored procedure, where one of the columns is the original_login_name from sys.dm_exec_sessions
the basic select statement in the proc is this
select distinct
au.login
, au.first_name
,case when AU.login = b.original_login_name then b.original_login_name
else
null
end as loggedinusername
,case when AU.login = b.original_login_name then 1
else
0
end as IsUserLoggedIn
From APP_users AU with (nolock)
Left join sys.dm_exec_sessions b
on b.original_login_name = AU.login
and b.program_name like 'Microsoft SQL Server%' --I change the program_name to be the actual UI's program name...I set that during the connection
where 1 = 1
The interesting thing is that adding WITH EXECUTE AS OWNER on the original proc worked on the sql server 2005 server and original DB where the original proc lives...('worked' = any user calling this proc shows ALL that are logged in into the app via the App's UI or Query Window (logged in as any user) )
But creating a brand new DB , new SP (the snipit below) with the WITH EXECUTE AS OWNER is NOT giving me the expected results. Even after noticing that the DB and SP had the owner as myself, and changing them to SA, I'm not getting expected results. I only see that myself is logged in when calling the proc...not the others that are logged in
USE [Test_db]
GO
/****** Object: StoredProcedure [dbo].[usp_Test_Who_is_logged] Script Date: 4/1/2020 10:40:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~
--SCOPE: BTO
-- PROCEDURE: [usp_Test_Who_is_logged]
-- exec usp_Test_Who_is_logged @login = 'xxxx'
--/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~
CREATE procedure [dbo].[usp_Test_Who_is_logged]
@login varchar(50)
with execute as owner
as
begin
SET NOCOUNT ON
select distinct
@login as 'passed in parm for login'
, suser_sname() as susersname --this shows the context under what creds the proc is being called....
, au.login
, au.first_name
, case
when au.login = b.original_login_name then
b.original_login_name
else
null
end as LoggedIn
,case when AU.login = b.original_login_name then 1
else
0
end as IsUserLoggedIn
, b.host_name, b.program_name, b.login_name, b.original_login_name
--select distinct au.login, au.first_name,b.host_name, b.program_name, b.login_name, b.original_login_name
From app_users AU
left outer join sys.dm_exec_sessions b
on au.login = b.original_login_name
and (program_name like 'Microsoft SQL Server Management%'
or
program_name like 'Bto%'
)
where 1=1
SET NOCOUNT OFF
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
END --/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~\~/~
GO
This is frustrating...but sure it is a simple thing I'm not seeing
April 3, 2020 at 11:06 pm
Success!:
It seems to have been the TrustWorthy attribute on the DB
it was ON on DEV and OFF on QA and PROD
list out the trustworthyness for each db
SELECT [name], SUSER_SNAME(owner_sid) TheOwnerOfDB, is_trustworthy_on
FROM sys.databaseswhere
Why my test proc didn't work as mentioned previously was because looking at my Test_db it was set to OFF too
to set it to ON
ALTER DATABASE test_db SET TRUSTWORTHY on
Upon doing THAT, in DEV, the test proc started behaving as expected (showing all the users logged in and having the expected application name)
I then went ahead and turned it on the QA server for the real DB and tried the original proc both in a query window and the UI pointing to QA...using a login that did not have sysadmin....and it worked as expected (IE : could see that just not me were logged in)
In PROD I created the test_db, test proc, added the users to the test_db, and tried to call the proc from the original DB, under the creds of a lower level user without Sysadmin, and got an error that OWNER of the original DB didn't have rights to the test_DB.
Unlike QA the owner of the original db didn't have a corresponding windows login
DBname owner
original_DB <domainname>\sqlsvcdevacct
test_db <domainname>\sqlserviceacct
when looking at the SQL Logins I could see sqlserviceacct, but not sqlsvcdevacct
to create the login that was missing I did
USE [master]
GO
CREATE LOGIN [<domainname>\sqlsvcdevacct] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [<domainname>\sqlsvcdevacct]
GO
then tried the original proc in query window as lower user and got the expected results (IE could see all the peeps logged in)
then tried the test proc in test_db and there too could see all the peeps logged in
then tried the UI against PROD db as lower level user and now saw the sungod icon next to the people logged in....as expected
so ultimately it was the trust worthyness of the DB and the DB owner not having a corresponding Windows Login on the server.
Now if someone can get my inkjet printer to NOT print wavy lines and blurry text
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply