|
I wish it was a joke, but our DBA passed away. Till such time we find a new DBA, I am the man. I have this script:
exec sp_droprole 'SAM_USER'
exec sp_addrole 'SAM_USER'
exec sp_droprole 'Manage_Presentation'
exec sp_addrole 'Manage_Presentation'
grant select , insert,delete , update on Presentation to Manage_Presentation
grant select , insert,delete , update on Presentation_Menu_Link to Manage_Presentation
grant select , insert,delete , update on PresentationOwner to Manage_Presentation
grant select , insert,delete , update on PresentationSlide to Manage_Presentation
grant select , insert,delete , update on PresentationSlideOverride to Manage_Presentation
grant select , insert,delete , update on PresentationSlideVerticalMenu to Manage_Presentation
exec sp_addrole 'Manage_Slides'
grant select , delete , update on dbo.Slide to Manage_Slides
grant select , delete , update on dbo.Slide_Menu_Link to Manage_Slides
grant select , delete , update on dbo.SlideComment to Manage_Slides
grant select , delete , update on dbo.SlideFile to Manage_Slides
grant select , delete , update on dbo.SlideType to Manage_Slides
grant select , delete , update on dbo.SYSFile to Manage_Slides
exec sp_addrolemember 'SAM_USER','Manage_Presentation'
exec sp_addrolemember 'SAM_USER','Manage_Slides'
--create user section of the database
exec sp_addlogin @loginame = 'Sameer',@passwd = 'Sameer',@defdb = 'SAM_DEV'
exec sp_grantdbaccess 'SameerHanda'
exec sp_addrolemember 'SAM_USER','SameerHanda'
Well all well and good, but when I signon as Sameer and do the following
select * from Presentation, I ge this error:
SELECT permission denied on object 'Presentation', database 'SAM_DEV', owner 'dbo'.
Well what am I missing. Any help would be greatly appreciated
Thanks
Sameer
PS: RIP Kev Dawg 
|
|
|
|
|
You've added the Manage_Presentation and Manage_Slides roles to the SAM_USER role. I think you intended it the other way around. You've also specified a different username in the sp_grantdbaccess and sp_addrolemember calls than you did in the sp_addlogin call.
I think you simply need to change
exec sp_addrolemember 'SAM_USER','Manage_Presentation'
exec sp_addrolemember 'SAM_USER','Manage_Slides' to
exec sp_addrolemember 'Manage_Presentation','SAM_USER'
exec sp_addrolemember 'Manage_Slides','SAM_USER'
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
 Cool thanks it seems to have worked, I am a c# guy being forced to act as a DBA for the time being. Good experience none the less.
|
|
|
|
|
If your DBA died (sorry to hear that) and you are the acting DBA, I would be more than a little concerned about what happens if your DB dies too.
Murphy's Law would suggest that since your DBA died, you are at much higher risk than normal that your SQL Server is going to crash sometime in the near future too.
If it does and you are not prepared for it, you could be in for a lot of nasty suprises.
If I were in your position, I would run full backups on all the databases and especially the master database immediately.
And if you don't know a lot about what you are doing, until you get a new DBA I would advise not worrying so much about the media cost and keep making regular full backups of any databases in active use on a pretty regular basis.
Your DBA probably has some kind of transactional backup system implemented. If you don't have his expertise, and your server dies tomorrow, you could really be in a jam.
|
|
|
|
|
 It's a good thing that I am familair with Oracle DBA stuff. Then again DB's are DB's its just a different way of doing things.
