Determining Security: user

Showing posts with label user. Show all posts

Thursday, March 29, 2012

Determine what User Account SQL Server is running under

(MS SQL 2000)
Is there a way to determine what network user account SQL Server is
installed and running under? More importantly, can I easily change it?
Thanks!If you have permissions to the server have a look in Control Panel /
Services / MSSQLServer / Logon Tab
It can be easily changed but you do need to stop/start the services for it
to take effect
--
HTH. Ryan
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:uLoxF9TEGHA.1508@.TK2MSFTNGP15.phx.gbl...
> (MS SQL 2000)
> Is there a way to determine what network user account SQL Server is
> installed and running under? More importantly, can I easily change it?
> Thanks!
>|||Thanks! That was pretty easy.
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23zgi8AUEGHA.2724@.TK2MSFTNGP12.phx.gbl...
> If you have permissions to the server have a look in Control Panel /
> Services / MSSQLServer / Logon Tab
> It can be easily changed but you do need to stop/start the services for it
> to take effect
> --
> HTH. Ryan
>
> "D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:uLoxF9TEGHA.1508@.TK2MSFTNGP15.phx.gbl...
>> (MS SQL 2000)
>> Is there a way to determine what network user account SQL Server is
>> installed and running under? More importantly, can I easily change it?
>> Thanks!
>>
>|||Hi,
The proper method to change the account is via the Enterprise Manager or you
have to grant the new account some additional permissions on the server. I do
not remember all of the permissions, but they include 'act as part of os',
'run as service', etc.
If the account is changed via the EM, all of the required permisions are
automatically granted.
hth
DeeJay Puar
MCDBA
"D. Shane Fowlkes" wrote:
> Thanks! That was pretty easy.
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%23zgi8AUEGHA.2724@.TK2MSFTNGP12.phx.gbl...
> > If you have permissions to the server have a look in Control Panel /
> > Services / MSSQLServer / Logon Tab
> >
> > It can be easily changed but you do need to stop/start the services for it
> > to take effect
> >
> > --
> > HTH. Ryan
> >
> >
> > "D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> > news:uLoxF9TEGHA.1508@.TK2MSFTNGP15.phx.gbl...
> >> (MS SQL 2000)
> >>
> >> Is there a way to determine what network user account SQL Server is
> >> installed and running under? More importantly, can I easily change it?
> >>
> >> Thanks!
> >>
> >>
> >
> >
>
>|||Thanks again.
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
news:5881C956-4A72-4868-A3BC-1E3907C3667E@.microsoft.com...
> Hi,
> The proper method to change the account is via the Enterprise Manager or
> you
> have to grant the new account some additional permissions on the server. I
> do
> not remember all of the permissions, but they include 'act as part of os',
> 'run as service', etc.
> If the account is changed via the EM, all of the required permisions are
> automatically granted.
> hth
> DeeJay Puar
> MCDBA
> "D. Shane Fowlkes" wrote:
>> Thanks! That was pretty easy.
>>
>> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
>> news:%23zgi8AUEGHA.2724@.TK2MSFTNGP12.phx.gbl...
>> > If you have permissions to the server have a look in Control Panel /
>> > Services / MSSQLServer / Logon Tab
>> >
>> > It can be easily changed but you do need to stop/start the services for
>> > it
>> > to take effect
>> >
>> > --
>> > HTH. Ryan
>> >
>> >
>> > "D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
>> > news:uLoxF9TEGHA.1508@.TK2MSFTNGP15.phx.gbl...
>> >> (MS SQL 2000)
>> >>
>> >> Is there a way to determine what network user account SQL Server is
>> >> installed and running under? More importantly, can I easily change
>> >> it?
>> >>
>> >> Thanks!
>> >>
>> >>
>> >
>> >
>>

Sunday, March 25, 2012

Determine date when each user last accessed the database

I need to clean up some security issues. We have numerous users who access the database and I'm not confident which logins can be deleted. I've already done sp_who and sp_who2 and neither of them gave me exactly what I wanted (I know they didn't an employee who was terminated but certainly access the database numerous times before he left). I also looked at the sys.sysprocesses table. Is there another system table that can be tapped into for this?

Thanks!

It depends on whether you're looking for a change to the objects or simply an access to the data. For data, you need to set some things up to track who has looked at it recently - and that will only work if they used an application that reports who the user was, such as SQL Server Management Studio. If you have not set the system up to track data access or the application they used to get to the data doesn't report the accurate name of the user, then you won't be able to go back in time to find out what they did.

Thanks -

Buck Woody

|||

One option would be to create a trigger to populate a table of user logins. This might be resource intensive if there is a lot of login activity.

Or, you can turn on login auditing for successful logins and search through the Event log.

You can use a third-party database auditing tool.

Determine All User Databases

Hi All
I have a SQL Server 2005 server and I was wanting to determine all of the
user databases and I was wondering if there was a better way to do this
instead of the following script:
select name
from master.sys.databases
where name not in ('mater', 'tempdb', 'msdb', 'model')
Thanks
If these are the only 4 db's that you want to eliminate, then you could also
query based on database_id, and then it wouldn't matter if you don't spell
one of the database names correctly. :-) You also don't need a 3-part name
when you use the new metadata views.
SELECT name FROM sys.databases
WHERE database_id > 4
If you set up replication, some servers will get a distribution database,
and you may want to eliminate that one also.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:7B789F7B-702A-4109-9FEB-D8E6F27A4FE5@.microsoft.com...
> Hi All
> I have a SQL Server 2005 server and I was wanting to determine all of the
> user databases and I was wondering if there was a better way to do this
> instead of the following script:
> select name
> from master.sys.databases
> where name not in ('mater', 'tempdb', 'msdb', 'model')
> Thanks

Determine All User Databases

Hi All
I have a SQL Server 2005 server and I was wanting to determine all of the
user databases and I was wondering if there was a better way to do this
instead of the following script:
select name
from master.sys.databases
where name not in ('mater', 'tempdb', 'msdb', 'model')
ThanksIf these are the only 4 db's that you want to eliminate, then you could also
query based on database_id, and then it wouldn't matter if you don't spell
one of the database names correctly. :-) You also don't need a 3-part name
when you use the new metadata views.
SELECT name FROM sys.databases
WHERE database_id > 4
If you set up replication, some servers will get a distribution database,
and you may want to eliminate that one also.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:7B789F7B-702A-4109-9FEB-D8E6F27A4FE5@.microsoft.com...
> Hi All
> I have a SQL Server 2005 server and I was wanting to determine all of the
> user databases and I was wondering if there was a better way to do this
> instead of the following script:
> select name
> from master.sys.databases
> where name not in ('mater', 'tempdb', 'msdb', 'model')
> Thanks

Determine All User Databases

Hi All
I have a SQL Server 2005 server and I was wanting to determine all of the
user databases and I was wondering if there was a better way to do this
instead of the following script:
select name
from master.sys.databases
where name not in ('mater', 'tempdb', 'msdb', 'model')
ThanksIf these are the only 4 db's that you want to eliminate, then you could also
query based on database_id, and then it wouldn't matter if you don't spell
one of the database names correctly. :-) You also don't need a 3-part name
when you use the new metadata views.
SELECT name FROM sys.databases
WHERE database_id > 4
If you set up replication, some servers will get a distribution database,
and you may want to eliminate that one also.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:7B789F7B-702A-4109-9FEB-D8E6F27A4FE5@.microsoft.com...
> Hi All
> I have a SQL Server 2005 server and I was wanting to determine all of the
> user databases and I was wondering if there was a better way to do this
> instead of the following script:
> select name
> from master.sys.databases
> where name not in ('mater', 'tempdb', 'msdb', 'model')
> Thanks

Thursday, March 22, 2012

detecting whether all items are selected in a multi-value paramete

Is there an easy way to detect whether the user has selected all possible
items in a multi-value parameter? If they've selected everything I want to
put "All Regions" as a label in the header of the report. If they've selected
only some, I want to list the regions they've selected [accomplished by doing
Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in the
header, I can't just compare Parameters!Region.Label.Count to
Count(Fields!Region.Value,"RegionsDataset") because you can't use fields in
the header. (The workaround of adding a hidden field to the body of the
report then referencing it by ReportItems!HiddenField.Value is just too
kludgy for me... plus the hidden field's gotta repeat on every page.)
If anyone has any good suggestions, I'd be interested in hearing them.
The solution I decided upon was builiding a hidden parameter which is
dependent upon the multi-value parameter... it's query figures out if they've
selected all possible options. But it seems silly to have to fire off another
query to the DB to accomplish this.If you know the count of parameters you can use that:
This is something I do in a textbox where I show the parameters selected:
="Containers= " & iif(Parameters!Container.Count > 8," more than 8
Selected",Join(Parameters!Container.Value, ", "))
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:709E330A-6E97-4F37-9F2B-50A417CFF15C@.microsoft.com...
> Is there an easy way to detect whether the user has selected all possible
> items in a multi-value parameter? If they've selected everything I want to
> put "All Regions" as a label in the header of the report. If they've
> selected
> only some, I want to list the regions they've selected [accomplished by
> doing
> Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in the
> header, I can't just compare Parameters!Region.Label.Count to
> Count(Fields!Region.Value,"RegionsDataset") because you can't use fields
> in
> the header. (The workaround of adding a hidden field to the body of the
> report then referencing it by ReportItems!HiddenField.Value is just too
> kludgy for me... plus the hidden field's gotta repeat on every page.)
> If anyone has any good suggestions, I'd be interested in hearing them.
> The solution I decided upon was builiding a hidden parameter which is
> dependent upon the multi-value parameter... it's query figures out if
> they've
> selected all possible options. But it seems silly to have to fire off
> another
> query to the DB to accomplish this.|||Bruce-
That would work if the list of items in the multi-value parameter was a
constant set. But the list can change over time, so I can't hard code it to a
specific count. Any other suggestions? Thanks for the reply, though.
"Bruce L-C [MVP]" wrote:
> If you know the count of parameters you can use that:
> This is something I do in a textbox where I show the parameters selected:
> ="Containers= " & iif(Parameters!Container.Count > 8," more than 8
> Selected",Join(Parameters!Container.Value, ", "))
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
> news:709E330A-6E97-4F37-9F2B-50A417CFF15C@.microsoft.com...
> > Is there an easy way to detect whether the user has selected all possible
> > items in a multi-value parameter? If they've selected everything I want to
> > put "All Regions" as a label in the header of the report. If they've
> > selected
> > only some, I want to list the regions they've selected [accomplished by
> > doing
> > Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in the
> > header, I can't just compare Parameters!Region.Label.Count to
> > Count(Fields!Region.Value,"RegionsDataset") because you can't use fields
> > in
> > the header. (The workaround of adding a hidden field to the body of the
> > report then referencing it by ReportItems!HiddenField.Value is just too
> > kludgy for me... plus the hidden field's gotta repeat on every page.)
> >
> > If anyone has any good suggestions, I'd be interested in hearing them.
> >
> > The solution I decided upon was builiding a hidden parameter which is
> > dependent upon the multi-value parameter... it's query figures out if
> > they've
> > selected all possible options. But it seems silly to have to fire off
> > another
> > query to the DB to accomplish this.
>
>|||If your list is based on a query you could have a query that returns a count
and then you can use that count to compare to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:EAFC9B4C-3371-4159-8917-4CAA8595FC7E@.microsoft.com...
> Bruce-
> That would work if the list of items in the multi-value parameter was a
> constant set. But the list can change over time, so I can't hard code it
> to a
> specific count. Any other suggestions? Thanks for the reply, though.
> "Bruce L-C [MVP]" wrote:
>> If you know the count of parameters you can use that:
>> This is something I do in a textbox where I show the parameters selected:
>> ="Containers= " & iif(Parameters!Container.Count > 8," more than 8
>> Selected",Join(Parameters!Container.Value, ", "))
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
>> news:709E330A-6E97-4F37-9F2B-50A417CFF15C@.microsoft.com...
>> > Is there an easy way to detect whether the user has selected all
>> > possible
>> > items in a multi-value parameter? If they've selected everything I want
>> > to
>> > put "All Regions" as a label in the header of the report. If they've
>> > selected
>> > only some, I want to list the regions they've selected [accomplished by
>> > doing
>> > Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in
>> > the
>> > header, I can't just compare Parameters!Region.Label.Count to
>> > Count(Fields!Region.Value,"RegionsDataset") because you can't use
>> > fields
>> > in
>> > the header. (The workaround of adding a hidden field to the body of the
>> > report then referencing it by ReportItems!HiddenField.Value is just too
>> > kludgy for me... plus the hidden field's gotta repeat on every page.)
>> >
>> > If anyone has any good suggestions, I'd be interested in hearing them.
>> >
>> > The solution I decided upon was builiding a hidden parameter which is
>> > dependent upon the multi-value parameter... it's query figures out if
>> > they've
>> > selected all possible options. But it seems silly to have to fire off
>> > another
>> > query to the DB to accomplish this.
>>

Detecting when an user connects to a database

Greetings,
Is there any way to detect when a user connects to my database?
I mean, I want to know when a user connects to my database and when he/she
disconnects, and do something when this happens (a trigger, I guess).
Any ideas'
Thanks!Hi
If the user is only connecting to your database through a bespoke
application, then you will have control of this. If you don't have this
option you may want to check out the C2 auditing abilities and processing
the audit file, using SQL profiler or poling the sysprocesses table.
John
"Arnau Font" <afont@.nospam-ipssoft.com> wrote in message
news:uATVAOA5DHA.2576@.TK2MSFTNGP11.phx.gbl...

quote:

> Greetings,
> Is there any way to detect when a user connects to my database?
> I mean, I want to know when a user connects to my database and when he/she
> disconnects, and do something when this happens (a trigger, I guess).
> Any ideas'
> Thanks!
>

|||HI,
Simple option is enable Audit level (ALL)
How to enable audit,
1. In Enterprise manager , select the server name and right click and select
properties
2. Select the security tab and select Audit level as "ALL"
3. Restart SQL server Service
From now all the successful Login and Failure attempts will stored in SQL
server errorlog, but we can not set a trigger for this.
I belive to set a trigger we may need write our own custom trigger in
sysprocesses table.
Thanks
Hari
MCDBA
"Arnau Font" <afont@.nospam-ipssoft.com> wrote in message
news:uATVAOA5DHA.2576@.TK2MSFTNGP11.phx.gbl...

quote:

> Greetings,
> Is there any way to detect when a user connects to my database?
> I mean, I want to know when a user connects to my database and when he/she
> disconnects, and do something when this happens (a trigger, I guess).
> Any ideas'
> Thanks!
>

detecting Update Conflict

When a user try to update the record I use timestamp field (upsize_ts) to
figure out if data was modified by other user.
However I found out @.@.DBTS may cause problem in Multi user senario and may
not return correct Tiemstamp value as it is not the timestamp for the curren
t
operation but for the database is there any inexpensive solutin to resolve
this.
Thanks
Tanweer
CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
@.rowversion timestamp OUTPUT,
@.people_phone_id int,
@.offsysid varchar(5),
@.people_id int=Null,
@.sequence_id smallint=Null,
@.code_type varchar(5)=Null,
@.code_value varchar(50)=Null,
@.modify_operator varchar(15)=Null,
@.upsize_ts timestamp=Null
AS
SET NOCOUNT OFF
DECLARE @.errormsg varchar(1000)
DECLARE @.errcode int
DECLARE @.rowcount int
DECLARE @.modify_date datetime
SET @.modify_date = GetDate()
UPDATE [dbo].[PEOPLE_PHONE] SET
[people_id] = case when @.people_id is null then people_id else @.people_id
end,
[sequence_id] = case when @.sequence_id is null then sequence_id else
@.sequence_id end,
[code_type] = case when @.code_type is null then code_type else @.code_type
end,
[code_value] = case when @.code_value is null then code_value else
@.code_value end,
[modify_date] = case when @.modify_date is null then modify_date else
@.modify_date end,
[modify_operator] = case when @.modify_operator is null then modify_operator
else @.modify_operator end
WHERE
[people_phone_id] = @.people_phone_id
AND [offsysid] = @.offsysid
AND [upsize_ts] = @.rowversion
SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
--Use it to check the value of Timestamp column
--print 'Timestamp' + convert(varchar(50),@.rowversion)
select @.rowversion
IF(@.errcode = 0)
BEGIN
IF(@.RowCount = 0)
BEGIN
IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
@.people_phone_id AND [offsysid] = @.offsysid)
BEGIN
RAISERROR('Row not updated because it was changed', 15, 1)
RETURN 50003
END
ELSE
BEGIN
RAISERROR('Row is gone', 15, 1)
RETURN 50004
END
END
ELSE
PRINT 'Row updated'
END
ELSE
PRINT 'Error'
RETURN @.errcode
GOeasist of all..
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
ALL OPERATIONS
COMMIT TRAN
"Tanweer" wrote:

> When a user try to update the record I use timestamp field (upsize_ts) to
> figure out if data was modified by other user.
> However I found out @.@.DBTS may cause problem in Multi user senario and may
> not return correct Tiemstamp value as it is not the timestamp for the curr
ent
> operation but for the database is there any inexpensive solutin to resolve
> this.
> Thanks
> Tanweer
>
>
> CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
> @.rowversion timestamp OUTPUT,
> @.people_phone_id int,
> @.offsysid varchar(5),
> @.people_id int=Null,
> @.sequence_id smallint=Null,
> @.code_type varchar(5)=Null,
> @.code_value varchar(50)=Null,
> @.modify_operator varchar(15)=Null,
> @.upsize_ts timestamp=Null
> AS
> SET NOCOUNT OFF
> DECLARE @.errormsg varchar(1000)
> DECLARE @.errcode int
> DECLARE @.rowcount int
> DECLARE @.modify_date datetime
> SET @.modify_date = GetDate()
>
> UPDATE [dbo].[PEOPLE_PHONE] SET
> [people_id] = case when @.people_id is null then people_id else @.people_id
> end,
> [sequence_id] = case when @.sequence_id is null then sequence_id else
> @.sequence_id end,
> [code_type] = case when @.code_type is null then code_type else @.code_type
> end,
> [code_value] = case when @.code_value is null then code_value else
> @.code_value end,
> [modify_date] = case when @.modify_date is null then modify_date else
> @.modify_date end,
> [modify_operator] = case when @.modify_operator is null then modify_operat
or
> else @.modify_operator end
> WHERE
> [people_phone_id] = @.people_phone_id
> AND [offsysid] = @.offsysid
> AND [upsize_ts] = @.rowversion
> SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
> --Use it to check the value of Timestamp column
> --print 'Timestamp' + convert(varchar(50),@.rowversion)
> select @.rowversion
> IF(@.errcode = 0)
> BEGIN
> IF(@.RowCount = 0)
> BEGIN
> IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
> @.people_phone_id AND [offsysid] = @.offsysid)
> BEGIN
> RAISERROR('Row not updated because it was changed', 15, 1)
> RETURN 50003
> END
> ELSE
> BEGIN
> RAISERROR('Row is gone', 15, 1)
> RETURN 50004
> END
> END
> ELSE
> PRINT 'Row updated'
> END
> ELSE
> PRINT 'Error'
>
> RETURN @.errcode
> GO
>|||A doubt, why do you bother in this scnario. There is only one update and no
select and no transaction. You will never get an erronous update in this cas
e.
"Tanweer" wrote:

> When a user try to update the record I use timestamp field (upsize_ts) to
> figure out if data was modified by other user.
> However I found out @.@.DBTS may cause problem in Multi user senario and may
> not return correct Tiemstamp value as it is not the timestamp for the curr
ent
> operation but for the database is there any inexpensive solutin to resolve
> this.
> Thanks
> Tanweer
>
>
> CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
> @.rowversion timestamp OUTPUT,
> @.people_phone_id int,
> @.offsysid varchar(5),
> @.people_id int=Null,
> @.sequence_id smallint=Null,
> @.code_type varchar(5)=Null,
> @.code_value varchar(50)=Null,
> @.modify_operator varchar(15)=Null,
> @.upsize_ts timestamp=Null
> AS
> SET NOCOUNT OFF
> DECLARE @.errormsg varchar(1000)
> DECLARE @.errcode int
> DECLARE @.rowcount int
> DECLARE @.modify_date datetime
> SET @.modify_date = GetDate()
>
> UPDATE [dbo].[PEOPLE_PHONE] SET
> [people_id] = case when @.people_id is null then people_id else @.people_id
> end,
> [sequence_id] = case when @.sequence_id is null then sequence_id else
> @.sequence_id end,
> [code_type] = case when @.code_type is null then code_type else @.code_type
> end,
> [code_value] = case when @.code_value is null then code_value else
> @.code_value end,
> [modify_date] = case when @.modify_date is null then modify_date else
> @.modify_date end,
> [modify_operator] = case when @.modify_operator is null then modify_operat
or
> else @.modify_operator end
> WHERE
> [people_phone_id] = @.people_phone_id
> AND [offsysid] = @.offsysid
> AND [upsize_ts] = @.rowversion
> SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
> --Use it to check the value of Timestamp column
> --print 'Timestamp' + convert(varchar(50),@.rowversion)
> select @.rowversion
> IF(@.errcode = 0)
> BEGIN
> IF(@.RowCount = 0)
> BEGIN
> IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
> @.people_phone_id AND [offsysid] = @.offsysid)
> BEGIN
> RAISERROR('Row not updated because it was changed', 15, 1)
> RETURN 50003
> END
> ELSE
> BEGIN
> RAISERROR('Row is gone', 15, 1)
> RETURN 50004
> END
> END
> ELSE
> PRINT 'Row updated'
> END
> ELSE
> PRINT 'Error'
>
> RETURN @.errcode
> GO
>|||Don't use @.@.dbts, but rather retrieve the actual value for the updated row.
If more than one row is updated at a time, then you'd have to store the
values in a temporary table (e.g. a table variable) or use the OUTPUT clause
in SQL 2005.
ML
http://milambda.blogspot.com/|||I guess I have to go with ML syggestion there is no easy way out.
May be a wish for SQL 2005 Service Pack2
Thanks
Tanweer
"ML" wrote:

> Don't use @.@.dbts, but rather retrieve the actual value for the updated row
.
> If more than one row is updated at a time, then you'd have to store the
> values in a temporary table (e.g. a table variable) or use the OUTPUT clau
se
> in SQL 2005.
>
> ML
> --
> http://milambda.blogspot.com/|||are you using sql server 2005'
--
"Tanweer" wrote:
> I guess I have to go with ML syggestion there is no easy way out.
> May be a wish for SQL 2005 Service Pack2
> Thanks
> Tanweer
> "ML" wrote:
>

Detecting the network UserID

Hey guys,
I would like to determine the network id of the current user so that I can
pass that value to a stored procedure that the report references.
I tried add the following code in the Code tab of the Report Properties tab:
Function GetUserID() AS String
Return System.Environment.UserName
End Function
I then referenced this function as the default value for the @.vcUserId
argument that the stored procedure wants by assigning =Code.GetUserID() as
the Non-queried default value for the report parameter.
This approach works well when I preview the report in Visual Studio.
Unfortunately, it fails when I deploy the report to the server.
Can anyone offer some suggestions on how to approach this issue?
Thanks in advance,
-JimI'd guess that the reason this doesn't work is because when you run it
in VS.net the application is running as you or some other acceptable
local user
(http://msdn2.microsoft.com/en-us/library/system.environment.username.aspx).
When it runs online it probably can't execute or it returns the
ASP.NET worker process's user or something similar and not useful. I
know for ASP.NET you can call HttpContext.User.Idenity.Name (or
something similar, I'm working from memory mostly) to see who the
logged in user is, this might work better in report services. Or there
might be a separate way to get the current user from the report manager
that I'm not aware of.|||Use the global variable User!UserID.value
You can get to this with the expression builder. It returns domain\username,
if you don't want the domain then you will need to strip it off.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Terence Tirella" <ttirella@.literate.com> wrote in message
news:1141851303.988200.237700@.u72g2000cwu.googlegroups.com...
> I'd guess that the reason this doesn't work is because when you run it
> in VS.net the application is running as you or some other acceptable
> local user
> (http://msdn2.microsoft.com/en-us/library/system.environment.username.aspx).
> When it runs online it probably can't execute or it returns the
> ASP.NET worker process's user or something similar and not useful. I
> know for ASP.NET you can call HttpContext.User.Idenity.Name (or
> something similar, I'm working from memory mostly) to see who the
> logged in user is, this might work better in report services. Or there
> might be a separate way to get the current user from the report manager
> that I'm not aware of.
>sql

Wednesday, March 21, 2012

Detecting a change in a particular field in a database

Is it possible, using VB code, to retain a user on a particular pageuntil the status of a certain field in a database is changed?
The scenario I am working on is that a person makes a reservation andhas to wait until that reservation has been accepted or rejected by theadministration.
Dim dsResv As DataSet
dsResv = objResv.DALgetResvStatus(resvId)
While (dsResv.Tables(0).Rows(0).Item("resvStatus") = "pending")
dsResv = objResv.DALgetResvStatus(resvId)
IfdsResv.Tables(0).Rows(0).Item("resvStatus") = "denied" Then
failure.Visible = True
LinkButton1.Visible = True
ElseIf dsResv.Tables(0).Rows(0).Item("resvStatus") = "reserved" Then
success.Visible = True
LinkButton1.Visible = True
End If
End While
This is how I tried to do it, but it doesnt seem to work.
Any suggestions?
If it is web application, use AJAX to send the request to the server and to get the response asynchronously without submitting the page to server.
For AJAX refer:
http://aspalliance.com/716|||Any other way??
i mean without javascripting

Monday, March 19, 2012

Details section does not show all details

I have a data entry application where the user is allowed to enter multiple funding, goals and objective selections for each Project ID record. I have a database that lists each Project by ID and a relational table for each selection and its corresponding project id. For example:

projid type descr

1 Funding Capital

1 Goal PI-1 To develop a project that will come in on schedule

1 Objective PI-1.1 Schedule objective 1

1 Goal T-3 To develop a project that will come in on budget

1 Objective T-3.1 Budget objective 1

1 Objective T-3.2 Budget objective 2

1 Objective T-3.2 Budget objective 3

The report only prints the following in the details section:

projid type descr

1 Funding Capital

1 Goal PI-1 To develop a project that will come in on schedule

1 Objective PI-1.1 Schedule objective 1

Any help is greatly appreciated!

I started a brand new report starting with the details and then adding the two groups and now all records and data shows and sort correctly.

|||

I started a new report and now all related data is showing in the details for each record. But there is another problem...the report sorts by Project Owner then Project Name. If the project name is the same in 2 different records (i.e. Record id = 89 with project name = "Project" and Record id=90 and project name = "Project" the report show record 89 but in the details it shows related detail for record id's 89 and 90 and then doesn't show the individual record id = 90 at all. I don't understand why the details for each record are dependent on the project name. I am new to SQL Reporting and appreciate any help.

Detailed User Access Rights

Hi all, I have a question about user access in MS-SQL.
In our system, we only really have one main user, the SA user. Thats the
user that is used to do all the reads & writes for our application. Works
fine.
A problem arises when our customers want to have access to the DB. Now, we
want to control this so they cant go hacking up thier data and blaming us, s
o
we only allow read only access for report writings etc.
Now our customers are interested in writing views & user defined functions
etc, but we cant seem to find a way to allow users access to create / mod /
delete views & user defined functions, and NOT create / mod / delete DB
tables.
Any ideas / suggestions?
Thanks!> In our system, we only really have one main user, the SA user. Thats the
> user that is used to do all the reads & writes for our application. Works
> fine.
Although this works, it doesn't make it right. The Best Practice in SQL
Server is to never use 'sa' for routine database access.

> Now our customers are interested in writing views & user defined functions
> etc, but we cant seem to find a way to allow users access to create / mod
> /
> delete views & user defined functions, and NOT create / mod / delete DB
> tables.
You can allow users to create objects in only their own schema by granting
the desired object create permissions. Note that only privileged users
(sysdamin, dbo, db_owner and db_ddladmin) can create objects owned by other
users or the 'dbo' schema. Consequently, you could grant CREATE TABLE as
well without compromising data in tables owned by other users.
GRANT
CREATE VIEW,
CREATE FUNCTION
TO CustomerUser
If those user objects reference your application tables, SELECT permissions
on those tables will be needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:7C2D9969-BBEF-4469-982B-5AC3B5307656@.microsoft.com...
> Hi all, I have a question about user access in MS-SQL.
> In our system, we only really have one main user, the SA user. Thats the
> user that is used to do all the reads & writes for our application. Works
> fine.
> A problem arises when our customers want to have access to the DB. Now,
> we
> want to control this so they cant go hacking up thier data and blaming us,
> so
> we only allow read only access for report writings etc.
> Now our customers are interested in writing views & user defined functions
> etc, but we cant seem to find a way to allow users access to create / mod
> /
> delete views & user defined functions, and NOT create / mod / delete DB
> tables.
> Any ideas / suggestions?
> Thanks!|||Excellent! Thats just what I needed! Tried it out, and it works just fine
:D
But while were on the subject, could I ask a few more questions?
1) You say that using the SA admin isn't the best way of access the DB
through our application. Is there a specific reason why this is bad?
2) Now that my customers can create their own views etc, I need them to be
able to access user defined functions created by the application. How do I
allow exe permissions on these functions to allow all users to execute them?
Thanks again!
"Dan Guzman" wrote:

> Although this works, it doesn't make it right. The Best Practice in SQL
> Server is to never use 'sa' for routine database access.
>
> You can allow users to create objects in only their own schema by granting
> the desired object create permissions. Note that only privileged users
> (sysdamin, dbo, db_owner and db_ddladmin) can create objects owned by othe
r
> users or the 'dbo' schema. Consequently, you could grant CREATE TABLE as
> well without compromising data in tables owned by other users.
> GRANT
> CREATE VIEW,
> CREATE FUNCTION
> TO CustomerUser
> If those user objects reference your application tables, SELECT permission
s
> on those tables will be needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:7C2D9969-BBEF-4469-982B-5AC3B5307656@.microsoft.com...
>
>|||> 1) You say that using the SA admin isn't the best way of access the DB
> through our application. Is there a specific reason why this is bad?
For routine application database access, it's best to use an account with no
more permissions than actually needed. Using 'sa' or a sysadmin role member
account is overkill unless your app requires that level of security.
Sysadmin role members have full SQL Server permissions and OS permissions
are limited only by the SQL Server service account.

> 2) Now that my customers can create their own views etc, I need them to be
> able to access user defined functions created by the application. How do
> I
> allow exe permissions on these functions to allow all users to execute
> them?
You can GRANT EXECUTE or SELECT, depending on whether the function is scalar
or table-valued.
GRANT SELECT ON MyTableFunction TO CustomerUser
GRANT EXECUTE ON MyScalarFunction TO CustomerUser
Hope this helps.
Dan Guzman
SQL Server MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:95E53960-B58A-4125-B298-430AAAC1191D@.microsoft.com...[vbcol=seagreen]
> Excellent! Thats just what I needed! Tried it out, and it works just
> fine :D
> But while were on the subject, could I ask a few more questions?
> 1) You say that using the SA admin isn't the best way of access the DB
> through our application. Is there a specific reason why this is bad?
> 2) Now that my customers can create their own views etc, I need them to be
> able to access user defined functions created by the application. How do
> I
> allow exe permissions on these functions to allow all users to execute
> them?
> Thanks again!
> "Dan Guzman" wrote:
>|||Right.. i've added those & all is well.
Just as a closing thought.. is there any way I can declare a 'grant' command
to ALL users, current & future?
Its more than likely that once a view / function has been made, it could be
used by other SQL users that will require access, and even though we control
the number of SQL users, it makes more sence to make a function 'global' /
'public' rather than having to recode to add a new user each time we decide
to add one.
Thanks!|||The Best Practice is to create database roles and grant object permissions
only to roles. You can then control access via role membership. This
greatly simplifies security administration.
Hope this helps.
Dan Guzman
SQL Server MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:6F88596D-0A1A-47FB-B56D-9E37EFF3CC94@.microsoft.com...
> Right.. i've added those & all is well.
> Just as a closing thought.. is there any way I can declare a 'grant'
> command
> to ALL users, current & future?
> Its more than likely that once a view / function has been made, it could
> be
> used by other SQL users that will require access, and even though we
> control
> the number of SQL users, it makes more sence to make a function 'global' /
> 'public' rather than having to recode to add a new user each time we
> decide
> to add one.
> Thanks!

Friday, March 9, 2012

detach db failed due to ghost cleanup

hi
i'm trying to detach a database. However i cant as the db is in use. I can
kill any user processes but not the non-user process 'ghost cleanup'. Because
of this the db is in use and the detach cannot occur.
I realise what the process 'ghost cleanup' is there for - but surely it
shouldn't be in use permanently? (db options such as shrink db are not set on
this db). There any safe way to disable?
Try putting the database in single user mode by using the ALTER DATABSE with
SET SINGLE_USER mode option and then try detaching.
"Dunxz" wrote:

> hi
> i'm trying to detach a database. However i cant as the db is in use. I can
> kill any user processes but not the non-user process 'ghost cleanup'. Because
> of this the db is in use and the detach cannot occur.
> I realise what the process 'ghost cleanup' is there for - but surely it
> shouldn't be in use permanently? (db options such as shrink db are not set on
> this db). There any safe way to disable?
>
|||Thanks
"Pragya" wrote:
[vbcol=seagreen]
> Try putting the database in single user mode by using the ALTER DATABSE with
> SET SINGLE_USER mode option and then try detaching.
>
> "Dunxz" wrote:

detach db failed due to ghost cleanup

hi
i'm trying to detach a database. However i cant as the db is in use. I can
kill any user processes but not the non-user process 'ghost cleanup'. Becaus
e
of this the db is in use and the detach cannot occur.
I realise what the process 'ghost cleanup' is there for - but surely it
shouldn't be in use permanently? (db options such as shrink db are not set o
n
this db). There any safe way to disable'Try putting the database in single user mode by using the ALTER DATABSE with
SET SINGLE_USER mode option and then try detaching.
"Dunxz" wrote:

> hi
> i'm trying to detach a database. However i cant as the db is in use. I can
> kill any user processes but not the non-user process 'ghost cleanup'. Beca
use
> of this the db is in use and the detach cannot occur.
> I realise what the process 'ghost cleanup' is there for - but surely it
> shouldn't be in use permanently? (db options such as shrink db are not set
on
> this db). There any safe way to disable'
>|||Thanks
"Pragya" wrote:
[vbcol=seagreen]
> Try putting the database in single user mode by using the ALTER DATABSE wi
th
> SET SINGLE_USER mode option and then try detaching.
>
> "Dunxz" wrote:
>

detach db failed due to ghost cleanup

hi
i'm trying to detach a database. However i cant as the db is in use. I can
kill any user processes but not the non-user process 'ghost cleanup'. Because
of this the db is in use and the detach cannot occur.
I realise what the process 'ghost cleanup' is there for - but surely it
shouldn't be in use permanently? (db options such as shrink db are not set on
this db). There any safe way to disable'Try putting the database in single user mode by using the ALTER DATABSE with
SET SINGLE_USER mode option and then try detaching.
"Dunxz" wrote:
> hi
> i'm trying to detach a database. However i cant as the db is in use. I can
> kill any user processes but not the non-user process 'ghost cleanup'. Because
> of this the db is in use and the detach cannot occur.
> I realise what the process 'ghost cleanup' is there for - but surely it
> shouldn't be in use permanently? (db options such as shrink db are not set on
> this db). There any safe way to disable'
>|||Thanks
"Pragya" wrote:
> Try putting the database in single user mode by using the ALTER DATABSE with
> SET SINGLE_USER mode option and then try detaching.
>
> "Dunxz" wrote:
> > hi
> >
> > i'm trying to detach a database. However i cant as the db is in use. I can
> > kill any user processes but not the non-user process 'ghost cleanup'. Because
> > of this the db is in use and the detach cannot occur.
> >
> > I realise what the process 'ghost cleanup' is there for - but surely it
> > shouldn't be in use permanently? (db options such as shrink db are not set on
> > this db). There any safe way to disable'
> >
> >

