Transact SQL :: Execute As Credentials
May 20, 2015
I have created credentials:
CREATE CREDENTIAL myCredentials WITH IDENTITY = 'domain.user', SECRET = 'password';
Now, I would like to execute query with this credentials:
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
'SELECT col1 FROM [myServer].[myDb].[dbo].[myTable]'
) AS a;
How can I do that?There is no EXECUTE AS 'myCredentials'...Should I create some new login first, bind it to this credentials and then : EXECUTE AS 'myLogin'
View 8 Replies
ADVERTISEMENT
Apr 19, 2007
I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.
View 5 Replies
View Related
Aug 6, 2015
In temp table there rae data which start with 1 and 2.I want to select only those record which start with 1 Zone is a parameter to the Execute sql task in ssis package..I have created sample code to test when I am running my query I am not getting anything
create table #temp
( zoneid bigint
)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(200000000000000000)
insert into #temp values(200000000000000000)
[code]...
View 6 Replies
View Related
May 20, 2015
I have a function like below
CREATE FUNCTION [dbo].[UDF_GetCode]
(
@TableName NVARCHAR(50)
)
RETURNS NVARCHAR(50)
[code]...
This function is called in insert statement like below. exec sp_executesql N'INSERT INTO Table ([Code], [Name]) VALUES (dbo.UDF_ GetGlobal ConfigCode (''TableName''), @Name)'I am getting following error.Only functions and some extended stored procedures can be executed from within a function.
View 3 Replies
View Related
May 31, 2005
Basically, I have a table with a column that stores mathematical formulas in string format. When my UDF is executed, it needs to select an appropriate formula from this table and evaluate it using values that are stored in local variables.
Look at the example below:
Suppose I have a string named @vcFormula that contains the following:"@dVar1 + @dVar2 / @dVar2"Now suppose I have a variable named @dVar1 that contains a value of 1.0, and variable @dVar2 contains a value of 2.5. I can use the REPLACE function to change my original string to look like this:"1.0 + 2.5 / 2.5"
Now I want to execute this string and find the numeric result, placing it in a variable named @dResult. The following works, but presents a problem:CREATE TABLE #Result (dResult decimal(20, 10))INSERT #Result EXEC('SELECT ' + @vcFormula)SELECT @dResult = dResult FROM #ResultThe problem with using this method comes from the fact that I need to be able to evaluate @vcFormula from within a user-defined function, but temporary tables are not allowed inside UDF's.
So I attempted to change the temporary table above into an instance of the TABLE data type. This didn't work either because EXEC cannot be used to populate instances of the TABLE data type. Then I came up with the bright idea to put the code above in a SP and call the SP from the UDF, but of course UDF's are not allowed to call SP's. Specifically, is there any way to execute a command/formula that is contained within a string other than by using EXEC?
View 10 Replies
View Related
Jun 19, 2015
I want to include GO statements to execute a large stored procedure in batches, how can I do that?
View 9 Replies
View Related
Jun 24, 2015
Suppose you have a simple DML "execute as..." trigger that looks like this:
CREATE TRIGGER dbo.myTrigger
ON dbo.myTable
WITH Execute As Owner
AFTER INSERT
AS
[code]...
which runs as "owner" so that the user inserting into "myTable" won't have the privs to edit the audit table.How does one capture the identity of the user whose insert statement caused the trigger to fire? I've tried "current_user" and "system_user" without success when "execute as ..." is used: they each return a value not related to the identity of the user running the original insert.without an "execute as" clause, what prevents a user from adding to or editing the audit table directly?
View 6 Replies
View Related
Aug 19, 2015
I am getting an exception. And i don't know whether this exception is from the C# coding or SQL SERVER."Cannot roll back InsertRxs. No transaction or savepoint of that name was found.Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back."
View 2 Replies
View Related
Jul 9, 2015
I am trying to fetch records from excel sheet using Select Query but I am getting the error message saying
"Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"."
Here is my Query,
sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go
reconfigure
SELECT *
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:M2MworkedworkedBOS.xlsx;HDR=YES', 'select * from [Sheet1$]') AS A;
View 5 Replies
View Related
Apr 21, 2006
Hello....
I have a stored procedure which needs to access the files on a remote server... a server which does not belong to the same domain as the SQL Server 2005 machine.
I have an account which has access to the remote server. If the account name is "NewAccountAccountName", how can I execute the stored procedure using that account? Is this possible?
Thanks,
Forch
View 3 Replies
View Related
May 1, 2008
Hi
Does anyone know the correct syntax to run a xp_cmdshell command using different sql server credentials?
Thanks
View 5 Replies
View Related
Jul 27, 2007
On to the next understanding topic. I understand the concept of credentials and why they are needed with the isolation of machine admin authority and running a SQL Server instance. I had a question on the purpose of the system credentials.
##MS_SQLResourceSigningCertificate## is the credential utilized by the instance itself to gain access to Windows for such things as reading and writing to database files, network stacks, writing to the error logs/event logs, etc. ##MS_AgentSigningCertificate## is the credential that is used by SQL Server Agent for accessing any necessary Windows resources.
##MS_SQLReplicationSigningCertificate## is used only by the replication engine to allow it to access Windows resources.
##MS_SQLAuthenticatorCertificate## is used during the authentication process for SQL Logins to provide authenticator services and is also used to access the Windows security API.
Each of the credentials is a certificate which is loaded into the instance and you can find the corresponding certificate within the data folder for the instance. I'm assuming that a mechanism is already in place to prevent someone from mucking with the certificates on the OS to gain access back into the SQL Server by comparing the signature already stored within the instance with the signature of the certificate on the OS. (This is also part of the larger discussion of certificate use within SQL Server itself and explains why you can't change the service account/password using the Service Control Applet.)
Did I get that correct, am I way off base, or is this one of those "no comment" items?
View 5 Replies
View Related
Jun 17, 2004
I'm trying to create a login screen that will verify a user's username and password from within a SQL table. If the credentials are found in the table, they should be sent to a Main Menu screen. If the credentials are not found, I will throw a message on the screen. Does anyone have code to accomplish this? I am at a total standstill.
Thanks,
BoydM
View 5 Replies
View Related
May 8, 2008
I'm using SQL Server 2000 Reporting Services.
I'm trying to create a subscription to automatically generate a report daily and put a resulting pdf into a folder on the drive. Users will then receive an email notification with a link to the report so they can just click on it and see the report.
When creating the subscription, I am prompted for a set of "credentials used to access the file share" (user name and password). But I don't want my users to have to supply any credentials, I just want them to click on the link and get the report. Is there a way to disable the requirement for the credentials?
Thank you.
View 1 Replies
View Related
Dec 14, 2006
Get this when selecting parameter value in Report Manager
Report is working fine in preview
Up to me, it has nothing to do with credentials
I have some analysis server reports.
I have a single value report parameter, the report runs fine with the default value, when I select another value than the default one, I get above message immediately.
I got the error since I rebuild the report with SP2 CTP.
Strange thing is that the other single value parameter of the report works fine (year)
And that I get the error also with other reports, and on the same dimension (Organisation)
And that I do not have the error when I change the parameter to multi-value (but as the report is not written for multiple values...)
MDX queries are as follows:
SELECT NON EMPTY { KPIValue("KPINewBiz"), KPIGoal("KPINewBiz"), KPIStatus("KPINewBiz"), KPITrend("KPINewBiz") } ON COLUMNS,
NONEMPTY
([Reps].[Organisation].[Organisation].ALLMEMBERS
*[Reps].[Rep].[Rep].ALLMEMBERS
*[Date].[Year].[Year].ALLMEMBERS
*[Date].[Month].[Month].ALLMEMBERS
,[Measures].[Gross Sales]
)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@ByYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ByOrganisation, CONSTRAINED) ) ON COLUMNS FROM [Sales])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
WITH MEMBER [Measures].[ParameterCaption] AS '[Reps].[Organisation].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Reps].[Organisation].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Reps].[Organisation].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
NONEMPTY([Reps].[Organisation].[Organisation].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Year].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Year].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Year].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , NONEMPTY([Date].[Year].[Year].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]
Discovered something else that is very strange, you change the value of the parameter, you get the error, you hit the back button, and you can run the report with the new parameter
Did one more test, changed the order of the parameters, is now going wrong on the other parameter, Year.
So, it is always on the first parameter.
Correction, I have put a multi-value parameter as first, I still have the problem, it is always on the first single value parameter
I have the same problem.
Up to me, it has nothing to do with credentials
I have some analysis server reports.
I have a single value report parameter, the report runs fine with the default value, when I select another value than the default one, I get above message immediately.
I got the error since I rebuild the report with SP2 CTP.
Strange thing is that the other single value parameter of the report works fine (year)
And that I get the error also with other reports, and on the same dimension (Organisation)
And that I do not have the error when I change the parameter to multi-value (but as the report is not written for multiple values...)
MDX queries are as follows:
SELECT NON EMPTY { KPIValue("KPINewBiz"), KPIGoal("KPINewBiz"), KPIStatus("KPINewBiz"), KPITrend("KPINewBiz") } ON COLUMNS,
NONEMPTY
([Reps].[Organisation].[Organisation].ALLMEMBERS
*[Reps].[Rep].[Rep].ALLMEMBERS
*[Date].[Year].[Year].ALLMEMBERS
*[Date].[Month].[Month].ALLMEMBERS
,[Measures].[Gross Sales]
)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@ByYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ByOrganisation, CONSTRAINED) ) ON COLUMNS FROM [Sales])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
WITH MEMBER [Measures].[ParameterCaption] AS '[Reps].[Organisation].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Reps].[Organisation].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Reps].[Organisation].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
NONEMPTY([Reps].[Organisation].[Organisation].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Year].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Year].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Year].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , NONEMPTY([Date].[Year].[Year].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]
Discovered something else that is very strange, you change the value of the parameter, you get the error, you hit the back button, and you can run the report with the new parameter
View 3 Replies
View Related
Jul 5, 2006
I have created several reports in SQL Server 2005 reporting services and I am viewing those reports in my C# application using a report viewer control. I want to pass the credentials for each report through my applications. I do not want to use any web service.
Is it possible???
Any help will be appreciated!!!
TIA
View 19 Replies
View Related
Jul 31, 2006
Hi,
because my package does not run with SQL-Server-Agent, but without problems if started by "hand", I created a new credential which contains the information needed for the package. I did this as described on: http://msdn2.microsoft.com/en-us/library/ms190703.aspx .
After that i tried to create an proxy account, but when I chose the created credential, Management Studio says "Der Proxy "[name_of_credential] ist kein gültiger Windows-Benutzer(Microsoft SQL Server, Fehler: 14529)". This means something like: "This Proxy is not a valid windows-user. Error: 14529".
Any hints how to use a credential that is not a windows-user?
Regards,
Jan Wagner
View 7 Replies
View Related
Jan 23, 2007
Hi all,
When I create a new report subscription in report manager, I got this error
Credentials used to run this report are not stored
any idea?
Thanks,
Jone
View 17 Replies
View Related
Sep 12, 2007
Hi,
I'd like to programme the execution of a report through the execution property option. However, when I try to do it a message error appears related to credentials of the datasource.
The present action is impossible to complete, because the credentials of the user datasource needed to execute this report are not stored on report server database.
I've tried all possible combinations.
Any idea?
Thanks.
View 2 Replies
View Related
Apr 17, 2007
I created a report in SSRS, and deployed it. I have created security where when users run the on demand report, it will prompt them for a username and password. I went to the properties of the report then Execution, and tried to set up report execution snapshot and got an error 'Credentials used to run this report are not stored.' I get the same error if I go to History and try to select any of the options there.
View 11 Replies
View Related
Apr 21, 2006
Hi everyone....
I have made a CLR stored procedure which goes to a shared folder on a remote computer. I am having permission problems when executing the stored procedure. (In case anyone is wondering, I am using the .NET SYSTEM.IO class)
I have done the following:
CREATE CREDENTIAL myuser
WITH IDENTITY = 'mydomainmyuser', SECRET = 'some56*Z';
CREATE LOGIN sam WITH PASSWORD = 'meowPw!a3';
ALTER LOGIN sam WITH CREDENTIAL = myuser;
...(other SQL so that SAM can execute the SP)
Now, I login to SQL Server using the new login, and when I try to execute the CLR stored procedure, I get "unknown username or bad password.".
I know 100% for sure that the account in the "MYUSER" credential has access. Is there something else I need to do?
View 4 Replies
View Related
Jun 18, 2007
Hello friends,
In my web application I have saved my connection string into web.config file. Now I have to send this web application to the client in the form of web set up. The issue is that on the client side, user credentials for the database will be different. So can we do some setings that the web application whenever will run should ask for the server name, username & Password. If this can be done then the application can be run anywhere considering all compatibilities.
Please let me know how this can be done.
Thanks & RegardsGirish Nehte
View 2 Replies
View Related
Jul 25, 2004
I have developed an asp.net, vb.net web app and have integrated reporting services. Right now I am using url access but am not to crazy about showing the parameters in the querystring. I am using a dynamic stored procedure to get client data. I would like to use the rs web service to programatically set the parameters and send them invisably to the web service. Since the web app is not using windows authentication I would like to pass the parameters using a hard coded account that has been set up with browse privilages in the report manager. Is it possible to hard code the credentials that are sent to the reporting service instead of the System.Net.CredentialsCache.DefaultCredentials? The report returns a bar graph which I am hoping to display on the page. Reading some posts about some problems returning images I might be posting some more questions.
Are there any good walkthroughs or code examples using reporting services web service other than MSDN's limited supply?
Thank you for your time!
--
- preisi
View 2 Replies
View Related
Oct 22, 2007
In the SSMS, is it possible to allow a user to log into the DB engine using alternate Domain credentials. There are fields that allow you to log in with SQL credentials but I don't see a way to do it with Doman credentials. It's always passthrough authentication.
View 2 Replies
View Related
Apr 28, 2008
Is it possible for me to login using windows login information that is separate from my current windows login? That is to say, can I login to a SQL server using windows athentication but actually specify the login and domain information? I woul like to login to a SQL server from a computer that is not part of the domain using my windows credentials and not a SQL user name.
View 5 Replies
View Related
Jul 27, 2006
I using MS SQLServer as a secure method of setting up system tasks andprocesses for automated running. The intent is that all logins (idsand passwords) are in a secure database table and are not sittingaround in batch files on the server.Some of the tasks make use of network authentication, and this is wherethe problem arises.If I execute the command line from within SQLServer (via xp_cmdshell),I get an error that says that the userid is missing. If I execute thatsame command vid the CMD window, it works.It appears that the shell that xp_cmdshell kicks off does not inheritthe domain authentication.Aside from questions on why I am doing things this way as opposed tousing the windows scheduler or other tools, what do I need to do tomake sure that my domain credentials are passed?
View 1 Replies
View Related
Sep 11, 2007
On my SSIS package I have 2 connection managers, both are connecting to an Oracle db. I enter in the ID and Password then click 'test connection' I'm able to connect to the database fine. I then go to a data flow of one of my control flow task. Open up my OleDb Source and click preview. The SQL query executes with no issues. I can see the data from my connection manager source.
I then go and run the package and I get this error message:
[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OracleConnection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
I've been racking my brain on this for a few days and still no luck on getting this to work.
View 8 Replies
View Related
Apr 22, 2008
For credentials, I have a Shared Data Source that "uses a specific user name and password".
When I open the data source's RDS file, I observed that the credentials are not stored in the RDS file.
Where are the credentials for the data sources stored?
It has to be stored somewhere. When I close and reopen the solution, the credentials for the data source are still intact.
View 1 Replies
View Related
Sep 18, 2007
Hello,
When trying to subscribe to an report via email I get to following error
The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)
Can anybody help me with this issue.
Greetings
Vinnie
View 15 Replies
View Related
Nov 4, 2007
I am trying to open the reportbuilder from within a web application so that I can pass the credentials. I would like to be able to have the reportbuilder open within the app. Is this possible.
I'm trying to avoid keeping the report builder credentials and the app's credentials both up to date.
Thanks.
View 2 Replies
View Related
Mar 4, 2008
Hello. I have set up an SQL Endpoint using NTLM authentication. When I try to retrieve the WSDL file I am prompted for credentials. I put in username/password (this user has Connect access to the Endpoint) but its not accepted. The dialog reappears. The SQL error log shows an HTTP authentication failed error. I've triple checked that the username/password is correct.
Now here's the strange part: the same endpoint (exact same code for setting up endpoint, permissions, user, etc.) works just fine on another box (username/password is accepted and WSDL is shown). This leads me to believe that the problem isn't with SQL or the endpoint, but someplace else. I really don't know how to debug this one.
View 16 Replies
View Related
Mar 7, 2007
Hi,
Is it possible to make jobs visible to people other than the job owner?
Can jobs run under non-sysadmin account?
Thanks,
Gulden
View 3 Replies
View Related