I have already made backups of our MDF file, the transaction logs. I am not sure where the redo logs of Sql server are. Working on figuring that out.
|
|
|
|
|
Hello everybody,
I wanted to transfer XML data to SQL Server using MS Sql Server DTS. But there is no connection object for XML data in MS Sql Server 2000. So without using DOM object how can i transfer XML data to MS Sql Server ?
|
|
|
|
|
Hi,
I'm currently developing a forge where all the receipts is stored in a mySQL database. The players suppose to insert their items within the forge, and activate it.
When the forge activates, the script goes through each item located within, and get the unique id for each item. An example of a result would be:
item1 item4 items5 item8
I want to check if the field 'components' contains these id's. If so, the row should be returned.
I've tried the following statement, but will it work if the order were differnt?
SELECT FROM `recept` WHERE `komponenter` LIKE 'item1%item4%item5%item8'
|
|
|
|
|
it will work... notice that a select don't know the order of the records in a table... if you want to sort the, use after your where statement the ORDER BY keywords...
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
Thank you for your reply!
So if komponenter contains the ID's arranged in the following order:
item1 item5 item3 item2 item4
... a SQL statement like this will find the row?
SELECT `...` FROM `recept` WHERE `komponenter` LIKE '%item5%item4%item3%item2%item1%'?
The reason I'm asking instead of just trying, is the fact that each time I want to try something out, I have to update the server via a console-interface - after I've uploaded the 25 meg new version of the game.
|
|
|
|
|
nop, you don't have it.
SELECT komponenter
FROM recept
WHERE komponenter LIKE 'item%'
ORDER BY komponenter ASC;
this will return :
item1<br />
item2<br />
item3<br />
item4<br />
item5
well, tell me, i don't know what database you're using in your SW, but you might have tool to test SQL statements before updating your whole code and test it each time you change your query...
(Entreprise Manager with SQL Server 2000, SQL*Plus with Oracle DB...)
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
You may also want to consider the IN Syntax if you have the full id:
SELECT * FROM MyTable WHERE MyField IN('Value1','Value2','Value3')
|
|
|
|
|
OK i've used just Microsoft Access database for my application made in c#.
Now i want to know how can use mySQL database.
My questions are the following:
1)How can create a new mysql database within my c# application? (using just sintax with .NET objects or using VisualStudio tools)
2)What i need installed on my (for developing) computer?
3)What is needed installed on the final user computer?
4)I know how create and manageing database in mySQL(using the daemon) so i want to know what are the differences with mySQL and SQL.
Thanks.
|
|
|
|
|
Sasuko,
You may want to visit MySQl's site and look at their .net connector in the tools section.
You may just need to distribute the .dll file with your application.
I hope this is a start for you 
Paul
|
|
|
|
|
so the final user doesn't need the mysql server installed on its pc?
And where can i find the database (where is the file?) i've created before and i want to distribuite with my application?
What directory have i to put the database in during the installation?
|
|
|
|
|
The user should not need to have a mySQL server installed on hir or her machine. Your application should point to the server in the connection string. You'll need to dig around with your mySQL server installation for the database file. If there is an existing database server, you should not have to install a database file anywheres.
|
|
|
|
|
Hola!
Is it possible to so something like this with SQL Server 2005 (in a SPROC)?
DECLARE @Variable varchar(1000)
SET @Variable = 'LastName ASC, FirstName ASC'
SELECT * FROM table ORDER BY @Variable
This doesn't work for sure... but is there a known workaround? I don't want to do "EXEC @sql" for performance and good programming practice reasons.
Thanks!
|
|
|
|
|
ITs not clear to me why you don't just write 'LastName ASC, FirstName ASC' into your query in the first place. If you do have a good reason, then you'll have to write a dynamic query and make use of EXEC @sql. If you're worried about performance, you should know that the optimizer WILL be able to optimize dynamic queries too. It remembers the query plan from that last query so as long as the query isn't going to change too much, you shouldn't have to worry. The only other danger with dynamic queries is that someone would try to inject SQL into the query. If you're using ASP.NET, ValidateRequest is turned on by default.
|
|
|
|
|
The reason is pretty simple, I want the user to be able to sort data by whatever they want. The query I posted was just an example. The real query is about 1 screen long and has 37 parameters.
|
|
|
|
|
I like Edbert's solution (CASE statement in the ORDER BY clause) if the user can only order by one field. However, if you want to support sorting by multiple fields, I think that the dynamic query method will be easier to implement.
|
|
|
|
|
You can't use ORDER BY @Variable.
However, you can use CASE statements to do ORDER BY, e.g.:
SELECT * FROM table
ORDER BY
CASE WHEN @Variable = 'LastName' THEN
LastName
CASE WHEN @Variable = 'FirstName' THEN
FirstName
END ASC
Note: this is limited to one field only. But theoritically you can combine it with another case statement below to order by several columns.
Does that help?
Edbert P.
Sydney, Australia
|
|
|
|
|
 Hi all. I have a problem to solve with an MS Access database. In a login form the user have to enter username and password. In the back there is a table called users witch have the fallowing fields: userid, username, userpass. I wrote the fallowing code to check if the username and password match with those in the table users:
