Which user account is running SQLCMD in T-SQL Script without -U and -P option?

I am using s开发者_运维问答qlcmd 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?

---

Use this to find the user name:

PRINT Suser_Sname();

---

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.

---

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.

---

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:

CREATE   PROCEDURE create_sql_proc(@procName sysname, @sqlfile sysname) AS
BEGIN
    DECLARE @crlf           nvarchar(2)   = char(10)+char(13)
    DECLARE @scriptText     nvarchar(max)
    DECLARE @cmd            nvarchar(max) 
            = N'SET @text = (SELECT * FROM openrowset(BULK '''+@sqlFile+''', SINGLE_CLOB) as script)'

    EXEC sp_executesql @cmd , N'@text nvarchar(max) output', @text = @scriptText OUTPUT
    DECLARE @ProcHead       nvarchar(max) = N'CREATE or ALTER PROCEDURE '+@procName+ ' AS '+@crlf+'BEGIN'+@crlf
    DECLARE @ProcTail       nvarchar(max) = @crlf + N'END '

    SET @scriptText = @ProcHead + @scriptText + @ProcTail
    -- create TestGen stored procedure --
    EXEC sys.sp_executesql @scriptText
END