January 21, 2021 at 11:36 am
I have a stored procedure with the following:
SELECT physical_name FROM sys.master_files Where physical_name like '%' + DB_NAME() + '.%'
When the procedure is compiled normally, this runs fine, but when it is compiled With Execute As 'Paleo-Server\SQLServerZaloha', this select statement returns a null.
I ran this statement I found on the web:
grant view any definition to [Paleo-Server\SQLServerZaloha]
but it didn't help. What is wrong, and why does this not work with the 'Execute As' in effect?
January 22, 2021 at 12:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 1, 2021 at 8:56 am
Really? Not one person even has an idea?
February 1, 2021 at 5:19 pm
See this link: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver15
The login would need VIEW ANY DEFINITION to see anything in sys.master_files AND access to the database(s). If the login does not have access to the databases - you would need to add additional permissions such as CONTROL SERVER (which you probably don't want to do).
Depending on what you are trying to accomplish - you probably need: VIEW ANY DEFINITION, VIEW SERVER STATE, create the user in every database - and possible add the user to one of the agent roles (if you want the login to have access to that data).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 1, 2021 at 11:26 pm
Correct me if I'm wrong, please... [Paleo-Server\SQLServerZaloha] is the instance name, correct? That's not a login.
If, like most people do, the owner of the databases is the disabled "sa" user, you should try EXECUTE AS OWNER. If not, you may have to use the login that the SQL Server Service uses.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2021 at 3:19 pm
See this link: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver15
The login would need VIEW ANY DEFINITION to see anything in sys.master_files AND access to the database(s). If the login does not have access to the databases - you would need to add additional permissions such as CONTROL SERVER (which you probably don't want to do).
Depending on what you are trying to accomplish - you probably need: VIEW ANY DEFINITION, VIEW SERVER STATE, create the user in every database - and possible add the user to one of the agent roles (if you want the login to have access to that data).
I ran the statement (OP):
grant view any definition to [Paleo-Server\SQLServerZaloha]
but the problem persists. What I want is to find the path to the database file on the server, and this is what I found to give me that info. Further in the procedure, I execute DIR commands, and I need to impersonate a user with access to the server's file system, hence the Execute As.
February 2, 2021 at 4:35 pm
Correct me if I'm wrong, please... [Paleo-Server\SQLServerZaloha] is the instance name, correct? That's not a login.
If, like most people do, the owner of the databases is the disabled "sa" user, you should try EXECUTE AS OWNER. If not, you may have to use the login that the SQL Server Service uses.
It is a login:
USE [PaleoControls]
GO
CREATE USER [Paleo-Server\SQLServerZaloha] FOR LOGIN [Paleo-Server\SQLServerZaloha]
GO
February 2, 2021 at 4:40 pm
Correct me if I'm wrong, please... [Paleo-Server\SQLServerZaloha] is the instance name, correct? That's not a login.
If, like most people do, the owner of the databases is the disabled "sa" user, you should try EXECUTE AS OWNER. If not, you may have to use the login that the SQL Server Service uses.
Wait, that's all messed up. That's what I copied from SSMS, but it doesn't look right. Maybe that's what's wrong. I'm still (after so many years) kind of fuzzy on the concepts of logins, rules, users and how privileges cascade through these items. No matter how much I read, and how much I experiment, I keep running into something new that again shows me I STILL don't understand the basics of this.
February 2, 2021 at 5:12 pm
I ran the statement (OP):
grant view any definition to [Paleo-Server\SQLServerZaloha]but the problem persists. What I want is to find the path to the database file on the server, and this is what I found to give me that info. Further in the procedure, I execute DIR commands, and I need to impersonate a user with access to the server's file system, hence the Execute As.
If the login does not have access to the database - they cannot view any definition in that database. I know the documentation states that is all that is necessary - but the documentation isn't correct. The login also needs VIEW SERVER STATE to get the permissions you expect.
Now - the question I have is why are you trying to run that procedure as a different user? If it executes fine as is - then why not just leave it that way? No reason to EXECUTE AS a different login when the default is EXECUTE AS owner which grants appropriate permissions to view any definition.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 2, 2021 at 6:39 pm
I ran the statement (OP):
grant view any definition to [Paleo-Server\SQLServerZaloha]
but the problem persists. What I want is to find the path to the database file on the server, and this is what I found to give me that info. Further in the procedure, I execute DIR commands, and I need to impersonate a user with access to the server's file system, hence the Execute As.
If the login does not have access to the database - they cannot view any definition in that database. I know the documentation states that is all that is necessary - but the documentation isn't correct. The login also needs VIEW SERVER STATE to get the permissions you expect.
Okay, thanks you - I didn't know that. I'll see if I can make that work.
Now - the question I have is why are you trying to run that procedure as a different user? If it executes fine as is - then why not just leave it that way? No reason to EXECUTE AS a different login when the default is EXECUTE AS owner which grants appropriate permissions to view any definition.
Bolded above in what you quoted from me.
February 2, 2021 at 6:44 pm
Okay - let's take a step back...can you outline what you need to do with this procedure? Maybe there is a better way to approach the problem. This sounds like something that might be better solved using an agent job running a PS script - but not sure.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 2, 2021 at 8:48 pm
Okay - let's take a step back...can you outline what you need to do with this procedure? Maybe there is a better way to approach the problem. This sounds like something that might be better solved using an agent job running a PS script - but not sure.
Gladly – if you can come up with a better way to do this, I will be delighted. One of my pet peeves with SQL Server is the lack of decent access to reading a machine's file/folder structure.
The database is a museum collection management system. There are around 130,000 records in the primary table and anywhere from a few to a few thousand records in several dozen auxiliary tables - nowhere near any of SQL Server's limits, but large enough to have performance problems if I design poorly. The gist of the whole problem is, in the folder that contains the database files, there is a subfolder/subfolders with around 60,000 'attachment' files, mostly photographs of the collection objects. Rather than putting the photographs into the database, or trying to make some solid link from record to photographs, I have implemented a naming scheme. The record ID numbers have a specific format – one or two letters, a number, and an optional one or two more letters, or plus sign, or minus sign. When the user opens the details of a record, the database looks for files which are named beginning with the ID for that record. Record F1 will find and display the name of files F1.jpg, F1_sideview.tif, F1 notes.txt, and so on, but will NOT pick up F1a.jpg, F1-damages.docx, F11.bmp, etc. The code in the stored procedure to correctly isolate the ID number and discard things that look similar but are not correct works quite nicely. Initially, I had the procedure go directly to the disk and show what it found. However, the girl doing most of the photography for this system is rather industrious, and the number of photographs soon grew to volumes that made this impractical. I then tried putting a 'Show attachments' button on the detail form, to cut down on the time needed to display a record, but that also didn't work well. It still took an unacceptably long time, even when it was not needed all that often (users don't need to see this info on every interaction with a record), it was a PIA for the user to have to click the button each time, when I made it a toggle, it was a PIA to remember to turn it off again, and it was just generally clunky. I then made a separate table into which I copy the filenames, internal ID of the record, and a few other details. That made the response time instant, since I am simply querying a joined and indexed table of 60,000, rather than interrogating the server's file/folder structure. An additional benefit of the instant response is that I can now easily filter records that have or don't have attachments.
There are two ways for attachment files to appear in this subfolder:
1. There are command buttons in the detail view of a record for selecting a file and copying it to the server. The underlying code takes care of proper naming and placement.
2. Users (mostly the photographer) can manually name the files correctly and copy them into the server's folder via the internal network, with no interaction with the database. This is much more convenient for her, since she can name the photographs as she goes, and the simply dump an accumulated bunch of work into the folder. (There are subfolders as well – not all 60,000 files are in one folder, but that is not germane to the problem at hand. The lookups for all that work perfectly.)
I originally looked up how to use DIR commands from SQL Server and got it working, but it was years ago and I didn't really know what I was doing – I just kept trying things I found on the net until I finally got something to work. I recently migrated from the original 2008r2 to 2014, and I don't remember exactly how I did it back then - I think it was via a dummy account with credentials, but I'm not sure exactly what finally made it all work. I don't even think I really understood it back then, but it worked and I had other tasks on my plate.
The stored procedure to update the attachments table works fine, when I run it, but I have full admin privileges in the machine. Not an accident – I am the only person who services this thing, but we have a domain account system in the institution, and users have various local privileges in various machines. I don't want to give all user accounts admin privileges in the machine, and it's not necessary – I had it working once without them, but I had hardcoded the name of the subfolder, rather than reading it from the database, as I am trying to do now. The procedure is run unattended from a service account every night, to pick up files that have been simply dropped into the folder, and is run each time a file is added via the database. It has parameters to specify whether to update the table for one record, an entire catalog (specified by the leading letter(s) of the ID) or the entire dataset. When a user adds a file via the detail form, it updates only records for that one ID, but they can manually specify that they want to update an entire catalog, if they want to see some stuff that was manually added just that day, or the entire dataset, if there is reason to suspect that there may be a problem. (There are also procedures for locating 'orphan' files – files which are not named correctly, or are named correctly but have no corresponding record in the database. This does happen – the photographer may take pictures of something that has not yet been recorded in the database. Not a problem, when a record is created, the update routine will pick up the appropriate files on the nightly maintenance run, or the user can immediately request an update for just that one new record, and the stored procedure will find those previously 'orphan' files and add them to the attachments table.)
Sorry about the length of this post – I do go on, don't I?
Anyway, in a nutshell:
I need to read the file structure of my server.
If I run under the user's permissions, I can get the location of the database, but I cannot read the file structure.
If I run under different permissions, I can read the file structure, but I can't get the location of the database - as I wrote in the original post, the query:
SELECT physical_name FROM sys.master_files...
returns nothing.
February 2, 2021 at 9:14 pm
Not that you have the time - but this sounds like something Filestream was designed to accomplish and probably would be the better option long term.
With that said - there are other options to get the information into SQL Server. Instead of using SQL to query the file system - you could use a Powershell script. That script could easily parse a directory and upload data to SQL using Invoke-SqlCmd.
Back to your problem - if the stored procedure is created by you (sysadmin) without an EXECUTE AS clause and then executed by a user that only has access to execute the procedure, that procedure should have the same level of access. Not sure how you are getting the file system data - but if you are using xp_cmdshell then that procedure has an associated proxy account.
As long as the proxy account has access to the folders - you shouldn't have any need to use EXECUTE AS to get the information from the tables or from the file system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 2, 2021 at 9:16 pm
Further to that - if you are using something else...then move the file system stuff to its own procedure and use EXECUTE AS on that procedure. You can create a temp table in the outer procedure and populate the temp table on the inner procedure from the file system calls - this would give the outer procedure access to the tables and the inner procedure access to the file system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 2, 2021 at 10:22 pm
Further to that - if you are using something else...then move the file system stuff to its own procedure and use EXECUTE AS on that procedure. You can create a temp table in the outer procedure and populate the temp table on the inner procedure from the file system calls - this would give the outer procedure access to the tables and the inner procedure access to the file system.
I looked at FILESTREAM and it wasn't what I wanted. It would not only require a complete redesign of the attachment handling software, but it would also require the photographer to interact with the database for storing new photographs, and other users would have a much tougher time of copying out photos that they want for research, exhibitions, publications. Storing them using the native file system is something that everyone understands. Pissed me right off, too - when I first found out about it, it sounded like just the thing.
Here is what I use to get the file information:
set @LocalRootPrilohy = 'mode con cols=500 && powershell.exe "Get-ChildItem ' + @LocalRoot + @Soukrome_Verejne + '\' + @KatalogKus + '\* | select FullName, LastWriteTime, Mode | format-table -AutoSize"'
INSERT @PrilohyDruheEvidenceTmp (CestaSoubor) exec master.dbo.xp_cmdshell @LocalRootPrilohy
It's a PowerShell command - would a stand-alone PowerShell script be better? I'm not very good at PowerShell - haven't needed it that much, but I could probably knock together a script that would do this. Would users have the proper privilege to execute such a script? Would the script have the necessary privilege to update my attachments table?
As long as the proxy account has access to the folders - you shouldn't have any need to use EXECUTE AS to get the information from the tables or from the file system.
Isn't that what EXECUTE AS does - use a proxy account? I think that's how I had it set up originally, but once again, permissions of all these various elements is something I'm not clear on.
I suppose I could make the DIR lookup a separate procedure, with EXECUTE AS, and pass the name of the root folder gotten by the user with his normal privilege before calling the procedure with the DIR command, but that seems awkward. I think I should be able to do it all in one routine.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply