I need to block some rows from being updated using mssql 2000.
this is because some users are changing data after the product has been
factured.
The VB program does not let any body to change the data but some advanced
users have done it directly to the database.
If some one can help me I will be very thankful
Regards, LinaWhy do those "advanced users" even have access to the database?
They should not.|||Given that you need to solve the problem as it exists, the simplest approach
would be to put a trigger on the table that checks to see if one of the
"non-alterable" records is being updated and roll back the transaction. You
might also want to log who is updating the row for future personnel related
actions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<Octavius@.gmail.com> wrote in message
news:1135822838.009333.252160@.g14g2000cwa.googlegroups.com...
> Why do those "advanced users" even have access to the database?
> They should not.
>|||Thanks a lot, Lina
"Roger Wolter[MSFT]" wrote:
> Given that you need to solve the problem as it exists, the simplest approa
ch
> would be to put a trigger on the table that checks to see if one of the
> "non-alterable" records is being updated and roll back the transaction. Y
ou
> might also want to log who is updating the row for future personnel relate
d
> actions.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> <Octavius@.gmail.com> wrote in message
> news:1135822838.009333.252160@.g14g2000cwa.googlegroups.com...
>
>|||You could try using the APP_NAME function. Run SQL Query Analyzer, and
execute sp_who2 while some users are connected. Look under the ProgramName
column to see what the name of your application is as it appears to the
server.
Then put some logic into your stored procedures, or into an INSTEAD OF
trigger on your table(s)
IF APP_NAME() <> 'YourApp'
--Sorry Charlie!
If your application doesn't report a ProgramName, but the "advanced users"
are using something like Access or a command line tool that does report a
ProgramName, you could block it with something like
IF APP_NAME() IN ('Microsoft Access', 'osql')
"Lina Manjarres" wrote:
> I need to block some rows from being updated using mssql 2000.
> this is because some users are changing data after the product has been
> factured.
> The VB program does not let any body to change the data but some advanced
> users have done it directly to the database.
> If some one can help me I will be very thankful
> Regards, Lina|||Thanks a lot Mark
I used this:
IF EXISTS(SELECT * FROM inserted WHERE facturado_Cliente = 1)
Begin
ROLLBACK TRAN
End
and i had a problem wit it. After the facturado_Cliente where changed to 0
again, it did not let me change the row any more. I mean, I couldn't make it
1 again, it allways rolled back.
So I will try what you suggest me, because it sound perfect. Is it posible
to do so, but with the HostName?
Thanks a lot, Lina
"Mark Williams" wrote:
> You could try using the APP_NAME function. Run SQL Query Analyzer, and
> execute sp_who2 while some users are connected. Look under the ProgramName
> column to see what the name of your application is as it appears to the
> server.
> Then put some logic into your stored procedures, or into an INSTEAD OF
> trigger on your table(s)
> IF APP_NAME() <> 'YourApp'
> --Sorry Charlie!
> If your application doesn't report a ProgramName, but the "advanced users"
> are using something like Access or a command line tool that does report a
> ProgramName, you could block it with something like
> IF APP_NAME() IN ('Microsoft Access', 'osql')
> "Lina Manjarres" wrote:
>|||The HOST_NAME() function will return the hostname of the system associated
with the connection, which you could use in a WHERE clause, but it is
probably not a good idea. You would have to block individual machine names,
and all someone would have to do in order to defeat your filter is
move to another machine , or
change their machine name
It would probably work out better to put in a filter condition based on
APP_NAME() . Does your application show a ProgramName value when you run
sp_who2 ? If it does, filter the updates based on the ProgramName.
"Lina Manjarres" wrote:
> Thanks a lot Mark
> I used this:
> IF EXISTS(SELECT * FROM inserted WHERE facturado_Cliente = 1)
> Begin
> ROLLBACK TRAN
> End
> and i had a problem wit it. After the facturado_Cliente where changed to 0
> again, it did not let me change the row any more. I mean, I couldn't make
it
> 1 again, it allways rolled back.
> So I will try what you suggest me, because it sound perfect. Is it posible
> to do so, but with the HostName?
> Thanks a lot, Lina
> "Mark Williams" wrote:
>|||Thanks a lot Mark.
One more question.
What happend when I have several SQL replicating. Is there any problem wit i
t?
"Mark Williams" wrote:
> The HOST_NAME() function will return the hostname of the system associated
> with the connection, which you could use in a WHERE clause, but it is
> probably not a good idea. You would have to block individual machine names
,
> and all someone would have to do in order to defeat your filter is
> move to another machine , or
> change their machine name
> It would probably work out better to put in a filter condition based on
> APP_NAME() . Does your application show a ProgramName value when you run
> sp_who2 ? If it does, filter the updates based on the ProgramName.
> "Lina Manjarres" wrote:
>|||If the table is involved in replication, you can place the application
checking logic into an INSTEAD OF trigger, and create the trigger with the
NOT FOR REPLICATION option. This will prevent the trigger from firing when a
replication operation modifies the table. See books online for more details.
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers. Thanks!
"Lina Manjarres" wrote:
> Thanks a lot Mark.
> One more question.
> What happend when I have several SQL replicating. Is there any problem wit
it?
> "Mark Williams" wrote:
>|||Dear Mark
I am using this:
AFTER UPDATE NOT FOR REPLICATION
Is it ok or do I have to use Instead of?
Thanks a lot, Lina
"Mark Williams" wrote:
> If the table is involved in replication, you can place the application
> checking logic into an INSTEAD OF trigger, and create the trigger with the
> NOT FOR REPLICATION option. This will prevent the trigger from firing when
a
> replication operation modifies the table. See books online for more detail
s.
> --
> If you posted to this forum through TechNet, and you found my answers
> helpful, please mark them as answers. Thanks!
>
> "Lina Manjarres" wrote:
>
Showing posts with label mssql. Show all posts
Showing posts with label mssql. Show all posts
Wednesday, March 7, 2012
Tuesday, February 14, 2012
Biztalk read and write access to the master database.
My fellow administrator tells me that his biztalk account needs read and
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
--
Best regards
MikaelMikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just if
the biztalk application itself needs this access to run?
--
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> You have to ask him why does he want to access? What process/s does he run ?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> > My fellow administrator tells me that his biztalk account needs read and
> > write permissions to the master database of out MSSQL 2000 Enterprice
> > edition, running on a windows 2000 advanced server.
> >
> > Our biztalk developer does not remember that there has been an issue, so
> > he
> > is not doing anything specific on the masters database, in the jobs he is
> > running.
> >
> > Is it for real that biztalk needs this access?
> >
> > --
> > Best regards
> >
> > Mikael
>
>|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
>> Mikael
>> You have to ask him why does he want to access? What process/s does he
>> run ?
>>
>>
>> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
>> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>> > My fellow administrator tells me that his biztalk account needs read
>> > and
>> > write permissions to the master database of out MSSQL 2000 Enterprice
>> > edition, running on a windows 2000 advanced server.
>> >
>> > Our biztalk developer does not remember that there has been an issue,
>> > so
>> > he
>> > is not doing anything specific on the masters database, in the jobs he
>> > is
>> > running.
>> >
>> > Is it for real that biztalk needs this access?
>> >
>> > --
>> > Best regards
>> >
>> > Mikael
>>|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I canâ't.
--
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
> > Yes, it has been a while since they did the installation and he just
> > remembers that it needs the permissions, but not why. My question is just
> > if
> > the biztalk application itself needs this access to run?
> >
> > --
> > Best regards
> >
> > Mikael
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Mikael
> >>
> >> You have to ask him why does he want to access? What process/s does he
> >> run ?
> >>
> >>
> >>
> >>
> >> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> >> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> >> > My fellow administrator tells me that his biztalk account needs read
> >> > and
> >> > write permissions to the master database of out MSSQL 2000 Enterprice
> >> > edition, running on a windows 2000 advanced server.
> >> >
> >> > Our biztalk developer does not remember that there has been an issue,
> >> > so
> >> > he
> >> > is not doing anything specific on the masters database, in the jobs he
> >> > is
> >> > running.
> >> >
> >> > Is it for real that biztalk needs this access?
> >> >
> >> > --
> >> > Best regards
> >> >
> >> > Mikael
> >>
> >>
> >>
>
>
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
--
Best regards
MikaelMikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just if
the biztalk application itself needs this access to run?
--
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> You have to ask him why does he want to access? What process/s does he run ?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> > My fellow administrator tells me that his biztalk account needs read and
> > write permissions to the master database of out MSSQL 2000 Enterprice
> > edition, running on a windows 2000 advanced server.
> >
> > Our biztalk developer does not remember that there has been an issue, so
> > he
> > is not doing anything specific on the masters database, in the jobs he is
> > running.
> >
> > Is it for real that biztalk needs this access?
> >
> > --
> > Best regards
> >
> > Mikael
>
>|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
>> Mikael
>> You have to ask him why does he want to access? What process/s does he
>> run ?
>>
>>
>> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
>> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>> > My fellow administrator tells me that his biztalk account needs read
>> > and
>> > write permissions to the master database of out MSSQL 2000 Enterprice
>> > edition, running on a windows 2000 advanced server.
>> >
>> > Our biztalk developer does not remember that there has been an issue,
>> > so
>> > he
>> > is not doing anything specific on the masters database, in the jobs he
>> > is
>> > running.
>> >
>> > Is it for real that biztalk needs this access?
>> >
>> > --
>> > Best regards
>> >
>> > Mikael
>>|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I canâ't.
--
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
> > Yes, it has been a while since they did the installation and he just
> > remembers that it needs the permissions, but not why. My question is just
> > if
> > the biztalk application itself needs this access to run?
> >
> > --
> > Best regards
> >
> > Mikael
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Mikael
> >>
> >> You have to ask him why does he want to access? What process/s does he
> >> run ?
> >>
> >>
> >>
> >>
> >> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> >> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> >> > My fellow administrator tells me that his biztalk account needs read
> >> > and
> >> > write permissions to the master database of out MSSQL 2000 Enterprice
> >> > edition, running on a windows 2000 advanced server.
> >> >
> >> > Our biztalk developer does not remember that there has been an issue,
> >> > so
> >> > he
> >> > is not doing anything specific on the masters database, in the jobs he
> >> > is
> >> > running.
> >> >
> >> > Is it for real that biztalk needs this access?
> >> >
> >> > --
> >> > Best regards
> >> >
> >> > Mikael
> >>
> >>
> >>
>
>
Biztalk read and write access to the master database.
My fellow administrator tells me that his biztalk account needs read and
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a Windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
Best regards
MikaelMikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a Windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just i
f
the biztalk application itself needs this access to run?
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> You have to ask him why does he want to access? What process/s does he run
?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>
>|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...[vbcol=seagreen]
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
>|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I can’t.
--
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
>
>
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a Windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
Best regards
MikaelMikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a Windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just i
f
the biztalk application itself needs this access to run?
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> You have to ask him why does he want to access? What process/s does he run
?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>
>|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...[vbcol=seagreen]
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
>|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I can’t.
--
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
>
>
Biztalk read and write access to the master database.
My fellow administrator tells me that his biztalk account needs read and
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
Best regards
Mikael
Mikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael
|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just if
the biztalk application itself needs this access to run?
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> You have to ask him why does he want to access? What process/s does he run ?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>
>
|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...[vbcol=seagreen]
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I can’t.
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
>
>
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
Best regards
Mikael
Mikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael
|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just if
the biztalk application itself needs this access to run?
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> You have to ask him why does he want to access? What process/s does he run ?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>
>
|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...[vbcol=seagreen]
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I can’t.
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
>
>
Sunday, February 12, 2012
Bitwise question -
I am new to bitwise thing in MSSQL.
Let's suppose there's a table of favorite foods
insert int fav_foods(food_name,bitwiseVal)
values('Pasta',1)
insert int fav_foods(food_name,bitwiseVal)
values('Chicken',2)
insert int fav_foods(food_name,bitwiseVal)
values('Beef',4)
insert int fav_foods(food_name,bitwiseVal)
values('Fish',8)
insert int fav_foods(food_name,bitwiseVal)
values('Pork',16)
How do I write query to find people who selected more than one item and
selected items from "Pasta, Chicken, Beef, Pork"(but not fish)?
I hope my question is not confusing....Bostonasian wrote:
> I am new to bitwise thing in MSSQL.
> Let's suppose there's a table of favorite foods
> insert int fav_foods(food_name,bitwiseVal)
> values('Pasta',1)
> insert int fav_foods(food_name,bitwiseVal)
> values('Chicken',2)
> insert int fav_foods(food_name,bitwiseVal)
> values('Beef',4)
> insert int fav_foods(food_name,bitwiseVal)
> values('Fish',8)
> insert int fav_foods(food_name,bitwiseVal)
> values('Pork',16)
> How do I write query to find people who selected more than one item and
> selected items from "Pasta, Chicken, Beef, Pork"(but not fish)?
> I hope my question is not confusing....
Your question isn't confusing but your design decision is. Why use
bitwise on something like this? If you were to use proper table design
this query would be trivial (and fast).
Zach|||I tried to simply the example as much as possible, that's probably why
it didn't look that neccesary to build table like this.
I actually have survey data. Survey answer includes text, single select
multiple choice and multi-select multiple choice.
In answered data table, I currently have schema like following :
customer | question_id | answer
---------------
John | 1 | Pasta
John | 1 | Beef
John | 1 | Chicken
John | 1 | Pork
And I've got 2.4 million customers to manage, so I thought it'd save
some rows by using bitwise to reduce row numbers to one.|||Bostonasian wrote:
> I tried to simply the example as much as possible, that's probably why
> it didn't look that neccesary to build table like this.
> I actually have survey data. Survey answer includes text, single select
> multiple choice and multi-select multiple choice.
> In answered data table, I currently have schema like following :
> customer | question_id | answer
> ---------------
> John | 1 | Pasta
> John | 1 | Beef
> John | 1 | Chicken
> John | 1 | Pork
> And I've got 2.4 million customers to manage, so I thought it'd save
> some rows by using bitwise to reduce row numbers to one.
What you save in rows (i.e. disk space, which is cheap), you'll likely
lose in readability, mainainability, performance and standardization.
Search out one of Joe Celko's rants about thinking like a procedural
programmer and not a SQL/set based programmer because I think that's the
problem here.
Zach|||> And I've got 2.4 million customers to manage, so I thought it'd save
> some rows by using bitwise to reduce row numbers to one.
I'll bet that disk space is much cheaper than the cost of the time you'll
spend fixing up a kludge like that :-)
Try this:
CREATE TABLE Foods (customer_id INTEGER NOT NULL REFERENCES Customers
(customer_id), food INTEGER NOT NULL REFERENCES Foods (food), PRIMARY KEY
(customer_id, food))
SELECT customer_id
FROM Foods
WHERE food IN (1,2,3,4,5) /* Pasta,Chicken,Beef,Pork,Fish */
GROUP BY customer_id
HAVING COUNT(CASE WHEN food IN (1,2,3,4) THEN 1 END) = COUNT(*)
/* Everything except fish */
--
David Portas
SQL Server MVP
--|||Bostonasian (axkixx@.gmail.com) writes:
> I am new to bitwise thing in MSSQL.
> Let's suppose there's a table of favorite foods
> insert int fav_foods(food_name,bitwiseVal)
> values('Pasta',1)
> insert int fav_foods(food_name,bitwiseVal)
> values('Chicken',2)
> insert int fav_foods(food_name,bitwiseVal)
> values('Beef',4)
> insert int fav_foods(food_name,bitwiseVal)
> values('Fish',8)
> insert int fav_foods(food_name,bitwiseVal)
> values('Pork',16)
> How do I write query to find people who selected more than one item and
> selected items from "Pasta, Chicken, Beef, Pork"(but not fish)?
> I hope my question is not confusing....
SELECT *
FROM tbl
WHERE fav_food & (SELECT SUM(bitwiseVal)
FROM fav_foods
WHERE food_name IN ('Pasta', 'Chicken', 'Beef', 'Pork'))
But as pointed out by others, this is a poor design. You may
save disk space, but if you need to find all that selected Chicken,
you will find that you cannot have an index on bit in an integer
column, so you get awful performance.
Look at David's query, and use that instead of the above.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bostonasian wrote:
> I tried to simply the example as much as possible, that's probably why
> it didn't look that neccesary to build table like this.
> I actually have survey data. Survey answer includes text, single select
> multiple choice and multi-select multiple choice.
> In answered data table, I currently have schema like following :
> customer | question_id | answer
> ---------------
> John | 1 | Pasta
> John | 1 | Beef
> John | 1 | Chicken
> John | 1 | Pork
> And I've got 2.4 million customers to manage, so I thought it'd save
> some rows by using bitwise to reduce row numbers to one.
Hi Bostonasian,
There is no need to denormalize or use bitwise operations for this. IMO,
bitwise operations are not suitable for this problem.
The database does not have to grow very fast. If you normalize all the
way through, you would get a Customers (reference) table, a Questions
(reference) table and a Answers (reference) table. All these reference
tables can have short keys, which you use in your CustomerAnswers (data)
table. If you have fewer than 64000 customers, fewer than 256 questions
and fewer than 256 (fixed) answers per question, then each row in
CustomerAnswers would be just 2+1+1 = 4 bytes (excluding the free format
text answers).
Your schema could look something like this:
CREATE TABLE Customers(CustomerID smallint PRIMARY KEY, Name
nvarchar(100))
CREATE TABLE Questions(QuestionID tinyint PRIMARY KEY, Question
nvarchar(3000))
CREATE TABLE Answers (QuestionID tinyint, AnswerID tinyint, Answer
nvarchar(200),PRIMARY KEY (QuestionID,AnswerID))
CREATE TABLE CustomerAnswers
(CustomerID smallint REFERENCES Customers
,QuestionID tinyint REFERENCES Questions
,AnswerID tinyint
,TextAnswer nvarchar(2000)
,PRIMARY KEY (CustomerID,QuestionID,AnswerID)
,FOREIGN KEY (QuestionID,AnswerID) REFERENCES Answers
)
Hope this helps,
Gert-Jan
Bitwise
I don't know what's bitwise....
How can I use in MSSQL , PHP ?
If you know please teach me...
How can I use in MSSQL , PHP ?
If you know please teach me...
ThanksThere is a great resource for all kind of learnings. It is called Wikipedia.
Take a look at the Bitwise Operation article there.
SQL Server syntax:
"|" - Bitwise OR
"&" - Bitwise AND
"^" - Bitwise Exclusive OR
Friday, February 10, 2012
Bit datatypes physical storage
Can anyone explain to me how a column defined with a "bit null" datatype is physically stored in MSSQL? Is it stored like a "tinyint null" physically? In other words, how many bytes on the row on the page does a "bit null" datatype consume (assuming a non-null value 0, or 1 is the current value).
Is there any good documentation about the physical storage layout for a data page?
Thanks -From SQL BOL: Microsoft SQL Server optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.A Null column does not consume any storage. SQL Server stores Nullable columns only if they exist. That's good unless they change frequently, then it can be bad.
In general, avoid Nulls as much as you possibly can for two reasons: Data Integrity
Reduced Page Splits|||i won't question point 2 because it seems to be another instance of DBA arcana (with all of which i am not familiar), but point 1 cannot go unchallenged
are you suggesting 6th normal form? because i have a real problem with that
and if not, then what are you suggesting? zeros instead of nulls in numeric columns, and empty strings in character columns? how does this improve data integrity? if anything, this decreases data integrity
or were you specifically referring to null bit fields only? because that wasn't clear (and doesn't make a lot of sense either)|||Ok, if I have 2 bit fields which are then stored in the same byte, and the first field is null, there must be a way that MSSQL knows that without thinking that the first bit of that byte which stores the bit fields is the first bit column value. I'm thinking that there is an offset table on the row, or some kind of null "indicator". Which is it? An offset table or an indicator?
If an offset table, then how are bit fields represented in it? If an indicator, then whoa, I'm lost on why I would choose such a datatype.|||Thanks for the challenges, r937. I'm not prone to making sense. My point is if a value is expected, then don't allow Nulls.
How often do you see DDL posted and all columns but the primary key are Nullable (just be happy there's a primary key, right?). I see it too often.
I like the aracana misconception - no secrets here as I'm a recovering pragmatist. However, I quesiton my ability to explain it succinctly and I'm burnt out on searching MSN for links that do the job for me.
But, I'll try for posterity.
Page Splits occur when SQL Server has to reorganize data on a page and Page Splits are one of the most expensive operations that SQL Server has to do. Updating a value from Null to something that Is Not Null may cause a Page Split on a page that is > 50% full (I'll leave the research up to you for determing the conditions under which SQL Server splits a page).
For a heavy OLTP system, it gets expensive. Thus, if you force users to enter values that are needed, you reduce the probability they'll have to go back and add the values later, thus reducing the probability of a Page Split.
Finally, my working mode is enterprise applications. So, I answer questions with the assumption the answer should apply to an enterprise architecture. I'm often amused how my assumptions lead to useless typing, but hey, I'll keep trying.
Which is it? An offset table or an indicator? Offset. Which led Microsoft to disallow Null bits in the beginning. They succumbed to pressure. Your seeking internals knowledge, and Microsoft has been relaxing their rules around providing it, but I'm not sure they've gone into any depth regarding offsets. I'd tell you more but I'd have to shoot you.
Hope this helps. I gotta Split.|||My point is if a value is expected, then don't allow Nulls.well, sure, if one knows what one's doing (although i would change the word "expected" to "required")
but that's a lot different than "avoid Nulls as much as you possibly can"
the latter advice will cause some people to use DEFAULT 0 and DEFAULT '' needlessly, wantonly, and, in most cases, flat out incorrectly
as far as all columns but PK being nullable, i myself like that idea :)
it allows the referential integrity action "ON INSERT CASCADE [to parent]" (yes, i made that up) whereby if you try to insert a child row with a foreign key for a parent that doesn't exist yet, it takes the foreign key value and uses it to insert a row with that value of the primary key in the parent table (so all other parent columns are therefore assigned NULL or get their defaults) before inserting the child row
would save writing a trigger
and don't say you haven't seen an application where this is a valid business rule ;)|||I was gonna leave this alone, but then I'm a pedant.
The world in which I live requires parents to exist for a child to exist. Now, that could just be my world and with the advent of cloning, it may quickly be invalidated and I'll have to move to a different world.
If you do the math on your interesting CASCADE [to parent] you will find that the parent insert should require knowing all the parent's attributes to do the insert. My interpretation of your proposition is that parent attributes are irrelevant. They're relevant in my world.
Where ya gonna put 'em and how ya gonna get them if you just ignored them?
I have not seen a valid business for which this type of an application would rule. :p|||Avoid nulls as much as you can?
Bullcrap! The value NULL provides information that simply cannot be provided by a default value, that being that absence of information. If a value is unknown, then the last thing I would want would be to have a value place their by default.
I'd classify that as bad data in a heartbeat.
Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."
There is nothing wrong with a table schema that allows many columns to be null. Are you going to prevent a user from entering a record with incomplete information just to satisfy the obsesive compulsions of an overly anal-retentive dba?
Ultimately, the business rules drive the design. If they do not require a value, then allow NULL.|||Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."Who's being anal-retentive?
Expecting values implies they are required. Saying as much as you can implies you are competent enough to know when you can't. I'll switch to explicit mode to avoid illogical conclusions.
I didn't say completely avoid Nulls, I said avoid Nulls as much as you can.
I agree - if a value is not required, then allow Null. But, avoid Nulls as much as you can.
I didn't say ignore them. I didn't say they don't mean anything even though their meaning is unknown. I said avoid Nulls as much as you can.
I didn't say mislead others with default values. I said avoid Nulls as much as you can.
If a value is expected, then don't allow Nulls. That is reality.
That's my story and I'm sticking to it.
Avoid Nulls as much as you can.|||Man, I love this. MaxA, - you rule!!!!
But looking back at my own designs, - I can tell thatt the better I understood the business requirements, the more complex but at the same time cleaner the final design looked like. And ironicly I did notice that fewer nulls were present. Offloading NULLable columns to a separate table always leads to more complex queries, but a LEFT OUTER JOIN is a LEFT OUTER JOIN, and if PKs/FKs are properly indexed, - you'll see a better performance as well...But that's just me ;)
EDITED: By better performance I mean that your table will naturally be theoretically wider when nullable columns are present, and at the time of optimizing your SELECT you have 2 choices: avoid selecting nullable columns, or include them into existing indexes to avoid bookmark lookups. But if you offload those columns into a separate table that will only contain records where the column is not null, it'll result in fewer records to go through and all indexes are used.|||Avoid NULLS as much as you can? That is just a tautology. If you are saying not to allow NULLS when a value is required, then I completely agree with you.
How about this statement: "Avoid making a field required as much as you can."
I didn't say completely avoid Non-Nulls, I said avoid Non-Nulls as much as you can.
I agree - if a value is required, then disallow Null. But, avoid Non-Nulls as much as you can.
I didn't say ignore Non-Nulls. I didn't say they should be used even when a value is unknown. I said avoid Non-Nulls as much as you can.
If a value is not required, then allow Non-Nulls. That is reality.
That's my story and I'm sticking to it.
Avoid Non-Nulls as much as you can.|||Oh that's just plagiarism, Mr. Lindman, come up with your own style! ;)|||Plagiarism IS a style. Just look at Hollywood or the Grammy Awards! ;)
"Plagiarism is the sincerest form of flattery".
I forget who originally said that...|||Apparently Charles Caleb Colton:
http://www.quotationspage.com/quote/27484.html
I just don't think he has ever gotten enough credit for his work, do you?
Is there any good documentation about the physical storage layout for a data page?
Thanks -From SQL BOL: Microsoft SQL Server optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.A Null column does not consume any storage. SQL Server stores Nullable columns only if they exist. That's good unless they change frequently, then it can be bad.
In general, avoid Nulls as much as you possibly can for two reasons: Data Integrity
Reduced Page Splits|||i won't question point 2 because it seems to be another instance of DBA arcana (with all of which i am not familiar), but point 1 cannot go unchallenged
are you suggesting 6th normal form? because i have a real problem with that
and if not, then what are you suggesting? zeros instead of nulls in numeric columns, and empty strings in character columns? how does this improve data integrity? if anything, this decreases data integrity
or were you specifically referring to null bit fields only? because that wasn't clear (and doesn't make a lot of sense either)|||Ok, if I have 2 bit fields which are then stored in the same byte, and the first field is null, there must be a way that MSSQL knows that without thinking that the first bit of that byte which stores the bit fields is the first bit column value. I'm thinking that there is an offset table on the row, or some kind of null "indicator". Which is it? An offset table or an indicator?
If an offset table, then how are bit fields represented in it? If an indicator, then whoa, I'm lost on why I would choose such a datatype.|||Thanks for the challenges, r937. I'm not prone to making sense. My point is if a value is expected, then don't allow Nulls.
How often do you see DDL posted and all columns but the primary key are Nullable (just be happy there's a primary key, right?). I see it too often.
I like the aracana misconception - no secrets here as I'm a recovering pragmatist. However, I quesiton my ability to explain it succinctly and I'm burnt out on searching MSN for links that do the job for me.
But, I'll try for posterity.
Page Splits occur when SQL Server has to reorganize data on a page and Page Splits are one of the most expensive operations that SQL Server has to do. Updating a value from Null to something that Is Not Null may cause a Page Split on a page that is > 50% full (I'll leave the research up to you for determing the conditions under which SQL Server splits a page).
For a heavy OLTP system, it gets expensive. Thus, if you force users to enter values that are needed, you reduce the probability they'll have to go back and add the values later, thus reducing the probability of a Page Split.
Finally, my working mode is enterprise applications. So, I answer questions with the assumption the answer should apply to an enterprise architecture. I'm often amused how my assumptions lead to useless typing, but hey, I'll keep trying.
Which is it? An offset table or an indicator? Offset. Which led Microsoft to disallow Null bits in the beginning. They succumbed to pressure. Your seeking internals knowledge, and Microsoft has been relaxing their rules around providing it, but I'm not sure they've gone into any depth regarding offsets. I'd tell you more but I'd have to shoot you.
Hope this helps. I gotta Split.|||My point is if a value is expected, then don't allow Nulls.well, sure, if one knows what one's doing (although i would change the word "expected" to "required")
but that's a lot different than "avoid Nulls as much as you possibly can"
the latter advice will cause some people to use DEFAULT 0 and DEFAULT '' needlessly, wantonly, and, in most cases, flat out incorrectly
as far as all columns but PK being nullable, i myself like that idea :)
it allows the referential integrity action "ON INSERT CASCADE [to parent]" (yes, i made that up) whereby if you try to insert a child row with a foreign key for a parent that doesn't exist yet, it takes the foreign key value and uses it to insert a row with that value of the primary key in the parent table (so all other parent columns are therefore assigned NULL or get their defaults) before inserting the child row
would save writing a trigger
and don't say you haven't seen an application where this is a valid business rule ;)|||I was gonna leave this alone, but then I'm a pedant.
The world in which I live requires parents to exist for a child to exist. Now, that could just be my world and with the advent of cloning, it may quickly be invalidated and I'll have to move to a different world.
If you do the math on your interesting CASCADE [to parent] you will find that the parent insert should require knowing all the parent's attributes to do the insert. My interpretation of your proposition is that parent attributes are irrelevant. They're relevant in my world.
Where ya gonna put 'em and how ya gonna get them if you just ignored them?
I have not seen a valid business for which this type of an application would rule. :p|||Avoid nulls as much as you can?
Bullcrap! The value NULL provides information that simply cannot be provided by a default value, that being that absence of information. If a value is unknown, then the last thing I would want would be to have a value place their by default.
I'd classify that as bad data in a heartbeat.
Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."
There is nothing wrong with a table schema that allows many columns to be null. Are you going to prevent a user from entering a record with incomplete information just to satisfy the obsesive compulsions of an overly anal-retentive dba?
Ultimately, the business rules drive the design. If they do not require a value, then allow NULL.|||Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."Who's being anal-retentive?
Expecting values implies they are required. Saying as much as you can implies you are competent enough to know when you can't. I'll switch to explicit mode to avoid illogical conclusions.
I didn't say completely avoid Nulls, I said avoid Nulls as much as you can.
I agree - if a value is not required, then allow Null. But, avoid Nulls as much as you can.
I didn't say ignore them. I didn't say they don't mean anything even though their meaning is unknown. I said avoid Nulls as much as you can.
I didn't say mislead others with default values. I said avoid Nulls as much as you can.
If a value is expected, then don't allow Nulls. That is reality.
That's my story and I'm sticking to it.
Avoid Nulls as much as you can.|||Man, I love this. MaxA, - you rule!!!!
But looking back at my own designs, - I can tell thatt the better I understood the business requirements, the more complex but at the same time cleaner the final design looked like. And ironicly I did notice that fewer nulls were present. Offloading NULLable columns to a separate table always leads to more complex queries, but a LEFT OUTER JOIN is a LEFT OUTER JOIN, and if PKs/FKs are properly indexed, - you'll see a better performance as well...But that's just me ;)
EDITED: By better performance I mean that your table will naturally be theoretically wider when nullable columns are present, and at the time of optimizing your SELECT you have 2 choices: avoid selecting nullable columns, or include them into existing indexes to avoid bookmark lookups. But if you offload those columns into a separate table that will only contain records where the column is not null, it'll result in fewer records to go through and all indexes are used.|||Avoid NULLS as much as you can? That is just a tautology. If you are saying not to allow NULLS when a value is required, then I completely agree with you.
How about this statement: "Avoid making a field required as much as you can."
I didn't say completely avoid Non-Nulls, I said avoid Non-Nulls as much as you can.
I agree - if a value is required, then disallow Null. But, avoid Non-Nulls as much as you can.
I didn't say ignore Non-Nulls. I didn't say they should be used even when a value is unknown. I said avoid Non-Nulls as much as you can.
If a value is not required, then allow Non-Nulls. That is reality.
That's my story and I'm sticking to it.
Avoid Non-Nulls as much as you can.|||Oh that's just plagiarism, Mr. Lindman, come up with your own style! ;)|||Plagiarism IS a style. Just look at Hollywood or the Grammy Awards! ;)
"Plagiarism is the sincerest form of flattery".
I forget who originally said that...|||Apparently Charles Caleb Colton:
http://www.quotationspage.com/quote/27484.html
I just don't think he has ever gotten enough credit for his work, do you?
Subscribe to:
Posts (Atom)