Hi,
we have a sqlserver 2000 db with a mixture of OS and NT authentication.How to write a query (or get info) on users.
The query should be able to tell me that these users are OS authentiacted and these users are db authenticated.
Thanks,
-copernicus
Hello experts. I inherited two SQL2000 SP4 servers. ServerA is configured with a Linked Server (ServerB). The problem is that liked_user which is the login configured "For a login not identified in the list above, connections will be made using this security context" is a sysadmin on ServerB. This means that any login from ServerA can do havoc on all databases in ServerB! There are many DBs and logins on both servers and no one can tell us which login is supposed to connect to the other server on which DB with what permission. Now, we want to trace which logins in ServerA use this Link so we can reconfigure this nasty setting.
I started the following SQL Profilers: On ServerA --> Filter TextData Like %ServerB%. On ServerB --> Filter LoginName Like liked_user AND HostName Like ServerA
Interesting enough, several entries were logged on the profiler i ran in ServerB which means that liked_user logged into ServerB from ServerA. Since liked_user is only used by the Link, i can safely conclude that the link was used by some login in ServerA. BUT, nothing was logged in the profiler i ran on ServerA!
Can anyone help me sort this one out? Thanks in advance,
I have 2 TableAuthorsID Name1 Clint2 Voke
BooksBookID ID BookName Price1 1 Book1 10 2 1 Boo21 12 3 2 Book3 6 4 1 Book4 13 5 1 Book5 2
Now I want to List All Authors and only show Most Expensive book Name of each Author.So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).I want to Create NEw Views Which Shows my required Results.
thanks,
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
I was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine.
I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.
This is what worked for me before:
EXEC sp_grantlogin '4800X2ASPNET'
USE MyDB
EXEC sp_grantdbaccess '4800X2ASPNET'
EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'
But for some reason now I am getting this error:
Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role '4800X2ASPNET' does not exist in this database.
I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working.
What weird is the old copy of this query I used had the local pc as the computer name. If I exchange the computer name from the remote pc name to the local 2003 Server machine name, the query works fine. Although that still doesn't let me add connections via Web Developer Express without error. It doesn't make sense to me anyhows, how could I have used the local machine's name and not the remote machine's name to get this to work previously. So confused.
Thank you in advance for help.
I was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine.I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.This is what worked for me before:EXEC sp_grantlogin '4800X2ASPNET'USE MyDBEXEC sp_grantdbaccess '4800X2ASPNET'EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'But for some reason now I am getting this error:Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49Windows NT user or group '4800X2ASPNET' not found. Check the name again.Msg 15401, Level 16, State 1, Line 1Windows NT user or group '4800X2ASPNET' not found. Check the name again.Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75User or role '4800X2ASPNET' does not exist in this database.I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working.What
weird is the old copy of this query I used had the local pc as the
computer name. If I exchange the computer name from the remote pc name
to the local 2003 Server machine name, the query works fine. Although
that still doesn't let me add connections via Web Developer Express
without error. It doesn't make sense to me anyhows, how could I have
used the local machine's name and not the remote machine's name to get
this to work previously. So confused.Thank you in advance for help.
I was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine.
I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.
This is what worked for me before:
EXEC sp_grantlogin '4800X2ASPNET'
USE MyDB
EXEC sp_grantdbaccess '4800X2ASPNET'
EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'
But for some reason now I am getting this error:
Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role '4800X2ASPNET' does not exist in this database.
I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working.
What weird is the old copy of this query I used had the local pc as the computer name. If I exchange the computer name from the remote pc name to the local 2003 Server machine name, the query works fine. Although that still doesn't let me add connections via Web Developer Express without error. It doesn't make sense to me anyhows, how could I have used the local machine's name and not the remote machine's name to get this to work previously. So confused.
Thank you in advance for help.
I have a 3rd party dashboard application that I can only use SQL authenticated logins to connect to the database.
I'm trying to create a query within the application that will directly access an excel file through a linked server.
As a test, I login to SSMS as the sql auth user to run the linked server query below but the following error is returned:
select *
from Corporate...[Sheet1$]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate".
When I login as a Windows auth user, I can successfully run the above query.
I noticed within the linked server's security definition that I cannot specify a windows auth user as the mapped Remote User or as the Remote login
I've tried creating a Credential object with the identity of the windows user and assign that object to the sql auth user but to no avail. I still get the same error
I am using SQL Server express so the option of an automated server agent job to import the excel file is not available.
Details:
SQL Server Express 2012
Office version: Excel 10
Provider: Microsoft.ACE.OLEDB.12.0
Hi,I have a web app, that runs fine, except for one particular section that uses a class called by an event in the code behind. The class resides as a dll in the bin folder. We had no problems during testing, when only one user was running this dll. Problems soon occurred when multiple users tried running it. Here's the error & stack: 06/02/2007 09:25:26 ==> cburns ==> There is already an open DataReader associated with this Command which must be closed first. at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at ESP.Validator.Data.DatabaseEvents.DatabaseEventManager.Read(IEventable eventObject, Int16 eventType, DateTime earliestDate, DateTime latestDate) in C:My PathValidatorValidator.NETDataDatabaseEventsDatabaseEventManager.cs:line 92 at ESP.Validator.Data.Translink.CATCard.GetDespatchDate() in C:My PathProjectsValidatorValidator.NETDataTranslinkCATCard.cs:line 94 at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.ReadCards() in C:My PathProjectsValidatorValidator.NETDataTranslinkExistingSchemeEntitlement.cs:line 215 at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.Read() in C:My PathProjectsValidatorValidator.NETDataTranslinkExistingSchemeEntitlement.cs:line 147 at ESP.Validator.Data.Translink.TranslinkApplicant.ReadEntitlements() in C:My PathProjectsValidatorValidator.NETDataTranslinkTranslinkApplicant.cs:line 369 at ESP.Validator.Data.Translink.TranslinkApplicant.Read() in C:My PathProjectsValidatorValidator.NETDataTranslinkTranslinkApplicant.cs:line 353 at ESP.Validator.Data.Translink.PrePrintedLetter.Read() in C:My PathProjectsValidatorValidator.NETDataTranslinkPrePrintedLetter.cs:line 282 at ESP.Validator.ValidationProcessor.Read(ValidationSubject subject) in C:My PathProjectsValidatorValidator.NETValidationProcessor.cs:line 82 at clear_applications_scan_applications.ProcessValidation() It seems the data reader is getting reused. We have ensured after each read the reader is closed. Though all users are using the same connection string. Could it be a connection pool problem, with the connection being overwritten during execution? Should i edit it according to the users logon?I am really at a loss for ideas, and I don't mind admitting I am a bit out of my depth with this one!! Any ideas/suggestions would be greatly appreciated. Thanks
View 1 Replies View RelatedHello,
I have this table:
Table1: ID, Num,Type, Amt
Can I create a view that return all rows in Table1 and Amt as zero if Type=’P’ (if not the value itself)
I have read about Views vs. SP's and I have question to which I could notreadily find the answer.The database I'm creating will only have 1 user from the start and up tothree maximum. Is there any reason that I shouldn't just use SP's foreverything or might there be an advantage to using views.This is not a heavy I/O project either and most of the day to day use willbe record manipulation and reference rather than new record entry.Thanks-Jake
View 2 Replies View RelatedHiWhat is the sql commnad to view active user on sql sever databasesThanks
View 1 Replies View RelatedIs there a utility that will allow me to see what users on my network areconnected/active on SQL server?Ryan
View 4 Replies View RelatedHello colleagues,
I am trying to create some reports in VS.NET for our Microsoft CRM3.0.
I am using a SQL query like this:
SELECT CRMAF_Account.Name, CRMAF_Account.primarycontactidName, CRMAF_Account.address1_line1, CRMAF_Account.address1_line2, CRMAF_Account.address1_city, ActivityPointer.scheduledstart, ActivityPointer.subject, ActivityPointer.description
FROM ActivityPointer, FilteredAccount CRMAF_Account INNER JOIN FilteredSystemUser FSU ON CRMAF_Account.ownerid = FSU.systemuserid
WHERE ActivityPointer.regardingobjectid = CRMAF_Account.accountID AND FSU.Domainname = SYSTEM_USER
ORDER BY Datum DESC
When I run this query, I get the desired result (I am the database dbo), but when any user tries doing the same, they get an error
An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Query execution failed for data set '[DatabaseName]'. (rsErrorExecutingCommand) Get Online Help
SELECT permission denied on object 'ActivityPointer', database '[DatabaseName]', owner 'dbo'.
I desperately need to know which user rights are missing and where are they missing - I do not suppose that the rights are expected to be assigned directly to the database tables
Any quick help would be highly appreciated. I would also appreciate any articles with examples how to use parameters, filters and subreports.
Thank you very much.
Marek Hlavac.
In SQL 2005 (we use Enterprise 64-bit SP2), the users cannot see thetext of the stored procedures, functions, etc.This is a production database, so I cannot give them rights to modifythem, but they need to be able to see what the procs are doing.I didn't have this problem in 2000 - how can I adjust the privilegesto allow them to view the contents of the Programmability objects?thanks for any insight!!Tracy
View 2 Replies View RelatedWe have a third party application and wish to create a report based upon a view.
The strange thing is logged in to the server as a SQL SYSAdmin account, we cannot view the data via the view. SQL Admin accounts are setup correctly and there is nothing different on this particular server. No errors are returned just a blank view with no records.
Could this be a permissions problem or orphaned schemas in that particular database? I thought SYSAdmin could view and do just about anything and the people who use this particular database would not have the know how on denying permission to the SYSAdmin role.
Thanks
N
I can create a string into a local "@" variable. It can include a date/time, and text for my timing testing.
I need to be able to send that string to the results window where table output goes, rather than to the message window where a PRINT statement goes.
How do I do that?
After upgrading my database from SQL2000 to SQL2005 I have noticed a change in behavior of sp_helprolemember.
In SQL2000 I could connect as 'user1' and use sp_helprolemember to find all users that belong to a certain role.
In SQL2005 sp_helprolemember seems to only show me the roles that connected user belongs to. For example, if I connect as 'user1' I only see the roles that 'user1' belongs to.
Any advice on how to duplicate the behavior from SQL2000?
Ideally, I'd like to have the reports use windows authentication, ie grab current user/password so long as user does not have to enter it manually. If that is not possible, then how do I make it so user does not have to enter this info manually? My theory is that I have IIS set up incorrectly for doing this, but I'm not familiar enough with either reporting services or IIS to find out.
View 1 Replies View RelatedHello, i have a problem regarding stored procedures and view server state.
I have an application with a lot of stored procedures, one of them checks data of the connected users.
In SQL 2000 i had no problem getting this information, but in SQL server 2005 i do.
my stored procedure looks like this:
ALTER PROCEDURE [dba].[applsp_GetConnectionInfo]
(
@DBName varchar(100)
)
WITH EXECUTE AS OWNER AS
BEGIN
SET NOCOUNT ON
DECLARE @sCollationMaster VARCHAR(128);
DECLARE @sSqlString VARCHAR(900);
-- Determine collation from master database because collation from master and ultimo database may differ
SELECT @sCollationMaster = CAST(databasepropertyex('master', 'Collation') AS VARCHAR);
SET @sSqlString =
'SELECT max(status) AS Status, max(isnull(SCISUSENAME, ''ULTIMOLOGIN'')) AS Login
, MAX(Rtrim(Rtrim(convert(varchar(255), nt_domain)) + nt_username)) AS NTUser
, max(Rtrim(hostname)) AS Host, MAX(Rtrim(program_name)) AS Program
FROM master.dbo.sysprocesses JOIN dba.SCONNECTIONINFO on SCISPID = CAST(spid AS VARCHAR)
AND ( SCISUSENAME = ISNULL(loginame, '''') COLLATE ' + @sCollationMaster + ' OR ISNULL(loginame, '''') = ''ULTIMOLOGIN'')
WHERE ...... AND DB_NAME(dbid) = ''' + @DBName + '''
GROUP BY hostprocess
ORDER BY Login
';
EXEC(@sSqlString);
END
I've granted view server state permissions to my user 'dba' which is the db_owner.
When i execute the query in the stored procedure seperatly as dba i get all the info i need, but when i execute the stored procedure i don't see anything.
I seem to have the same problem with sp_who2
Executing it gives me information about everyone but when i put in a stored procedure like this:
alter procedure test
with execute as owner as
begin
EXEC sp_who2
end
I just see information about myself
Hi,
I would like to restrict users to allow to view related reports only...
like i have 2 users..with 4 reports
1 user can see only two reports
2 user can see only another two reports
how can i do this by SSRS???
Can i do by using Configure item-level role definitions in seetings link..
or is thr any another way to do this?
Pls reply back
Regards,
Maruthi..
For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.
What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.
Developers need to view and modify jobs on some servers without being sysadmin. We've added the developers to the following MSDB roles:
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
Technically they should be able to view and modify jobs through Management Studio when being added only to SQLAgentOperatorRole. However they receive the following error when clicking on the Jobs folder regardless of the msdb role.
Execute Permission Denied on Object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Our original two SQL 2005 servers do not produce this message, but all other instances do. All servers, but one, are SP2 so the service pack is not an issue. Any thoughts?
Thanks, Dave
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedHello,
In SQL, suser_sname() gives me connection user name (I am using a single user to create all connections), I authenticate users through a login form aginst a SQL login table. Is there any way I can get a username authenticated in an SQL query?
Thanks,
Hi,
I have a program using VB and db-lib to connect to the SQL Server 7.0, it was working fine with
SQL Authentication, now when I changed to NT Authentication on the server, it always gave me
login failed(permission denied) error. Since it's a huge program, and I don't have enough
time to rewrite this in ADO, can anyone help? It did connect correctly through query window.
Thanks.
Heidi Zhang
I have a developer who created a DTS package using an NT authenticated ID. Now that we've migrated the SQL Server to another Domain in Active Directory, I am showing the following error for his jobs:
"The job failed. Unable to determine if the owner (DomainUSer) of job EmployeeDump has server access (reason: Could not obtain information about Windows NT group/user 'DomainUSer'. [SQLSTATE 42000] (Error 8198)).
When I went to check and see what the permissions problem might be, I noticed that all of my NT Authenticated ID are broken after migration - in other words, I can add the Active Directory NT ID's to the SQL Server instance, but I cannot give these ID's permissions to databases because it says they already exist??? I know if I have a sql login that is 'orphaned', I can use "sp_change_users_login" to fix it, but I think I read that this won't work for NT authenticated IDs.
IS there a way to fix this short of dropping ALL of my NT authenticated logins and recreating them????????
I have an ASP.NET 2 external application that is using and mix of the web services and tables/sps available from my 2005 SSRS.
I want to add an authenticated user from the external application. I don't see a web service that will do this. I have been through the SP's and can create roles, authenticated users, policies etc. But I think I need to update the XmlDescription field in the SecData table.
Do I need to build this XML string from scratch or is there a function / sp / web service that will do this. XML is a long ways from my strong suit.
Or am I just crazy and I should redirect my users to the Security tab!
Any help or advise would be appreciated.
Hi. New to ASP.NET and first time posting.
My web app connects to a SQL database - SQL authentication.
Users login to the web app through the login server control. Once authenticated, it is my understanding that the user name and password are stored on the client as a cookie.
How do you programmatically get this user info and use it for the userid and password parameters of the connection string?
Is there a better way to use the authenticated user info to access a SQL database?
Thanks
Hi, I have a table Projects. This table has ProjectID and Version as PK. The Version starts at 1 and everytime a project is changed, I save the project with the same ProjectID and increase the Version by 1.How can I create a query that get all Projects with the latest Version? Thx
View 1 Replies View RelatedI have a Properties table like thisPropertyID PropertyValue 1 Address 2 City 3 Stateetc.and a UserProfile table like thisUserID PropertyID PropertyValue1 1 123 Main Street1 2 Denveretc.How do I write a query that can populate a registration page with Address,City, State as labels and 123 Main Street, Denver, as TextBox text?
View 4 Replies View RelatedHi,I have included here my webform here.i need some assistance here with code.my webform contains two parts.the 1st part is office info and the 2nd part is client info.i also have two table named office_info and client_info.1st part is populated from the table office_info as soon as the office name is chosen from the dropdownlist.in my scenario,when user selects officename from dropdownlist,then textboxes correspondingto address and email gets populated by the related data from table office_info.2nd part is client info.here there are 3 textboxes(for name,age,address) to collect the data from the client using the form.these data gets posted to new row in table client_info as soon as user clicks on the save button.Now my actual question starts here.when user selects the option from the dropdonwlist the office info displays,now when he fills the client info part and clicks the save button,i want all the data to go to the table client_info in such a way that all the data fromthe client info part plus the id of the office also go along with it.eg: when user clicks the save button.i want data to get submitted in table client_info in this way.(id,name,age,address,off_row_id) (1,jack,25,US,1) here off_row_id is the id from the below table.my table office_info is like this (id,off_name,address,email) eg(1,xyz,ny,xyz@xyz.com) well can anyone tell me how to write query to do insert,edit,update,delete query in this case using c# and sql?here is the scenario <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> Office Info:<br /> <hr /> <br /> Office name: <asp:DropDownList ID="DropDownList1" runat="server" Width="63px"> <asp:ListItem>ABC</asp:ListItem> <asp:ListItem>XYZ</asp:ListItem> </asp:DropDownList><br /> <br /> Address: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <br /> email: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> <br /> <hr /> </div> Client info:<br /> <br /> name: <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /> <br /> age: <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br /> <br /> address:<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox><br /> <br /> <br /> <hr /> <asp:Button ID="Button1" runat="server" Text="save" /> <asp:Button ID="Button2" runat="server" Text="cancel" /> </form></body></html> thanks.jack.
View 8 Replies View Relatedhello everyone. i want to know how asp.net works with sql database. can i have a link to the article where i can perform from basic to advance sql query using asp.net(C#)? (in context of vwd 2005 and sql express ) thanks. jack.
View 1 Replies View Related