Wednesday, March 7, 2012

desperately needing help with a Server Error

hey... i know NOTHING about sql server, .net framework or probably anything
else on sql monster. i am a novice access user, self-taught for about 9
months now and have only a basic understanding of vba. that being said, i
need some help...8)
i have a web site that i used to routinely visit for my job that performs
calculations for monthly car payments based on about 20 user-entered fields.
the problem is that about 3 months ago, it stopped working -- when you hit
the calculate button, the screen will seem to refresh as normal but either
nothing comes up in the monthly payment box, or if it's there, when you clic
k
the print button (and you HAVE to click this button as opposed to printing
right from internet explorer), the following error occurrs:
Server Error in '/' Application
.Net SqlClient Data Provider::Procedure'sp_MW_tblDeals_Updat
e_Main_Finance'
expects parameter '@.lp_mny_D_Selling_Price', which was not supplied.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Stack Trace: (i will repost this if you need to see it -- it's a little
lengthy)
Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NE
T
Version:1.1.4322.2300
Now, here's the weird bit. this is NOT a problem with the web site. it
isn't a problem with internet explorer (99%) or adobe. i know this because
if i log off of windows under my normal user id, and log in as admin, it wil
l
work. the admin id has minimal files, software, etc.. just like most pc's
i'm sure. i uninstalled the .net framework and reinstalled it using version
2.0 and i still get the same error identifying version 1.1.4322.2300. I hav
e
called tech support for this web site but they are useless, trying to have m
e
delete temp internet files and cookies and not much else. i have created a
new user id on this pc just to be able to use this web site (which despite m
y
slow pace at adressing this issue due to lack of company pressure has become
a hot-button for my bosses all of a sudden).
i apologize for the way in which this question was worded, if it even makes
sense at all. i tripped on to this board from access monster and thought it
a stroke of dumb luck, considering i had no idea of where to turn for help
with this. thanks for looking.
Greg
Message posted via http://www.droptable.com"kaosyeti@.comcast.net" <u15580@.uwe> wrote in message
news:6057106c0556f@.uwe...
> hey... i know NOTHING about sql server, .net framework or probably
> anything
> else on sql monster. i am a novice access user, self-taught for about 9
> months now and have only a basic understanding of vba. that being said, i
> need some help...8)
> i have a web site that i used to routinely visit for my job that performs
> calculations for monthly car payments based on about 20 user-entered
> fields.
> the problem is that about 3 months ago, it stopped working -- when you hit
> the calculate button, the screen will seem to refresh as normal but either
> nothing comes up in the monthly payment box, or if it's there, when you
> click
> the print button (and you HAVE to click this button as opposed to printing
> right from internet explorer), the following error occurrs:
>
> Server Error in '/' Application
> .Net SqlClient Data
> Provider::Procedure'sp_MW_tblDeals_Updat
e_Main_Finance'
> expects parameter '@.lp_mny_D_Selling_Price', which was not supplied.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about the error and where it originated in the code.
> Stack Trace: (i will repost this if you need to see it -- it's a little
> lengthy)
> Version Information: Microsoft .NET Framework Version:1.1.4322.2300;
> ASP.NET
> Version:1.1.4322.2300
>
<snip>
The error means that the stored procedure named
sp_MW_tblDeals_Update_Main_Finance requires a certain input parameter
(@.lp_mny_D_Selling_Price) before it will work. This parameter was not set in
the web sites calling code. I suspect someone has changed either the stored
procedure code by adding a new parameter, or changed the web site calling
code by removing a parameter. All I can suggest is that you send the error
message to the web site administrator.
Good Luck
Ron.|||you believe this despite the fact that it works under a different user id on
the same pc?
Ron wrote:
>[quoted text clipped - 28 lines]
><snip>
>The error means that the stored procedure named
>sp_MW_tblDeals_Update_Main_Finance requires a certain input parameter
>(@.lp_mny_D_Selling_Price) before it will work. This parameter was not set i
n
>the web sites calling code. I suspect someone has changed either the stored
>procedure code by adding a new parameter, or changed the web site calling
>code by removing a parameter. All I can suggest is that you send the error
>message to the web site administrator.
>Good Luck
>Ron.
>
Greg
Message posted via http://www.droptable.com|||"kaosyeti@.comcast.net via droptable.com" <u15580@.uwe> wrote in message
news:6067051e82365@.uwe...
> you believe this despite the fact that it works under a different user id
> on
> the same pc?
>
<snip>[vbcol=seagreen]
<snip>
Well, that is what the error from .net sql client means. The error message
suggests a problem with the web site, but from what you say, it sounds as if
you may be having a windows permissions problem. Have you tried deleting the
user you have trouble with, and re-adding it? Can you try using a different
machine? Failing that, perhaps you could post the web site address, so we
could try it from here.
I am unsure what else to suggest that would help you.
Regards,
Ron.

