哪个用户帐户在没有 -U 和 -P 选项的 T-SQL 脚本中运行 SQLCMD?
我在 T-SQl 脚本中使用 sqlcmd 将文本文件写入网络位置。但是,由于网络文件夹的访问权限,SQLCMD 无法写入该位置。 SP 正在我的用户帐户下运行,该帐户有权访问网络文件夹。
如果我没有在 TSQL 脚本中指定 -U 和 -P 选项,您能帮我看看 sqlcmd 将在哪个帐户下运行吗?
I am using sqlcmd in a T-SQl script to write a text file to a network location. However SQLCMD is failing to write to that location due to access permission to the network folder. SP is being run under my user account which has access to the network folder.
Could you please help me under which account sqlcmd will run if I do not specify -U and -P option in TSQL Script?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
使用它来查找用户名:
Use this to find the user name:
如果您不使用 -u/-p 提供凭据,它将尝试使用 Windows 身份验证;即运行它的人的 Windows 帐户。
我经常只是使用 Process Monitor 来查看正在使用什么帐户以及权限错误是什么。
If you don't provide credentials with -u/-p it will try to use windows authentication; i.e the windows account of whomever is running it.
I often just use Process Monitor to look at what account is being used and what the permission error is.
你说你在T-SQL脚本中使用SQLCMD,你不是说你正在使用SQLCMD来运行T-SQL脚本吗?您的脚本如何写入文本文件?它在 SQL 管理器中有效吗?我的猜测是运行 SQL Server 的用户帐户无权访问该位置。
You say you are using SQLCMD in a T-SQL script, don't you mean you are using SQLCMD to run a T-SQL script? How is your script writing a text file? Does it work in SQL Manager? My guess is that the user account SQL Server is running under doesn't have access to that location.
如果您通过
xp_cmdshell
调用不带用户和密码参数的 SQL 脚本,它将在 mssqlserver 服务的环境中运行,该服务受到很大的限制,并且在不更改安全参数的情况下,您将得到的大多是“访问是”拒绝'消息而不是脚本的结果。为了避免这种安全冲突情况,我在存储过程
create_sql_proc
中使用了以下技巧。我读取脚本文件的文本,并通过添加头和脚将其包装在过程中。现在我有一个脚本,从名为@procname
的 SQL 文件创建存储过程。如果您现在通过 EXEC @procname 运行此存储过程,它将在您的安全环境中运行,并提供从命令提示符运行它所获得的结果:
If you call an SQL script via
xp_cmdshell
without User and Password parameters it will run in the environment of the mssqlserver service, which is very much restricted, and without changing security parameters you will get mostly an 'Access is denied' message instead of the results of the script.To avoid this security conflict situation I use the following trick in my stored procedure
create_sql_proc
. I read the text of the script file, and wrap it in a procedure by adding a head and a foot to it. Now I have a script creating a stored procedure from the SQL-file called@procname
.If you let now run this stored procedure by
EXEC @procname
, it will run in your security environment, and delivers the result you would get by running it from a command prompt: