系统管理员帐户的权限级别拒绝？ [英] Permissions level denial for a sysadmin account?

问题描述

问候语。我在一家小型汽车制造厂工作。我目前正在生成一个位于安全网络位置的文件夹的文件列表，该文件夹只能由执行管理人员和我们的"管理员"访问。帐户。我们的管理员帐户在我们的SQL服务器中也是
sysadmin。 

Greetings. I work for a small automotive manufacturing plant. I am currently generating a file listing of a folder located in a secured network location only accessible to executive managment and our "Administrator" account. Our admin account is also sysadmin in our SQL server. 

我创建了一个名为"CCR_Filepath"的表格。在SQL中。我的计划是每日工作将删除"CCR_Filepath"中的所有内容。然后重新加载网络位置中的所有文件路径。以管理员身份登录SSMS，我可以
直接在表中插入值，但是我无法从网络位置获取文件路径。我必须使用"EXECUTE AS USER ='TESTCO \Administrator'"命令从该网络位置提取文件路径信息，即使这是我记录的
到SQL中;这是我使用此代码生成文件路径的唯一方法：

I've created a table titled "CCR_Filepath" in SQL. My plan is to have a daily job that will delete everything in "CCR_Filepath" and then reload all the file paths in the network location. Signing into SSMS as the administrator, I can insert values directly into the table, but I cannot get the filepaths from the network location. I have to use "EXECUTE AS USER = 'TESTCO\Administrator'" command to pull the filepath info from that network location, even though that's what I'm logged into SQL as; This is the only way I can generate the file path using this code:

execute as user = 'TESTCO\Administrator'
DROP TABLE #TEMPA
DECLARE @FP AS NVARCHAR(150) 
SELECT @FP = 
'\\TESTCO\PLANT ADMIN\ACCOUNTING\Fixed Assets\CCR\CCR Copies\CCR 2019 Copies' 
CREATE TABLE #TEMPA 
  ( 
     FILEPATH NVARCHAR(250), 
     D        INT, 
     F        INT 
  ) 
INSERT INTO #TEMPA 
EXEC XP_DIRTREE 
  @FP, 
  10, 
  1 
  SELECT CONCAT('\\TESTCO\PLANT ADMIN\ACCOUNTING\Fixed Assets\CCR\CCR Copies\CCR 2019 Copies\',FILEPATH) AS [FILEPATH], FILEPATH AS [DOCNAME] FROM #TEMPA

使用此声明将这些值加载到我的#TEMPA临时表中。但是，当我使用"EXECUTE AS ..."时声明我现在收到以下错误：

Using this statement will load those values into my #TEMPA temptable. However, When I use the "EXECUTE AS..." statement I now get the following error :

"

Msg 229，Level 14，State 5，Line 2

对象'CCR_Filepath'，数据库'Ignition'，schema'dbo'上的INSERT权限被拒绝。"

Msg 229, Level 14, State 5, Line 2
The INSERT permission was denied on the object 'CCR_Filepath', database 'Ignition', schema 'dbo'."

此管理员是testco也是一个系统管理员，所以我不完全确定是什么导致了这个问题？运行两个查询窗口，一个使用execute as语句，另一个运行第三个查询窗口以显示用户活动，显示两个查询
正在使用完全相同的用户，一个被拒绝。之前有人遇到过这种情况吗？

This administror for testco is also a sysadmin, So I'm not entirely sure what's causing this issue? Running two query windows one with the execute as statement and the other without and running a third query window to show users active shows that both queries are using the exact same user, one being rejected. Anyone come across this before?

推荐答案

执行登录='TESTCO \Administrator'

EXECUTE AS LOGIN = 'TESTCO\Administrator'

这篇关于系统管理员帐户的权限级别拒绝？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！