|
I'm in the process of writing a stored procedure that copies information from one database to another. One of the pre-copy checks at the start of the stored procedure is to make sure that the logged in user has the right to create the tables and copy the data in the destination database.
How can I tell if the logged in user can access another database?
Also, I don't want it to raise an error while doing this. I currently have the following:
CREATE TABLE #temp (name sysname);
SET @stmt = 'USE ['+@destination_database+']; SELECT name from sysusers WHERE name='''+USER+'''';
INSERT #temp EXEC(@stmt);
My idea was to see if the #temp table contained any records, but obviously if the user has no permission to use the database this isn't going to work.
Any ideas?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
From BOL:
"SCHEMATA
Contains one row for each database that has permissions for the current user. The INFORMATION_SCHEMA.SCHEMATA view is based on the sysdatabases, sysconfigures, and syscharsets system tables.
To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA view_name."
The following SQL query should give you a list of all databases on the server that the current user has permissions in. Use a WHERE clause and possibly a COUNT(*) aggregate if you just want to know if the user has access to one particular database.
SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
That doesn't work. Actually I find that the INFORMATION_SCHEMA is quite eccentric in what it returns (see end note).
When I run the statement you gave, SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA, I get a list of all the databases. This is not useful as it still list the database that I cannot access.
If I SELECT * FROM INFORMATION_SCHEMA.SCHEMATA it tells me that the schema owner is the same as the user logged in for a database that that user cannot access.
If I then issue a USE [p1_p2_destination_database] command I get the following:
916: Server user 'fails_2' is not a valid user in database 'p1_p2_destination_database'.
'fails_2' is the user that is not supposed to be able to access that particular database (These are part of the tests I am running to ensure the stored procedure that I am writing behaves correctly for a given situation). All 'fails_2' has in the server is access in the 'master' and 'p1_p2_source_database', it also owns a table in 'p1_p2_source_database'. It is correct that it issues an error if I try to USE the 'p1_p2_destination_database' but I want to trap that situation before it arises.
I don't know if this helps explain the situation more clearly.
As an aside the code for INFORMATION_SCHEMATA is:
--Identifies schmata owned by current users, databases current users has permissions in
create view INFORMATION_SCHEMA.SCHEMATA
as
select
db.name as CATALOG_NAME
,USER_NAME() as SCHEMA_NAME
,USER_NAME() as SCHEMA_OWNER
,convert(sysname, NULL) as DEFAULT_CHARACTER_SET_CATALOG
,convert(sysname, NULL) as DEFAULT_CHARACTER_SET_SCHEMA
,a_cha.name as DEFAULT_CHARACTER_SET_NAME
FROM
master.dbo.sysdatabases db,
master.dbo.syscharsets a_cha --charset/1001, not sortorder.
WHERE
a_cha.type = 1001 --- type is charset
AND a_cha.id = convert(tinyint, DatabasePropertyEx(db.name, 'sqlcharset'))
I don't see anywhere in this where filters anything by permission. If anything just returneing USER_NAME() for the SCHEMA_NAME and SCHEMA_OWNER is just pointless.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Hi,
I am writing an VC++ application which access SQL server/Oracle/MS-Access/excel etc using ODBC drivers.
I have some basic knowledge of ODBC API's like SQLOpen, SQLGetSchema etc but no clue of how to implement and get the data from the above mentioned databases into my VC++ application.
Can anybody help me out sending some links, code examples etc.
Thanks,
Prashant
|
|
|
|
|
I have an xml datastream and would like to know the syntax to map the contents to a specific excel worksheet and xml map. This is to automate 30+ complex reports in excel (must be excel - and no iis available to make my job much easier!). I anyone has a better solution I welcome it.
This is my syntax so far:
Dim objConn As ADODB.Connection
Dim objCMD As ADODB.Command
Dim objResultStream As ADODB.Stream
Dim xmpCustomMap As XmlMap
On Error GoTo Handle_Error
Set xmpCustomMap = ActiveWorkbook.XmlMaps("triangle_Map")
Set objConn = New ADODB.Connection
objConn.Open "Provider=SQLOLEDB;server=mydb;Database=Development;" & _
"Integrated Security=SSPI"
Set objCMD = New ADODB.Command
Set objCMD.ActiveConnection = objConn
't-SQL DIALECT
objCMD.Dialect = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
objCMD.CommandText = "Exec dbo.proc_xml 1394"
objCMD.CommandType = adCmdText
Set objResultStream = New ADODB.Stream
objResultStream.Open
objCMD.Properties("Output stream") = objResultStream
objCMD.Properties("XML Root") = "Root"
objCMD.Execute , , adExecuteStream
Debug.Print objResultStream.ReadText(adReadAll)
|
|
|
|
|
I want to create a secure Access-Database from C#.
I want to create a Database and a Systemdatabase - I suppose this works only in the same time.
Why does the following code not work?
private void button1_Click(object sender, System.EventArgs e)<br />
{<br />
ADOX.Catalog catalog = new ADOX.Catalog();<br />
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\\new.mdb;Jet OLEDB:System Database=e:\\new.mdw;Jet OLEDB:Create System Database=true");<br />
}
- it creates the e:\new.mdb but fails to create the e:\new.mdw.
I use Visual Studio 2003 and I have tried this
- on WXP with installed Access2003 and
- on W2K with installed Access97 and Access2000
All with the same result.  
|
|
|
|
|
I met a trouble to create a customized report against MS-Server OLAP in crystal report. The report what I want could not be described clearly here. If anyone of you is interested in it, I'd like to send you a sample of it by your email.
BTW, anyone knows how to attach a file on this board?
Thanks,
Yanh100
|
|
|
|
|
'Cause right now I'm getting this from compiler:
<br />
c:\...\msado15.tlh(26) : error C2371: 'Properties' : redefinition; different basic types<br />
c:\mvs\microsoft sdk\include\adoint.h(242) : see declaration of 'Properties'<br />
c:\...\msado15.tlh(28) : error C2371: 'Property' : redefinition; different basic types<br />
c:\mvs\microsoft sdk\include\adoint.h(238) : see declaration of 'Property'<br />
c:\...\msado15.tlh(30) : error C2371: 'Error' : redefinition; different basic types<br />
c:\mvs\microsoft sdk\include\adoint.h(66) : see declaration of 'Error'<br />
c:\...\msado15.tlh(32) : error C2371: 'Errors' : redefinition; different basic types<br />
c:\mvs\microsoft sdk\include\adoint.h(70) : see declaration of 'Errors'<br />
c:\...\msado15.tlh(36) : error C2371: '_Connection' : redefinition; different basic types<br />
c:\mvs\microsoft sdk\include\adoint.h(106) : see declaration of '_Connection'<br />
... and so on for everything there is in msado15.tlh.
Help!
Thanks in advance!
|
|
|
|
|
Im currently writing a program that uses ADO to connect to a database. When the program is compiled in release mode then, run it, the connection is OK. But when I try to use the exe file of the program alone, the connection failed. what seems to be the reason?
pls help!
tnx in advance!
|
|
|
|
|
A couple of questions:
1) What kind of connection object are you using? SqlConnection? OleDbConnection? is it Access? Oracle? MySql?
2) "the connection fails" seems to me to imply that it's throwing an exception, yes? If so, can you send along what the error message is? If it's not an exception, can you describe what's happening?
Bill
|
|
|
|
|
1)Im using Access database. This is the code.
HRESULT CDBConnection::dbConnectionOpen()
{
HRESULT hr;
try
{
hr = connection_.CreateInstance( __uuidof( Connection ) );
}
catch(_com_error &errMessage)
{
TRACE(errMessage.Description());
TRACE(errMessage.ErrorMessage());
}
if (SUCCEEDED(hr))
{
// Open the database connection using the UDL file
try
{
hr = connection_->Open(
_bstr_t(LOGDATABASE),
_bstr_t(L""),
_bstr_t(L""),
adModeUnknown);
}
catch(_com_error &errMessage)
{
MessageBox(NULL, errMessage.Description(), "Test", MB_OK);
MessageBox(NULL, errMessage.ErrorMessage(), "Test", MB_OK);
TRACE(errMessage.Description());
TRACE(errMessage.ErrorMessage());
return -1;
}
}
return hr;
}
LOGDATABASE is a string constant for my Access Database- "MyDatabase.udl"
Included in my database are tables linked to another Access database.
2) The error message is "Unknown error 0x80030002".
|
|
|
|
|
Thanks for the info...
At this point I think I have to admit to being in unfamilar territory. My c++ experience is just about 0... (unless you count gnu cpp back in the day)
A quick Google search for '0x80030002' reveals this url: http://support.microsoft.com/default.aspx?scid=kb;en-us;196776[^] which suggest you use ErrLook.exe or look in a winerr.h.
Sorry if this is dumb advice or you've already tried it...like I said, I am out of my depth here...
Bill
|
|
|
|
|
I'm programming a little server application in MFC 7.1 + OLEDB that read from a database. I'm using a SQL Server DB (extactly the MSDE version) and all works good, but now i need to store the data in an Access 2000 file, so i've changed the connection parameters like this:
<br />
<br />
CDBPropSet dbinit(DBPROPSET_DBINIT);<br />
HRESULT hr;<br />
<br />
dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("Admin"));<br />
dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR(""));<br />
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("C:\\Gestu.mdb"));<br />
dbinit.AddProperty(DBPROP_INIT_MODE, (long)DB_MODE_READWRITE);<br />
dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)DBPROMPT_NOPROMPT);<br />
dbinit.AddProperty(DBPROP_INIT_PROVIDERSTRING, OLESTR(""));<br />
dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);<br />
<br />
hr = m_DataSource.Open(_T("Microsoft.Jet.OLEDB.4.0"), &dbinit);<br />
if (FAILED(hr))<br />
return false;<br />
<br />
hr = m_session.Open(m_DataSource);<br />
if (FAILED(hr))<br />
return false;<br />
<br />
return true;<br />
<br />
}<br />
<br />
the program logins correctly. but when i try to execute a SELECT statement or other accessor operations and execute them it have an error DB_E_ERRORSINCOMMAND.
a sample operation is:
<br />
CCommand<CDynamicAccessor> DynamicAccessor;<br />
<br />
CDBPropSet propset(DBPROPSET_ROWSET);<br />
propset.AddProperty(DBPROP_CANFETCHBACKWARDS, true);<br />
propset.AddProperty(DBPROP_IRowsetScroll, true);<br />
propset.AddProperty(DBPROP_IRowsetChange, true);<br />
propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE);<br />
<br />
DBROWCOUNT rows;<br />
CString sql; sql.Format("SELECT * FROM Users WHERE nickname = '%s';", strNickname);<br />
HRESULT hr;<br />
hr = DynamicAccessor.Open(m_session, sql, &propset, &rows);<br />
if (FAILED(hr))<br />
return false;<br />
The same code here works perfectly with the SQL Server connection. Is there some parameters to modify?
Thanks
|
|
|
|
|
I am trying to connect to SQL serevr using 'Windows only
Authentication'. Below is my code.
Dim strConn As String = _
"data source=localhost; " & _
"initial catalog=northwind; " & _
"integrated security=true;"
Dim conn As New SqlConnection(strConn)
Where as If I change the code to use 'Mixedmode
authentication' i.e.
Dim strConn As String = _
"data source=localhost; " & _
"initial catalog=northwind; " & _
"user id=sa;password=;"
Dim conn As New SqlConnection(strConn)
This one is working.
Do I need to change any options in SQL server to
work in windows-only authentication?
Please help!
Thanks!
|
|
|
|
|
SQL Server has two modes: Windows Authentication only and Mixed Mode, where both Windows Authentication and SQL Server Authentication are supported.
You need to ensure a number of things. Firstly, that the server computer trusts the client computer - they must either be in the same domain or in domains which have a trust relationship. Secondly, that the program is running under a domain account. And thirdly, that the user account, or a security group of which it's a member, has permission to log on to the database server.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
The following query
<br />
SQL.Format("SELECT DISTINCT s.Description FROM ((((SK_SKILLSINAGES AS sia LEFT JOIN SK_SKILLS AS s ON (sia.SkillCode = s.SkillCode) OR (sia.SkillCode = s.ParentSkillCode AND sia.SubSkillCode = s.SkillCode)) INNER JOIN SK_CATEGORY AS c ON sia.CategoryCode = c.CategoryCode) INNER JOIN SK_CATEGORYGROUPS AS cg ON (sia.GroupCode = cg.GroupCode) AND (cg.GroupCode = c.GroupCode)) INNER JOIN AG_AGESINERAS AS ae ON sia.AgeCode = ae.AgeCode) INNER JOIN AG_ERA AS e ON ae.EraCode = e.EraCode WHERE ( (sia.SubSkillCode Is Null AND s.ParentSkillCode Is Null) OR (sia.SubSkillCode Is Not Null AND s.ParentSkillCode Is Not Null) AND sia.CategoryCode = c.CategoryCode AND sia.GroupCode = cg.GroupCode AND sia.AgeCode = ae.AgeCode) AND ae.Description LIKE 'Middle Ages' AND e.Description LIKE 'Rolemaster' AND s.SkillCode LIKE 'SWORD';"<br />
runs nice and fast however if at the end of the query I add
<br />
OR s.SkillCode LIKE 'LONGSWORD'<br />
The query runs slow. In fact the first query will run instantaneously. the second query takes a few seconds, around 10 seconds. Does anyone know why simply adding an OR statement inflates the time to query a database? Any way of optimizing that somehow?
Hopefully there is enough info here.
Thanks!
Let me add, the first query will return one field while the second query will return 2 fields. Also if you remove
<br />
AND s.SkillCode LIKE 'SWORD'<br />
at the end of the query, the query takes awhile to run again. Again, it seems return 1 record will mean a fast load, but anything larger than that the query slows down considerably. 
|
|
|
|
|
My first question to you would be to ask why you're using LIKE with no wildcards?
I think you'd find slightly better performance with AND ae.Description = 'Middle Ages' AND e.Description = 'Rolemaster' AND s.SkillCode = 'SWORD'
Now that we're doing this, instead of adding your OR statement, try this: AND ae.Description = 'Middle Ages' AND e.Description = 'Rolemaster' AND s.SkillCode IN ('SWORD', 'LONGSWORD')
This may or may not help, mind you. To really get into the nuts and bolts of it I'd need a list of indexes, statistics, and/or an execution plan.
Oh, and what DB platform are you using, anyway?
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
currently Jet Database. Your suggestion with the IN seems to help. I never knew I could do that. Also I never thought about using wildcards for my LIKE statements. However I fail to see the difference between your first suggestion (not the one where I should use IN) and my original suggestion.
Thanks!
|
|
|
|
|
Like is a pattern matcher. It can never produce better results than =. Your statement, without wildcard, should be optimized to an = but, you are relying upon the query system being smart enough. It is easier for the programmer to be smarter.
SELECT *
FROM Weapons
WHERE Name LIKE '*SWORD*'
This would return all weapons with the letter pattern sword in them.
|
|
|
|
|
Like Michael said. The LIKE keyword is specifically used for pattern matching, and even if you don't use a pattern, the query builder will still probably build the query using the pattern matching algorithm, which is always going to be less efficient than a simple equality algorithm. Depending on the data you're matching, the difference could be inconsequential, or it could be dramatic.
Plus, if you have the field indexed, the equality comparison (or >, <, >=, <=, etc) will use the index much more efficiently. The LIKE operator will do a full index scan, to look for all fields that might match the pattern. The equality operator can use the index as it's intended, by starting with only the rows where that field begins with 'S'. Some database engines may automatically do this with LIKE statements that don't begin with a wildcard. Others may not.
AND, the real reason I brought it up. The IN keyword replaces multiple equality checks. I.e. field IN ('Val1', 'Val2') is translated by the query builder into (field = 'Val1' OR field = 'Val2'). Note that the only difference in this and your original query is the parentheses, which can make all the difference in the world if used properly (or not, as the case may be.)
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
hi,
i got an Access db and i want to make an SQL query
that retrieve a date field and but with this format (mm/yyyy).
how do i do it in SQL (of access db)?
anyone?
|
|
|
|
|
That's one i can answer 
select Format(<datefield>, "mm/yyyy") as <alias> from
replace <datefield>,<alias> and with what u need in your query 
|
|
|
|
|
I'm displaying details on my website, which is taken from my MS access database. Which is simple enough. The details are shown in a table, which you can most probably tell from the extract of code.
The problem is the "Address" appear on the site, but the "Details" do not.
The difference is that on the database, the Address data type is text, and the Details is data type memo.
I believe the problem lie with this difference in data type, and the
If RS("Details") <> "" then.
Hopefully someone can show me the light!
<% If RS("Address") <> "" then
Response.Write("<td height=""20"" valign=""top""><p>")
Response.Write RS("Address") 'This does appear
Response.Write("</p></td>")
End if
%>
<% If RS("Details") <> "" then
Response.Write("<td height=""20"" valign=""top""><p>")
Response.Write RS("Details") 'This does not appear
Response.Write("</p></td>")
End if
%>
---
Peter M
|
|
|
|
|
I've found out you cannot do comparisons with memo fields. You need to put it into a string to compare. Thanks to david wulff.
---
Peter M
|
|
|
|
|
Hi Guys,
I have a three process - database scenario
- The first process inserts data continuously on a 24 hr (TMPDATA). basis, for every 1 second my program insert 6-10 records. It is very fast I'm using a socket connection to get the data.
- 2nd process is to transfer from TMPDATA to ORIGDATA every 5 mins. But Here I do the Locking Isolation level SERIALIZABLE so that the first process will not interfer with this process becuase after the transfer I truncate the TMPDATA table so as to free some space. (24 hrs. continuous)
DTS PROCESS:
begin transaction<br />
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<br />
insert into TMPDATA select * from ORIGDATA<br />
truncate table rop_tmp<br />
SET TRANSACTION ISOLATION LEVEL READ COMMITTED<br />
commit transaction
- 3rd process when I get an Error of "Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction". My program downloads data from ORIGDATA using certain criterias but it failed becuase of this locking. Run only during 12 midnight.
Question: Can you please suggest any isolation level that stops the INSERT for process 1 and allow my process 3 to download data into a file. Please I really need the solution, any idea is welcome. I will try also to look for solution.
/Dabuskol
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