Private sub command2_click()
Set rst1 = CurrentDb.OpenRecordset("useri", dbOpenDynaset)
Private Sub Command2_Click()
Dim qdef1, qdef2 As QueryDef
Set rst1 = CurrentDb.OpenRecordset("useri", dbOpenDynaset)
Set qdef1 = CurrentDb.CreateQueryDef("")
With qdef1
.SQL = "SELECT * FROM users " & "WHERE username= '" & [Forms]![form1]![input_user] & "'" & "and userpass= '" & [Forms]![form1]![input_pass] & "'"
Set rst = qdef1.OpenRecordset()
nri = rst.RecordCount
End With
rst1.FindFirst "username= '" & Me.input_user & "'"
If rst1.NoMatch Then
MsgBox "There is not such user!"
Exit Sub
Else
If nri = 0 Then
MsgBox "Password does not match!"
Exit Sub
Else
' Everythiong is ok... go on and do the stuff!!!
End If
End If
End Sub
My question is if there is any possibility to optimize this code, to make it simple.
Thank you very much.
Respect,
Gabriel
|
|
|
|
|
Aside from a note that you should try using parameters instead of concatenating your SQL statements, the only optimization I can see is this:
Set rst = qdef1.OpenRecordset()
If rst.EOF Then
MsgBox "Not Found"
Exit Sub
Else
MsgBox "Found. Do whatever you want"
End If
First, your SQL statement will not work as you're concatenating the apostrophe with the and operand, see: username= '" & [Forms]![form1]![input_user] & "'" & "and userpass=. You should put a space before the and operand.
Second, your SQL statement will only return a record(s) that have the same username AND userpass, therefore it will not be able to tell if there are no records returned whether it is because the username does not exist or the password is wrong. You do not want to tell users whether it is the password or the username that is wrong. Just tell them username or password is invalid.
Does that help?
Edbert P.
Sydney, Australia
|
|
|
|
|
Hi. you suggest using parameters and by saying that u refer to using querys? because I was thinking is much more easier to user a query in query builder and then just to refer to it in the code... I was telling you I'm a newbie. Now... the question is: how can I do that?
Thank you.
Gabriel - Romania
|
|
|
|
|
Excuse me if this is old news, I had some run time problems using easy_odbc in conjunction with the MSaccess ODBC driver. The problems were to do with ODBC exception handling for the most part. I had a freind look at it and he came up with some suggestions for patches. Following is the output of a WINDIF compare of the original and patched versions of easy_odbc.cpp and test.cpp. The changes are a bit over my head but the original problems are well and truly fixed, thought this might be useful to someone. On a related subject, is there any way to insert the contents of a variable in line with an SQL query? Something like the following:
// fills a ResultSet with some data
easyodbc::ResultSet rslt = db.ExecuteQuery("SELECT * FROM kids" WHERE name =
MYVARIABLE);
Patch for easy_odbc.cpp:
long Database::Execute(const char* sql) {
try {
if(!this->m_bOpened) {
throw EasyODBCException("Call Open() before
Execute()",-1);
}
<! SQLRETURN ret = SQLPrepare(this->m_hStmt,(unsigned
char*)sql,
!> SQLFreeStmt(this->m_hStmt,SQL_CLOSE);
!> SQLRETURN ret = SQLAllocStmt(this->m_hDbc, &this->m_hStmt);
!> if(ret == SQL_ERROR || ret == SQL_INVALID_HANDLE || ret < 0)
{
!> HandleError("DBC");
!> }
!>
!> ret = SQLPrepare(this->m_hStmt,(unsigned char*)sql,
Patch for test.cpp:
// closes the database connection
db.Close();
<! }catch(easyodbc::EasyODBCException *ex) {
!> }catch(easyodbc::EasyODBCException ex) {
<! char buff[51];
<! ex->GetMessage(buff);
<! printf(buff);
!> char buff[2000] = {0};
!> ex.GetMessage(buff);
!> printf("%s\n", buff);
}
return 0;
}
--
Regards
Michel de la Villefromoy
|
|
|
|
|
I have a client server application (Powerbuilder) using Oracle database as the backend. My problem is how to connect to the same database from a remote location using the same application. I amusing ODBC for my connection currently. Kindly advice!
Francis
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
