DB Access from different m/c

when i access the database from different DB server getting the following error

Msg 18452, Sev 14, State 1, Line 1 : Login failed for user ''. The user is not associated with a trusted SQL Server connection. [SQLSTATE 28000]

Thanks,
Ravinder

Your SQL Server is set to Windows Authentication only. You need to logon with a user which has granted access to the server. You can′t provide a userId and passwort in the connecting string because this will be ognored in the case of Windows Auth. only. Another option would be, if feasable for your to switch the authentication mode to mixed.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

datetime problem

my asp .net application is coming along nicely. however, i would like to record the users last login time. i have a datetime field, and when i insert or update using Now() as the data for the field, i simply get 1/1/1900 12:00:00 in the last_login field. Same thing using Today(). any suggestions? am i using the wrong data type? i need to be able to do date comparisons as i plan on connecting my site with a forum, and this would be the easiest way to determine if there were new posts from the user's perspective.

TIA

Use GetDate() in your SQL to input the current time.|||

Unless your business object has a say in what the date/time stamp value is, there is little reason to send it over the network both ways. Might as well have the database provide the value and hand it back to you.

|||

thanks mikesdotnetting! worked like a charm!

|||

Don't forget that getdate(0 and Now() work on different servers which can be in different time zones :)

Datetime in Where clause

I have a simple stored procedure that updates the loggedout field, which is
a
datetime field, in a table based on the user id and login date (which is als
o
a datetime field). Since a user can login multiple times throughout the day,
I need the Where clause to include the time, which is part of the datetime
being passed in.
When I run the following sp and pass in a datetime field, for example
5/15/2005 1:12:22 PM, the table never updates, even though there is a field
with that date/time in it.
PROCEDURE dbo.UpdateLoginActivity
(
@.fldLogoutDate datetime,
@.fldUserId int,
@.fldLoginDate datetime
)
AS
UPDATE tblLoginActivity
SET fldLogoutDate = @.fldLogoutDate
WHERE (fldUserId = @.fldUserId) AND (fldLoginDate = @.fldLoginDate)
How can I use a datetime field effectively in a Where clause, so that it
finds the exact row based on the date & time?datetime stores milliseconds:
select getdate()
---
2006-05-15 16:31:33.750
(1 row(s) affected)
YOu must either provide milliseconds in your parameter or modify your
update
UPDATE tblLoginActivity
SET fldLogoutDate = @.fldLogoutDate
WHERE (fldUserId = @.fldUserId) AND (fldLoginDate >= @.fldLoginDate)
AND (fldLoginDate < dateadd(ms,1000,@.fldLoginDate) )|||It worked fine for me.
Did you mean to use 5/15/2006 1:12:22 PM instead of 5/15/2005 1:12:22
PM ?
Richard wrote:
> I have a simple stored procedure that updates the loggedout field, which i
s a
> datetime field, in a table based on the user id and login date (which is a
lso
> a datetime field). Since a user can login multiple times throughout the da
y,
> I need the Where clause to include the time, which is part of the datetime
> being passed in.
> When I run the following sp and pass in a datetime field, for example
> 5/15/2005 1:12:22 PM, the table never updates, even though there is a fiel
d
> with that date/time in it.
> PROCEDURE dbo.UpdateLoginActivity
> (
> @.fldLogoutDate datetime,
> @.fldUserId int,
> @.fldLoginDate datetime
> )
> AS
> UPDATE tblLoginActivity
> SET fldLogoutDate = @.fldLogoutDate
> WHERE (fldUserId = @.fldUserId) AND (fldLoginDate = @.fldLoginDate)
> How can I use a datetime field effectively in a Where clause, so that it
> finds the exact row based on the date & time?|||That worked! Thanks Alexander.
"Alexander Kuznetsov" wrote:

> datetime stores milliseconds:
> select getdate()
> ---
> 2006-05-15 16:31:33.750
> (1 row(s) affected)
> YOu must either provide milliseconds in your parameter or modify your
> update
> UPDATE tblLoginActivity
> SET fldLogoutDate = @.fldLogoutDate
> WHERE (fldUserId = @.fldUserId) AND (fldLoginDate >= @.fldLoginDate)
> AND (fldLoginDate < dateadd(ms,1000,@.fldLoginDate) )
>|||Yes, that is the correct date. See Alexander's post for the syntax, which
worked well for me. Thanks.
"tjolliffe@.hotmail.com" wrote:

> It worked fine for me.
> Did you mean to use 5/15/2006 1:12:22 PM instead of 5/15/2005 1:12:22
> PM ?
>
> Richard wrote:
>

Datebase roles problem.

OK this is going to sound like a very easy question but for the life of me its not working.

I have got a login called "Sales" and it is binded to a user called "sales"

The sales user has of course got the public role for my database.

I have created a Role on the database called "Sales Role" and given all the needed permissions to all the tables in the database.

As soon as i give the user the new role and then go to the securables area and look at the tables and hit the "Effective Privileges" button there is nothing listed....

If i take off the "Sales Role" role from the user and go back and look at the "Effective Privileges" it is filled with the privileges the public role has given it.

Any one no why as soon as i give the user my role (which has got privileges set for every table) the user does not have any effective rights on any table?

OK I sorted it my self. To be honest I think it’s rather annoying how they have done it....

I gave the user, select, Delete, Insert and Update privileges and denied the rest... which should have worked in my opinion.

What I did not no was the give a user the privileges I wanted I also had to grant them "control" privileges too... apart from that not being too obvious they should really have given out a warning to say that the following privileges can't be granted with out "control" being granted also, either that or automatically give the user the control privilege if you give them the select privilege?

What you guys think? Was it just me being stupid or what, I could not find the answer any where on Google and you didn’t have to do that in SQL 2000 either.

|||

CONTROL is a covering permission, which means it implies all other permissions on the entity as well. If you grant CONTROL, you also grant the other appropriate permissions depending on entity type. If you deny CONTROL, you will deny all permissions on the object.

So, you don't need to grant CONTROL, to be able to grant SELECT on a table - you can just grant SELECT. On the other hand, if you grant SELECT, but deny CONTROL, the SELECT won't take effect because the deny on the covering permission takes precedence.

SQL Server 2000, didn't had covering permissions, such as CONTROL.

See the following article, for additional information: http://msdn2.microsoft.com/en-us/library/ms177450.aspx.

Thanks
Laurentiu