desperately needing help with a Server Error

hey... i know NOTHING about sql server, .net framework or probably anything
else on sql monster. i am a novice access user, self-taught for about 9
months now and have only a basic understanding of vba. that being said, i
need some help...8)
i have a web site that i used to routinely visit for my job that performs
calculations for monthly car payments based on about 20 user-entered fields.
the problem is that about 3 months ago, it stopped working -- when you hit
the calculate button, the screen will seem to refresh as normal but either
nothing comes up in the monthly payment box, or if it's there, when you click
the print button (and you HAVE to click this button as opposed to printing
right from internet explorer), the following error occurrs:
Server Error in '/' Application
.Net SqlClient Data Provider::Procedure'sp_MW_tblDeals_Update_Main_Finance'
expects parameter '@.lp_mny_D_Selling_Price', which was not supplied.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Stack Trace: (i will repost this if you need to see it -- it's a little
lengthy)
Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET
Version:1.1.4322.2300
Now, here's the weird bit. this is NOT a problem with the web site. it
isn't a problem with internet explorer (99%) or adobe. i know this because
if i log off of windows under my normal user id, and log in as admin, it will
work. the admin id has minimal files, software, etc.. just like most pc's
i'm sure. i uninstalled the .net framework and reinstalled it using version
2.0 and i still get the same error identifying version 1.1.4322.2300. I have
called tech support for this web site but they are useless, trying to have me
delete temp internet files and cookies and not much else. i have created a
new user id on this pc just to be able to use this web site (which despite my
slow pace at adressing this issue due to lack of company pressure has become
a hot-button for my bosses all of a sudden).
i apologize for the way in which this question was worded, if it even makes
sense at all. i tripped on to this board from access monster and thought it
a stroke of dumb luck, considering i had no idea of where to turn for help
with this. thanks for looking.
--
Greg
Message posted via http://www.sqlmonster.com"kaosyeti@.comcast.net" <u15580@.uwe> wrote in message
news:6057106c0556f@.uwe...
> hey... i know NOTHING about sql server, .net framework or probably
> anything
> else on sql monster. i am a novice access user, self-taught for about 9
> months now and have only a basic understanding of vba. that being said, i
> need some help...8)
> i have a web site that i used to routinely visit for my job that performs
> calculations for monthly car payments based on about 20 user-entered
> fields.
> the problem is that about 3 months ago, it stopped working -- when you hit
> the calculate button, the screen will seem to refresh as normal but either
> nothing comes up in the monthly payment box, or if it's there, when you
> click
> the print button (and you HAVE to click this button as opposed to printing
> right from internet explorer), the following error occurrs:
>
> Server Error in '/' Application
> .Net SqlClient Data
> Provider::Procedure'sp_MW_tblDeals_Update_Main_Finance'
> expects parameter '@.lp_mny_D_Selling_Price', which was not supplied.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about the error and where it originated in the code.
> Stack Trace: (i will repost this if you need to see it -- it's a little
> lengthy)
> Version Information: Microsoft .NET Framework Version:1.1.4322.2300;
> ASP.NET
> Version:1.1.4322.2300
>
<snip>
The error means that the stored procedure named
sp_MW_tblDeals_Update_Main_Finance requires a certain input parameter
(@.lp_mny_D_Selling_Price) before it will work. This parameter was not set in
the web sites calling code. I suspect someone has changed either the stored
procedure code by adding a new parameter, or changed the web site calling
code by removing a parameter. All I can suggest is that you send the error
message to the web site administrator.
Good Luck
Ron.|||you believe this despite the fact that it works under a different user id on
the same pc?
Ron wrote:
>> hey... i know NOTHING about sql server, .net framework or probably
>> anything
>[quoted text clipped - 28 lines]
>> ASP.NET
>> Version:1.1.4322.2300
><snip>
>The error means that the stored procedure named
>sp_MW_tblDeals_Update_Main_Finance requires a certain input parameter
>(@.lp_mny_D_Selling_Price) before it will work. This parameter was not set in
>the web sites calling code. I suspect someone has changed either the stored
>procedure code by adding a new parameter, or changed the web site calling
>code by removing a parameter. All I can suggest is that you send the error
>message to the web site administrator.
>Good Luck
>Ron.
>
--
Greg
Message posted via http://www.sqlmonster.com|||"kaosyeti@.comcast.net via SQLMonster.com" <u15580@.uwe> wrote in message
news:6067051e82365@.uwe...
> you believe this despite the fact that it works under a different user id
> on
> the same pc?
>
<snip>
>>The error means that the stored procedure named
>>sp_MW_tblDeals_Update_Main_Finance requires a certain input parameter
>>(@.lp_mny_D_Selling_Price) before it will work. This parameter was not set
>>in
>>the web sites calling code. I suspect someone has changed either the
>>stored
>>procedure code by adding a new parameter, or changed the web site calling
>>code by removing a parameter. All I can suggest is that you send the error
>>message to the web site administrator.
<snip>
Well, that is what the error from .net sql client means. The error message
suggests a problem with the web site, but from what you say, it sounds as if
you may be having a windows permissions problem. Have you tried deleting the
user you have trouble with, and re-adding it? Can you try using a different
machine? Failing that, perhaps you could post the web site address, so we
could try it from here.
I am unsure what else to suggest that would help you.
Regards,
Ron.

Saturday, February 25, 2012

designing data entry form with scope_identity .....help

hi i'm thinking of putting 3 texboxes for the user to enter their previous work exp which look something like this:

experience
First job.textbox
Secondjob.textbox
Thirdjob.textbox

i'm kinda confuse on with the database. The 3 tables required means i will hav 3 sqldatasource ...
which i didin't quite get it right with the Secondjob table.I try using identity_scope() and @.@.identity to get the ID from my first table(personel)
which is autogenerate number. It works fine with Firstjob table but not secondjob table.

Generated error message:

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 6

The INSERT statement conflicted with the FOREIGN KEY constraint "Personnel_Secondjob". The conflict occurred in database "test", table "dbo.Personnel", column 'IDPersonnel'.

The statement has been terminated.

INSERTINTO dbo.Personnel

(Name)

VALUES

('Jon')

insert

into Firstjob(Designation, Employer, YearWorking, IDPersonnel)

values

('Teachier,'Gam','7thn',scope_identity())

insert

into Secondjob(Designation, Employer, YearWorking, IDPersonnel)

values

('Musician','KKSB','8thn',scope_identity())

any suggestions...

Declare @.id1int, @.id 2intINSERT INTO dbo.Personnel (Name)VALUES ('Jon')SELECT @.ID1 = SCOPE_IDENTITY()insert into Firstjob(Designation, Employer, YearWorking, IDPersonnel)values ('Teachier', 'Gam', '7thn', @.ID1) SELECT @.id2 = SCOPE_IDENTITY() insert into Secondjob(Designation, Employer, YearWorking, IDPersonnel) values ('Musician', 'KKSB', '8thn', @.ID2)

|||

it generate an error:

Msg 547, Level 16, State 0, Line 9

The INSERT statement conflicted with the FOREIGN KEY constraint "Personnel_PnglmnKerja2". The conflict occurred in database "test", table "dbo.Personnel", column 'IDPersonnel'.

The statement has been terminated.

i changed line 5 to
SELECT @.id2= @.ID1

or reduce the code to

SELECT @.ID1 = SCOPE_IDENTITY()
insert into Firstjob(Designation, Employer, YearWorking, IDPersonnel)values ('Teachier', 'Gam', '7thn', @.ID1)
insert into Secondjob(Designation, Employer, YearWorking, IDPersonnel) values ('Musician', 'KKSB', '8thn', @.ID1)

and it worked... thanx for the idea i'm just beginner,learning mssql through forums & ebook. I really appreciate it

do u foresee any problem if many people try to insert their data concurently?10-20 people at the same time

Thursday, March 29, 2012

Determine what User Account SQL Server is running under

Determine what User Account SQL Server is running under

Determine what User Account SQL Server is running under

Sunday, March 25, 2012

Determine date when each user last accessed the database

Determine All User Databases

Determine All User Databases

Determine All User Databases

Thursday, March 22, 2012

detecting whether all items are selected in a multi-value paramete

Detecting when an user connects to a database

detecting Update Conflict

Detecting the network UserID

Wednesday, March 21, 2012

Detecting a change in a particular field in a database

Monday, March 19, 2012

Details section does not show all details

Detailed User Access Rights

Friday, March 9, 2012

detach db failed due to ghost cleanup

detach db failed due to ghost cleanup

detach db failed due to ghost cleanup

Wednesday, March 7, 2012

desperately needing help with a Server Error

desperately needing help with a Server Error

Saturday, February 25, 2012

designing data entry form with scope_identity .....help

Determining Security

Blog Archive

About Me