We have 5 different users, and only the administrator can
open and use the programs, for example, Quickbooks can
only be opened by the administrator. How do we change
the "restriced access" to aloud the other users to open
the programs. All the programs and files are in the
server, now we need everyone to access them.
Thank you.This is a SQL Server newsgroup - your question doesn't have anything to do
with SQL Server!
How you go about unlocking security settings on your server based programs
will have much to do with how those programs work (ie are they client /
server programs or simple windows apps?) as well as how they are secured in
the first place - via policies, roles or individual dacls..
You should probably ask this question in a security related newsgroup
though..
Regards,
Greg Linwood
SQL Server MVP
"email@.caseymauldin.com" <anonymous@.discussions.microsoft.com> wrote in
message news:02c401c3c836$054987c0$a401280a@.phx.gbl...
> We have 5 different users, and only the administrator can
> open and use the programs, for example, Quickbooks can
> only be opened by the administrator. How do we change
> the "restriced access" to aloud the other users to open
> the programs. All the programs and files are in the
> server, now we need everyone to access them.
> Thank you.sql
Friday, March 30, 2012
Restricted access to Enterprise manager
Hi,
I want to restrict the access to enterprise manager from the developer
computer. They need the access the develpment machine so they require
enterprise manager on their machines. But i do not want them to access
the production database from their workstation enterprise manager. Also
because they are developers they know the username/password for the
production database.
If there is any tool or utility which check who is login in and from
which program and than it can accept or reject the connection would ba
great help.
Regards,
Puneet
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
"Puneet Parashar" <parashar22@.hotmail.com> wrote in message
news:#tPxEX5YEHA.3432@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to restrict the access to enterprise manager from the developer
> computer. They need the access the develpment machine so they require
> enterprise manager on their machines. But i do not want them to access
> the production database from their workstation enterprise manager. Also
> because they are developers they know the username/password for the
> production database.
> If there is any tool or utility which check who is login in and from
> which program and than it can accept or reject the connection would ba
> great help.
>
> Regards,
> Puneet
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi,
Check the sql server security in BOL.
1) Delete the guest user account from the database.
2) Restrict the users and give the permissions only to the specific
databases which they need.
"Puneet Parashar" <parashar22@.hotmail.com> wrote in message
news:#tPxEX5YEHA.3432@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to restrict the access to enterprise manager from the developer
> computer. They need the access the develpment machine so they require
> enterprise manager on their machines. But i do not want them to access
> the production database from their workstation enterprise manager. Also
> because they are developers they know the username/password for the
> production database.
> If there is any tool or utility which check who is login in and from
> which program and than it can accept or reject the connection would ba
> great help.
>
> Regards,
> Puneet
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
I want to restrict the access to enterprise manager from the developer
computer. They need the access the develpment machine so they require
enterprise manager on their machines. But i do not want them to access
the production database from their workstation enterprise manager. Also
because they are developers they know the username/password for the
production database.
If there is any tool or utility which check who is login in and from
which program and than it can accept or reject the connection would ba
great help.
Regards,
Puneet
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
"Puneet Parashar" <parashar22@.hotmail.com> wrote in message
news:#tPxEX5YEHA.3432@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to restrict the access to enterprise manager from the developer
> computer. They need the access the develpment machine so they require
> enterprise manager on their machines. But i do not want them to access
> the production database from their workstation enterprise manager. Also
> because they are developers they know the username/password for the
> production database.
> If there is any tool or utility which check who is login in and from
> which program and than it can accept or reject the connection would ba
> great help.
>
> Regards,
> Puneet
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi,
Check the sql server security in BOL.
1) Delete the guest user account from the database.
2) Restrict the users and give the permissions only to the specific
databases which they need.
"Puneet Parashar" <parashar22@.hotmail.com> wrote in message
news:#tPxEX5YEHA.3432@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to restrict the access to enterprise manager from the developer
> computer. They need the access the develpment machine so they require
> enterprise manager on their machines. But i do not want them to access
> the production database from their workstation enterprise manager. Also
> because they are developers they know the username/password for the
> production database.
> If there is any tool or utility which check who is login in and from
> which program and than it can accept or reject the connection would ba
> great help.
>
> Regards,
> Puneet
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
Restrict Warning Message
I used a temporary table in a stored procedure.Whenever this stored procedure is executed with the desired output it is giving the warning message-
Warning: The table '#TEMPTBLPERSON' has been created but its maximum row size (10930) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Now my question is how can I avoid of displaying this warning message
in the stored procedure without altering the temporary table ?Originally posted by subhasishray
I used a temporary table in a stored procedure.Whenever this stored procedure is executed with the desired output it is giving the warning message-
Warning: The table '#TEMPTBLPERSON' has been created but its maximum row size (10930) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Now my question is how can I avoid of displaying this warning message
in the stored procedure without altering the temporary table ?
The best way is to reduce the data size of the table to less than 8060 bytes.
other way .. i believe might be to set the option
set ansi_warnings off|||I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish|||Originally posted by subhasishray
I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish
Clearly ... you are not paying heed to the warning message .. An insert into your table will fail in case the data is more than 8060 bytes . You should take that case into consideration.
Am searching for the other answer .. will get back as soon as i find it|||There is no "other" answer, you've said it all. the only other thing to do would be to split the table into 2 with 1-to-1 relationship.|||I was searching for a server property where i could control the severity level of warnings and errors as is availible in osql with the -m flag.|||Originally posted by subhasishray
I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish
Whether you know it or not...that comes across with some attitude..
If you can't be flexible...then I don't know who will...
good luck...
Warning: The table '#TEMPTBLPERSON' has been created but its maximum row size (10930) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Now my question is how can I avoid of displaying this warning message
in the stored procedure without altering the temporary table ?Originally posted by subhasishray
I used a temporary table in a stored procedure.Whenever this stored procedure is executed with the desired output it is giving the warning message-
Warning: The table '#TEMPTBLPERSON' has been created but its maximum row size (10930) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Now my question is how can I avoid of displaying this warning message
in the stored procedure without altering the temporary table ?
The best way is to reduce the data size of the table to less than 8060 bytes.
other way .. i believe might be to set the option
set ansi_warnings off|||I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish|||Originally posted by subhasishray
I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish
Clearly ... you are not paying heed to the warning message .. An insert into your table will fail in case the data is more than 8060 bytes . You should take that case into consideration.
Am searching for the other answer .. will get back as soon as i find it|||There is no "other" answer, you've said it all. the only other thing to do would be to split the table into 2 with 1-to-1 relationship.|||I was searching for a server property where i could control the severity level of warnings and errors as is availible in osql with the -m flag.|||Originally posted by subhasishray
I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish
Whether you know it or not...that comes across with some attitude..
If you can't be flexible...then I don't know who will...
good luck...
Restrict users to modify tabble structure
Is it possible to restrict users to modify the table structure. Most of our
users are using "Windows authentication"
Thanks for any help.
J Justin
You need to limit what permissions each user has. Modify the security =
on each group or individual so that they have only the rights that they =
need. If you want to limit what they can do you will want to clear all =
checkboxes on the "server roles" tab and you will want to make sure that =
they have the appropriate database roles assigned. You access the forms =
that I am talking about via Enterprise Manager. Drill into the security =
folder, Logins node. double-click on a specific login to view its =
properties.
--=20
Keith
"Justin" <justinkumar@.hotmail.com> wrote in message =
news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most =
of our
> users are using "Windows authentication"
> Thanks for any help.
>=20
> J Justin
>=20
>
|||Just make sure that they don't have such permissions/privileges in your database. I.e., should not have the
sysadmin server role. Should not be dbo in the database. Should not have db_ddladmin or db_owner database
role.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <justinkumar@.hotmail.com> wrote in message news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most of our
> users are using "Windows authentication"
> Thanks for any help.
> J Justin
>
users are using "Windows authentication"
Thanks for any help.
J Justin
You need to limit what permissions each user has. Modify the security =
on each group or individual so that they have only the rights that they =
need. If you want to limit what they can do you will want to clear all =
checkboxes on the "server roles" tab and you will want to make sure that =
they have the appropriate database roles assigned. You access the forms =
that I am talking about via Enterprise Manager. Drill into the security =
folder, Logins node. double-click on a specific login to view its =
properties.
--=20
Keith
"Justin" <justinkumar@.hotmail.com> wrote in message =
news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most =
of our
> users are using "Windows authentication"
> Thanks for any help.
>=20
> J Justin
>=20
>
|||Just make sure that they don't have such permissions/privileges in your database. I.e., should not have the
sysadmin server role. Should not be dbo in the database. Should not have db_ddladmin or db_owner database
role.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <justinkumar@.hotmail.com> wrote in message news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most of our
> users are using "Windows authentication"
> Thanks for any help.
> J Justin
>
Restrict users to modify tabble structure
Is it possible to restrict users to modify the table structure. Most of our
users are using "Windows authentication"
Thanks for any help.
J JustinYou need to limit what permissions each user has. Modify the security =
on each group or individual so that they have only the rights that they =
need. If you want to limit what they can do you will want to clear all =
checkboxes on the "server roles" tab and you will want to make sure that =
they have the appropriate database roles assigned. You access the forms =
that I am talking about via Enterprise Manager. Drill into the security =
folder, Logins node. double-click on a specific login to view its =
properties.
--=20
Keith
"Justin" <justinkumar@.hotmail.com> wrote in message =
news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most =
of our
> users are using "Windows authentication"
> Thanks for any help.
>=20
> J Justin
>=20
>|||Just make sure that they don't have such permissions/privileges in your data
base. I.e., should not have the
sysadmin server role. Should not be dbo in the database. Should not have db_
ddladmin or db_owner database
role.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <justinkumar@.hotmail.com> wrote in message news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx
.gbl...
> Is it possible to restrict users to modify the table structure. Most of ou
r
> users are using "Windows authentication"
> Thanks for any help.
> J Justin
>
users are using "Windows authentication"
Thanks for any help.
J JustinYou need to limit what permissions each user has. Modify the security =
on each group or individual so that they have only the rights that they =
need. If you want to limit what they can do you will want to clear all =
checkboxes on the "server roles" tab and you will want to make sure that =
they have the appropriate database roles assigned. You access the forms =
that I am talking about via Enterprise Manager. Drill into the security =
folder, Logins node. double-click on a specific login to view its =
properties.
--=20
Keith
"Justin" <justinkumar@.hotmail.com> wrote in message =
news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most =
of our
> users are using "Windows authentication"
> Thanks for any help.
>=20
> J Justin
>=20
>|||Just make sure that they don't have such permissions/privileges in your data
base. I.e., should not have the
sysadmin server role. Should not be dbo in the database. Should not have db_
ddladmin or db_owner database
role.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <justinkumar@.hotmail.com> wrote in message news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx
.gbl...
> Is it possible to restrict users to modify the table structure. Most of ou
r
> users are using "Windows authentication"
> Thanks for any help.
> J Justin
>
Restrict users to modify tabble structure
Is it possible to restrict users to modify the table structure. Most of our
users are using "Windows authentication"
Thanks for any help.
J JustinYou need to limit what permissions each user has. Modify the security =on each group or individual so that they have only the rights that they =need. If you want to limit what they can do you will want to clear all =checkboxes on the "server roles" tab and you will want to make sure that =they have the appropriate database roles assigned. You access the forms =that I am talking about via Enterprise Manager. Drill into the security =folder, Logins node. double-click on a specific login to view its =properties.
-- Keith
"Justin" <justinkumar@.hotmail.com> wrote in message =news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most =of our
> users are using "Windows authentication"
> Thanks for any help.
> > J Justin
> >|||Just make sure that they don't have such permissions/privileges in your database. I.e., should not have the
sysadmin server role. Should not be dbo in the database. Should not have db_ddladmin or db_owner database
role.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <justinkumar@.hotmail.com> wrote in message news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most of our
> users are using "Windows authentication"
> Thanks for any help.
> J Justin
>sql
users are using "Windows authentication"
Thanks for any help.
J JustinYou need to limit what permissions each user has. Modify the security =on each group or individual so that they have only the rights that they =need. If you want to limit what they can do you will want to clear all =checkboxes on the "server roles" tab and you will want to make sure that =they have the appropriate database roles assigned. You access the forms =that I am talking about via Enterprise Manager. Drill into the security =folder, Logins node. double-click on a specific login to view its =properties.
-- Keith
"Justin" <justinkumar@.hotmail.com> wrote in message =news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most =of our
> users are using "Windows authentication"
> Thanks for any help.
> > J Justin
> >|||Just make sure that they don't have such permissions/privileges in your database. I.e., should not have the
sysadmin server role. Should not be dbo in the database. Should not have db_ddladmin or db_owner database
role.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <justinkumar@.hotmail.com> wrote in message news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most of our
> users are using "Windows authentication"
> Thanks for any help.
> J Justin
>sql
Restrict user from default tables (access given by public role)
I am newbie.. probably this is a silly question..
I added few users with only SELECT permissions to 2 User Tables. But
when these users login,they could also see multiple system tables
(syscontraints, syssegments , dtproperties) . From reading this forum
and other articles, I understand these are visible due to the user
being part of the default "public" role.
My Question is, is there any way to remove/hide these system tables
from the user?.. The team manager doesnt like these users seeing
anything other than the 2 User Tables they supposed to see..
Thanks in advance for all tips/advices/suggestionsTell your team manager he's a turd. No...actually don't say that, it'll
probably get you fired.
I wouldn't go messing around with the permissions on the system tables,
you're likely to stuff up some functionality of SQL Server in that DB
for those users. The system tables are a very necessary part of
day-to-day data & schema manipulation...for every user of the DB.
Things like optimising query plans, for example, rely on data stored in
the system tables. I'm not positive if things will start breaking if
you start revoking or denying permissions on those system tables but I
wouldn't risk it.
There's an option in SSMS (Tools | Options | Environment | General |
Hide system objects in Object Explorer), not sure about SQLEM or QA,
that will hide the system tables in the GUI but not change their
underlying permissions. That may placate the pointy-haired manager
<http://www.dilbert.com/> asking for this change, perhaps.
What tool are the users using to "see" these system tables?
*mike hodgson*
http://sqlnerd.blogspot.com
sreejith.ram@.gmail.com wrote:
>I am newbie.. probably this is a silly question..
>I added few users with only SELECT permissions to 2 User Tables. But
>when these users login,they could also see multiple system tables
>(syscontraints, syssegments , dtproperties) . From reading this forum
>and other articles, I understand these are visible due to the user
>being part of the default "public" role.
>My Question is, is there any way to remove/hide these system tables
>from the user?.. The team manager doesnt like these users seeing
>anything other than the 2 User Tables they supposed to see..
>Thanks in advance for all tips/advices/suggestions
>
>|||Thanks Mike... This convinced me I should spend my time convincing the
manager to leave it alone than trying to hide the tables
The users are using SQL Server Enterprise Manager.|||> The users are using SQL Server Enterprise Manager.
EM is more of a DBA/Developer tool than an end-user tool. In any case, you
can hide most of the system object noise by unchecking the 'show system
databases and system objects' option under the EM server registration
properties.
Hope this helps.
Dan Guzman
SQL Server MVP
<sreejith.ram@.gmail.com> wrote in message
news:1138983030.862156.116660@.f14g2000cwb.googlegroups.com...
> Thanks Mike... This convinced me I should spend my time convincing the
> manager to leave it alone than trying to hide the tables
> The users are using SQL Server Enterprise Manager.
>|||I thought there was that "hide" option in SQLEM. I just couldn't
remember where it was (been using SSMS too long <g> ).
*mike hodgson*
http://sqlnerd.blogspot.com
Dan Guzman wrote:
>EM is more of a DBA/Developer tool than an end-user tool. In any case, you
>can hide most of the system object noise by unchecking the 'show system
>databases and system objects' option under the EM server registration
>properties.
>
>
I added few users with only SELECT permissions to 2 User Tables. But
when these users login,they could also see multiple system tables
(syscontraints, syssegments , dtproperties) . From reading this forum
and other articles, I understand these are visible due to the user
being part of the default "public" role.
My Question is, is there any way to remove/hide these system tables
from the user?.. The team manager doesnt like these users seeing
anything other than the 2 User Tables they supposed to see..
Thanks in advance for all tips/advices/suggestionsTell your team manager he's a turd. No...actually don't say that, it'll
probably get you fired.
I wouldn't go messing around with the permissions on the system tables,
you're likely to stuff up some functionality of SQL Server in that DB
for those users. The system tables are a very necessary part of
day-to-day data & schema manipulation...for every user of the DB.
Things like optimising query plans, for example, rely on data stored in
the system tables. I'm not positive if things will start breaking if
you start revoking or denying permissions on those system tables but I
wouldn't risk it.
There's an option in SSMS (Tools | Options | Environment | General |
Hide system objects in Object Explorer), not sure about SQLEM or QA,
that will hide the system tables in the GUI but not change their
underlying permissions. That may placate the pointy-haired manager
<http://www.dilbert.com/> asking for this change, perhaps.
What tool are the users using to "see" these system tables?
*mike hodgson*
http://sqlnerd.blogspot.com
sreejith.ram@.gmail.com wrote:
>I am newbie.. probably this is a silly question..
>I added few users with only SELECT permissions to 2 User Tables. But
>when these users login,they could also see multiple system tables
>(syscontraints, syssegments , dtproperties) . From reading this forum
>and other articles, I understand these are visible due to the user
>being part of the default "public" role.
>My Question is, is there any way to remove/hide these system tables
>from the user?.. The team manager doesnt like these users seeing
>anything other than the 2 User Tables they supposed to see..
>Thanks in advance for all tips/advices/suggestions
>
>|||Thanks Mike... This convinced me I should spend my time convincing the
manager to leave it alone than trying to hide the tables
The users are using SQL Server Enterprise Manager.|||> The users are using SQL Server Enterprise Manager.
EM is more of a DBA/Developer tool than an end-user tool. In any case, you
can hide most of the system object noise by unchecking the 'show system
databases and system objects' option under the EM server registration
properties.
Hope this helps.
Dan Guzman
SQL Server MVP
<sreejith.ram@.gmail.com> wrote in message
news:1138983030.862156.116660@.f14g2000cwb.googlegroups.com...
> Thanks Mike... This convinced me I should spend my time convincing the
> manager to leave it alone than trying to hide the tables
> The users are using SQL Server Enterprise Manager.
>|||I thought there was that "hide" option in SQLEM. I just couldn't
remember where it was (been using SSMS too long <g> ).
*mike hodgson*
http://sqlnerd.blogspot.com
Dan Guzman wrote:
>EM is more of a DBA/Developer tool than an end-user tool. In any case, you
>can hide most of the system object noise by unchecking the 'show system
>databases and system objects' option under the EM server registration
>properties.
>
>
Restrict User Access in MSSQL 2000
Hi,
I have few SQL user who has permissions on different databases. When they are accessing MSSQL server from Enterprise Manager they can see all Databases but can not access them and they can change file size allocated to them. I want to restrict this and they can only see database belongs to respective user and restrict user to change allocated size. Please help me with this as I am not able to find the solution.
Thanks & Regards,
Gaurav
You cannot restrict the users in SQL Server 2000 from seeing the available databases (this can be done in SQL Server 2005 by revoking VIEW ANY DATABASE to the public server role).
Also, a database owner will be able to change the file size for the databases he owns; you cannot prevent this.
Thanks
Laurentiu
restrict user access
how can i set the user access level for sql 2000 server?
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.
Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.
Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>
restrict user access
how can i set the user access level for sql 2000 server?
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>
restrict user access
how can i set the user access level for sql 2000 server?
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>sql
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>sql
restrict update trigger to updated rows
How do I restrict an update trigger to just the rows updated?
Would like to have two audit fields with user and time of modification for
updates.
The following trigger would attempt to update all rows in the table:
ALTER trigger tm_stmp_admit_proc
on dbo.tblAdmissions_procedures
for update
as
begin
update tblAdmissions_procedures
set time_stamp = current_timestamp,user_modify = system_user
end
Couldn't find any description in BOL analogous to the FOR EACH ROW parameter
in
PostgreSQL:
CREATE OR REPLACE FUNCTION public.timestamper()
RETURNS trigger AS
'
BEGIN
NEW.time_stamp := \'now\';
NEW.user_modify := current_user;
RETURN NEW;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER timestamper
BEFORE INSERT OR UPDATE
ON public.admit_procedures
FOR EACH ROW
EXECUTE PROCEDURE public.timestamper();
Thanks,
David P. Lurie
If you have a primary key column in the table
use INSERTED table in the trigger.
like
update tblAdmissions_procedures
set time_stamp = current_timestamp,user_modify = system_user
where exists ( select keycolumn from inserted
where tblAdmissions_procedures.keycolumn = inserted.keycolumn )
but, then u may also have to set this parameter
SET RECURSIVE_TRIGGERS OFF
HTH
Faris
"David P. Lurie" <abc@.def.net> schrieb im Newsbeitrag
news:%23Di0ArVbEHA.2944@.TK2MSFTNGP11.phx.gbl...
> How do I restrict an update trigger to just the rows updated?
> Would like to have two audit fields with user and time of modification for
> updates.
> The following trigger would attempt to update all rows in the table:
> ALTER trigger tm_stmp_admit_proc
> on dbo.tblAdmissions_procedures
> for update
> as
> begin
> update tblAdmissions_procedures
> set time_stamp = current_timestamp,user_modify = system_user
> end
> Couldn't find any description in BOL analogous to the FOR EACH ROW
parameter
> in
> PostgreSQL:
> CREATE OR REPLACE FUNCTION public.timestamper()
> RETURNS trigger AS
> '
> BEGIN
> NEW.time_stamp := \'now\';
> NEW.user_modify := current_user;
> RETURN NEW;
> END;
> '
> LANGUAGE 'plpgsql' VOLATILE;
> CREATE TRIGGER timestamper
> BEFORE INSERT OR UPDATE
> ON public.admit_procedures
> FOR EACH ROW
> EXECUTE PROCEDURE public.timestamper();
> Thanks,
> David P. Lurie
>
|||Thanks a lot -
I tested this on two of the tables, and seems to work. Didn't turn off
recursive triggers to see whether it was necessary, and no problems thus far
with a few updates on each table. Assume that infinite loop would have
occurred if that setting was needed.
David P. Lurie
"Faris" <faris@.bss-india.com> wrote in message
news:eTBHKhWbEHA.3944@.tk2msftngp13.phx.gbl...
> If you have a primary key column in the table
> use INSERTED table in the trigger.
> like
> update tblAdmissions_procedures
> set time_stamp = current_timestamp,user_modify = system_user
> where exists ( select keycolumn from inserted
> where tblAdmissions_procedures.keycolumn = inserted.keycolumn )
> but, then u may also have to set this parameter
> SET RECURSIVE_TRIGGERS OFF
> HTH
> Faris
Would like to have two audit fields with user and time of modification for
updates.
The following trigger would attempt to update all rows in the table:
ALTER trigger tm_stmp_admit_proc
on dbo.tblAdmissions_procedures
for update
as
begin
update tblAdmissions_procedures
set time_stamp = current_timestamp,user_modify = system_user
end
Couldn't find any description in BOL analogous to the FOR EACH ROW parameter
in
PostgreSQL:
CREATE OR REPLACE FUNCTION public.timestamper()
RETURNS trigger AS
'
BEGIN
NEW.time_stamp := \'now\';
NEW.user_modify := current_user;
RETURN NEW;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER timestamper
BEFORE INSERT OR UPDATE
ON public.admit_procedures
FOR EACH ROW
EXECUTE PROCEDURE public.timestamper();
Thanks,
David P. Lurie
If you have a primary key column in the table
use INSERTED table in the trigger.
like
update tblAdmissions_procedures
set time_stamp = current_timestamp,user_modify = system_user
where exists ( select keycolumn from inserted
where tblAdmissions_procedures.keycolumn = inserted.keycolumn )
but, then u may also have to set this parameter
SET RECURSIVE_TRIGGERS OFF
HTH
Faris
"David P. Lurie" <abc@.def.net> schrieb im Newsbeitrag
news:%23Di0ArVbEHA.2944@.TK2MSFTNGP11.phx.gbl...
> How do I restrict an update trigger to just the rows updated?
> Would like to have two audit fields with user and time of modification for
> updates.
> The following trigger would attempt to update all rows in the table:
> ALTER trigger tm_stmp_admit_proc
> on dbo.tblAdmissions_procedures
> for update
> as
> begin
> update tblAdmissions_procedures
> set time_stamp = current_timestamp,user_modify = system_user
> end
> Couldn't find any description in BOL analogous to the FOR EACH ROW
parameter
> in
> PostgreSQL:
> CREATE OR REPLACE FUNCTION public.timestamper()
> RETURNS trigger AS
> '
> BEGIN
> NEW.time_stamp := \'now\';
> NEW.user_modify := current_user;
> RETURN NEW;
> END;
> '
> LANGUAGE 'plpgsql' VOLATILE;
> CREATE TRIGGER timestamper
> BEFORE INSERT OR UPDATE
> ON public.admit_procedures
> FOR EACH ROW
> EXECUTE PROCEDURE public.timestamper();
> Thanks,
> David P. Lurie
>
|||Thanks a lot -
I tested this on two of the tables, and seems to work. Didn't turn off
recursive triggers to see whether it was necessary, and no problems thus far
with a few updates on each table. Assume that infinite loop would have
occurred if that setting was needed.
David P. Lurie
"Faris" <faris@.bss-india.com> wrote in message
news:eTBHKhWbEHA.3944@.tk2msftngp13.phx.gbl...
> If you have a primary key column in the table
> use INSERTED table in the trigger.
> like
> update tblAdmissions_procedures
> set time_stamp = current_timestamp,user_modify = system_user
> where exists ( select keycolumn from inserted
> where tblAdmissions_procedures.keycolumn = inserted.keycolumn )
> but, then u may also have to set this parameter
> SET RECURSIVE_TRIGGERS OFF
> HTH
> Faris
restrict to localhost only
I want to restrict the database to localhost only. I did what past postings
suggested (http://support.microsoft.com/default.aspx?scid=kb;en-us;814130),
which was to go into SQL Server Network Utility and disable all protocols.
After doing that, I restarted the SQL Server service and also restarted the
computer). However, I was still able to connect to the database in Query
Analyzer from another machine.
Has anyone been able to successfully restrict to localhost? I am using SQL
2000 Developer Edition w/ SP3a, Windows XP Pro. Thanks for any help.One curious observation is that even though TCP/IP has been disabled, if I
remove the TCP/IP library file (C:\Program Files\Microsoft SQL
Server\MSSQL\Binn\ssnetlib.dll), SQL Server service will not start. Which
indicates to me that SQL Server is still loading the TCP/IP protocol even
though I have it disabled.|||After disabling all the protocols did you look at the SQL Server errorlog
to verify that it was listening only on shared memory?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||"Dean" <noreply@.fakeaddress.com> wrote in message
news:bv943l$rom$1@.news01.intel.com...
> I want to restrict the database to localhost only. I did what past
postings
> suggested
(http://support.microsoft.com/default.aspx?scid=kb;en-us;814130),
> which was to go into SQL Server Network Utility and disable all protocols.
> After doing that, I restarted the SQL Server service and also restarted
the
> computer). However, I was still able to connect to the database in Query
> Analyzer from another machine.
> Has anyone been able to successfully restrict to localhost? I am using SQL
> 2000 Developer Edition w/ SP3a, Windows XP Pro. Thanks for any help.
"Rand Boyd [MSFT]" rboyd@.onlinemicrosoft.com wrote in message
> After disabling all the protocols did you look at the SQL Server errorlog
> to verify that it was listening only on shared memory?
I just looked now in Management > SQL Server Logs. Here's are some key
lines:
Using 'SSNETLIB.DLL' version '8.0.766'.
SQL server listening on 127.0.0.1: 1433.
SQL server listening on xxx.xxx.xxx.xxx: 1433.
SQL server listening on TCP, Shared Memory.
This seems to indicate that it is not only listening on shared memory. But
when I go to Properties > Network Configuration, there are no enabled
protocols! And I have restarted SQL service as well as the computer.
suggested (http://support.microsoft.com/default.aspx?scid=kb;en-us;814130),
which was to go into SQL Server Network Utility and disable all protocols.
After doing that, I restarted the SQL Server service and also restarted the
computer). However, I was still able to connect to the database in Query
Analyzer from another machine.
Has anyone been able to successfully restrict to localhost? I am using SQL
2000 Developer Edition w/ SP3a, Windows XP Pro. Thanks for any help.One curious observation is that even though TCP/IP has been disabled, if I
remove the TCP/IP library file (C:\Program Files\Microsoft SQL
Server\MSSQL\Binn\ssnetlib.dll), SQL Server service will not start. Which
indicates to me that SQL Server is still loading the TCP/IP protocol even
though I have it disabled.|||After disabling all the protocols did you look at the SQL Server errorlog
to verify that it was listening only on shared memory?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||"Dean" <noreply@.fakeaddress.com> wrote in message
news:bv943l$rom$1@.news01.intel.com...
> I want to restrict the database to localhost only. I did what past
postings
> suggested
(http://support.microsoft.com/default.aspx?scid=kb;en-us;814130),
> which was to go into SQL Server Network Utility and disable all protocols.
> After doing that, I restarted the SQL Server service and also restarted
the
> computer). However, I was still able to connect to the database in Query
> Analyzer from another machine.
> Has anyone been able to successfully restrict to localhost? I am using SQL
> 2000 Developer Edition w/ SP3a, Windows XP Pro. Thanks for any help.
"Rand Boyd [MSFT]" rboyd@.onlinemicrosoft.com wrote in message
> After disabling all the protocols did you look at the SQL Server errorlog
> to verify that it was listening only on shared memory?
I just looked now in Management > SQL Server Logs. Here's are some key
lines:
Using 'SSNETLIB.DLL' version '8.0.766'.
SQL server listening on 127.0.0.1: 1433.
SQL server listening on xxx.xxx.xxx.xxx: 1433.
SQL server listening on TCP, Shared Memory.
This seems to indicate that it is not only listening on shared memory. But
when I go to Properties > Network Configuration, there are no enabled
protocols! And I have restarted SQL service as well as the computer.
restrict to localhost only
I want to restrict the database to localhost only. I did what past postings
suggested (http://support.microsoft.com/defaul...kb;en-us;814130),
which was to go into SQL Server Network Utility and disable all protocols.
After doing that, I restarted the SQL Server service and also restarted the
computer). However, I was still able to connect to the database in Query
Analyzer from another machine.
Has anyone been able to successfully restrict to localhost? I am using SQL
2000 Developer Edition w/ SP3a, Windows XP Pro. Thanks for any help.One curious observation is that even though TCP/IP has been disabled, if I
remove the TCP/IP library file (C:\Program Files\Microsoft SQL
Server\MSSQL\Binn\ssnetlib.dll), SQL Server service will not start. Which
indicates to me that SQL Server is still loading the TCP/IP protocol even
though I have it disabled.|||After disabling all the protocols did you look at the SQL Server errorlog
to verify that it was listening only on shared memory?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||"Dean" <noreply@.fakeaddress.com> wrote in message
news:bv943l$rom$1@.news01.intel.com...
postings
(http://support.microsoft.com/defaul...kb;en-us;814130),
the
"Rand Boyd [MSFT]" rboyd@.onlinemicrosoft.com wrote in message
I just looked now in Management > SQL Server Logs. Here's are some key
lines:
Using 'SSNETLIB.DLL' version '8.0.766'.
SQL server listening on 127.0.0.1: 1433.
SQL server listening on xxx.xxx.xxx.xxx: 1433.
SQL server listening on TCP, Shared Memory.
This seems to indicate that it is not only listening on shared memory. But
when I go to Properties > Network Configuration, there are no enabled
protocols! And I have restarted SQL service as well as the computer.
suggested (http://support.microsoft.com/defaul...kb;en-us;814130),
which was to go into SQL Server Network Utility and disable all protocols.
After doing that, I restarted the SQL Server service and also restarted the
computer). However, I was still able to connect to the database in Query
Analyzer from another machine.
Has anyone been able to successfully restrict to localhost? I am using SQL
2000 Developer Edition w/ SP3a, Windows XP Pro. Thanks for any help.One curious observation is that even though TCP/IP has been disabled, if I
remove the TCP/IP library file (C:\Program Files\Microsoft SQL
Server\MSSQL\Binn\ssnetlib.dll), SQL Server service will not start. Which
indicates to me that SQL Server is still loading the TCP/IP protocol even
though I have it disabled.|||After disabling all the protocols did you look at the SQL Server errorlog
to verify that it was listening only on shared memory?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||"Dean" <noreply@.fakeaddress.com> wrote in message
news:bv943l$rom$1@.news01.intel.com...
quote:
> I want to restrict the database to localhost only. I did what past
postings
quote:
> suggested
(http://support.microsoft.com/defaul...kb;en-us;814130),
quote:
> which was to go into SQL Server Network Utility and disable all protocols.
> After doing that, I restarted the SQL Server service and also restarted
the
quote:
> computer). However, I was still able to connect to the database in Query
> Analyzer from another machine.
> Has anyone been able to successfully restrict to localhost? I am using SQL
> 2000 Developer Edition w/ SP3a, Windows XP Pro. Thanks for any help.
"Rand Boyd [MSFT]" rboyd@.onlinemicrosoft.com wrote in message
quote:
> After disabling all the protocols did you look at the SQL Server errorlog
> to verify that it was listening only on shared memory?
I just looked now in Management > SQL Server Logs. Here's are some key
lines:
Using 'SSNETLIB.DLL' version '8.0.766'.
SQL server listening on 127.0.0.1: 1433.
SQL server listening on xxx.xxx.xxx.xxx: 1433.
SQL server listening on TCP, Shared Memory.
This seems to indicate that it is not only listening on shared memory. But
when I go to Properties > Network Configuration, there are no enabled
protocols! And I have restarted SQL service as well as the computer.
Restrict The Number Of Records From Query
Hi,
ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query
i want to implement a query system which will give me the first 50 records, next 50 ,... and so on
can ne body help ?Originally posted by baburajv
Hi,
ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query
i want to implement a query system which will give me the first 50 records, next 50 ,... and so on
can ne body help ?
select top 50 from table_name|||SELECT TOP 50 FROM TABLE_NAME
WILL RETURN THE TOP 50 ROWS FROM THE TABLE
HOW CAN I RETRIEVE THE NEXT 50 ROWS|||Originally posted by baburajv
SELECT TOP 50 FROM TABLE_NAME
WILL RETURN THE TOP 50 ROWS FROM THE TABLE
HOW CAN I RETRIEVE THE NEXT 50 ROWS
I think then u need to use temperory tables for it.|||select top NUMBER_OF_ROWS *
from table
where column NOT IN
(select top BEGIN_AT column
from table)
ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query
i want to implement a query system which will give me the first 50 records, next 50 ,... and so on
can ne body help ?Originally posted by baburajv
Hi,
ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query
i want to implement a query system which will give me the first 50 records, next 50 ,... and so on
can ne body help ?
select top 50 from table_name|||SELECT TOP 50 FROM TABLE_NAME
WILL RETURN THE TOP 50 ROWS FROM THE TABLE
HOW CAN I RETRIEVE THE NEXT 50 ROWS|||Originally posted by baburajv
SELECT TOP 50 FROM TABLE_NAME
WILL RETURN THE TOP 50 ROWS FROM THE TABLE
HOW CAN I RETRIEVE THE NEXT 50 ROWS
I think then u need to use temperory tables for it.|||select top NUMBER_OF_ROWS *
from table
where column NOT IN
(select top BEGIN_AT column
from table)
Restrict result rows with a database setting
I am using SQL Server 2000 SP4. Is it possible to limit the number of
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch Smith
Hi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 records
> and subsequently crash the machine they are running from. I was wondering if
> I could limit the maximum number of rows to 10,000 via a database setting? I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>
|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>
|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the view
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
>
sql
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch Smith
Hi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 records
> and subsequently crash the machine they are running from. I was wondering if
> I could limit the maximum number of rows to 10,000 via a database setting? I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>
|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>
|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the view
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
>
sql
Restrict result rows with a database setting
I am using SQL Server 2000 SP4. Is it possible to limit the number of
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch SmithHi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time
a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 record
s
> and subsequently crash the machine they are running from. I was wondering
if
> I could limit the maximum number of rows to 10,000 via a database setting?
I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the vie
w
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
>
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch SmithHi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time
a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 record
s
> and subsequently crash the machine they are running from. I was wondering
if
> I could limit the maximum number of rows to 10,000 via a database setting?
I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the vie
w
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
>
Restrict result rows with a database setting
I am using SQL Server 2000 SP4. Is it possible to limit the number of
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch SmithHi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 records
> and subsequently crash the machine they are running from. I was wondering if
> I could limit the maximum number of rows to 10,000 via a database setting? I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the view
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
> >I am using SQL Server 2000 SP4. Is it possible to limit the number of
> >returned rows in a query via some sort of database setting? We have a
> >customer table with over 100,000 records and are not able to modify the
> >application that queries this table.
> >
> > If our users do not put in selection criteria, they get all 100,000
> > records and subsequently crash the machine they are running from. I was
> > wondering if I could limit the maximum number of rows to 10,000 via a
> > database setting? I know I could do that via the SQL that is called, but I
> > co not have control over that.
> >
> >
> > Thanks for any suggestions,
> >
> > Butch Smith
> >
>
>
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch SmithHi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 records
> and subsequently crash the machine they are running from. I was wondering if
> I could limit the maximum number of rows to 10,000 via a database setting? I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the view
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
> >I am using SQL Server 2000 SP4. Is it possible to limit the number of
> >returned rows in a query via some sort of database setting? We have a
> >customer table with over 100,000 records and are not able to modify the
> >application that queries this table.
> >
> > If our users do not put in selection criteria, they get all 100,000
> > records and subsequently crash the machine they are running from. I was
> > wondering if I could limit the maximum number of rows to 10,000 via a
> > database setting? I know I could do that via the SQL that is called, but I
> > co not have control over that.
> >
> >
> > Thanks for any suggestions,
> >
> > Butch Smith
> >
>
>
Restrict remote admin
Hello,
Is it possible to restrict remote admin of SQL Server? I
would like to prevent anybody with Enterprise Manager on
their workstation from connecting and accessing the
databases.
Ideally I would like to prevent all Enterprise Manager
access except from the server using the local Enterprise
Manager.
Is this possible. I could not find anything on MS
knowledge base about this.
Thanks"AL" <anonymous@.discussions.microsoft.com> wrote in message
news:a5f101c3eb6e$90c6bf60$a401280a@.phx.gbl...
By remote admin, do you mean someone with sysadmin rights on SQL Server, or
someone with admin rights on the server? The former you can't prevent,
unless you deny access to the server itself.
If the latter, be sure you are using Windows Authentication, secure your SQL
Server logins and database permissions. You can add your own custom group in
SQL Server assign sysadmin rights to that group, place a strong password on
the sa account, finally remove the builtin\administrators group.
Stevesql
Is it possible to restrict remote admin of SQL Server? I
would like to prevent anybody with Enterprise Manager on
their workstation from connecting and accessing the
databases.
Ideally I would like to prevent all Enterprise Manager
access except from the server using the local Enterprise
Manager.
Is this possible. I could not find anything on MS
knowledge base about this.
Thanks"AL" <anonymous@.discussions.microsoft.com> wrote in message
news:a5f101c3eb6e$90c6bf60$a401280a@.phx.gbl...
quote:
> Is it possible to restrict remote admin of SQL Server? I
> would like to prevent anybody with Enterprise Manager on
> their workstation from connecting and accessing the
> databases.
> Ideally I would like to prevent all Enterprise Manager
> access except from the server using the local Enterprise
> Manager.
> Is this possible. I could not find anything on MS
> knowledge base about this.
By remote admin, do you mean someone with sysadmin rights on SQL Server, or
someone with admin rights on the server? The former you can't prevent,
unless you deny access to the server itself.
If the latter, be sure you are using Windows Authentication, secure your SQL
Server logins and database permissions. You can add your own custom group in
SQL Server assign sysadmin rights to that group, place a strong password on
the sa account, finally remove the builtin\administrators group.
Stevesql
restrict query results
Hi guys, say i wrote a query that returns 1,000 records.. what kinda query could i write that only returns say the first 50 records of the 1,000 recs..
(
Select Row_number() Over (Order by MyField) as RunningNumber,*
From MyTable
)
Select * from SQLPaging
Where RunningNumber between 100 and 150
SELECT TOP 50 * FROM yourTable
|||thanks alot, another question.. what if i wanted the result 100 to 150?|||the way I phrased that question was kinda ambigous.. i'll put it in a clearer way. say if out of that 1,000 results i wanted record 100 to record 150 basically skipping the first 99 records and returning those 50 results in the middle, how could i code that in sql?|||With SQLPaging As
(
Select Row_number() Over (Order by MyField) as RunningNumber,*
From MyTable
)
Select * from SQLPaging
Where RunningNumber between 100 and 150
Restrict Printing based on User?
Is there any way to restrict certain users from printing a report (or
even exporting it)?
If so, what is the difficulty level on a scale of 1-10? I received a
request from a user to prohibit some users from printing, but I would
argue that if you don't want them to print it, they probably shouldn't
even see it.
Still, I'm curious as to the feasibility.
Thanks,
MikeNot with Report Manager. You can remove certain export types but it is for
everybody. Same thing with the new printing control, everybody or nobody.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in message
news:1122382697.120430.226760@.g47g2000cwa.googlegroups.com...
> Is there any way to restrict certain users from printing a report (or
> even exporting it)?
> If so, what is the difficulty level on a scale of 1-10? I received a
> request from a user to prohibit some users from printing, but I would
> argue that if you don't want them to print it, they probably shouldn't
> even see it.
> Still, I'm curious as to the feasibility.
> Thanks,
> Mike
>|||If you allow users to view your reports, you can't prevent them from
printing them.. Even if you find a way to do that, they'll still be
able to use "Print Screen" and then print out the screenshot...
even exporting it)?
If so, what is the difficulty level on a scale of 1-10? I received a
request from a user to prohibit some users from printing, but I would
argue that if you don't want them to print it, they probably shouldn't
even see it.
Still, I'm curious as to the feasibility.
Thanks,
MikeNot with Report Manager. You can remove certain export types but it is for
everybody. Same thing with the new printing control, everybody or nobody.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in message
news:1122382697.120430.226760@.g47g2000cwa.googlegroups.com...
> Is there any way to restrict certain users from printing a report (or
> even exporting it)?
> If so, what is the difficulty level on a scale of 1-10? I received a
> request from a user to prohibit some users from printing, but I would
> argue that if you don't want them to print it, they probably shouldn't
> even see it.
> Still, I'm curious as to the feasibility.
> Thanks,
> Mike
>|||If you allow users to view your reports, you can't prevent them from
printing them.. Even if you find a way to do that, they'll still be
able to use "Print Screen" and then print out the screenshot...
Restrict Port Security
I'm going to be installing a sql server behind a firewall. I'd appreciate
any best practice advice on changing the default port number and also wanted
to know if there was a way to secure a sql server by granting access only to
specific IP addresses?
Hi Scott,
Really best practice is changing the default port number of the SQL Server.
But granting access to specific IP addresses only machine 1 to machine 2 or
ip address.
Hugs,
Rodrigo Fernandes
MCSE/MCDBA
"scott" wrote:
> I'm going to be installing a sql server behind a firewall. I'd appreciate
> any best practice advice on changing the default port number and also wanted
> to know if there was a way to secure a sql server by granting access only to
> specific IP addresses?
>
>
|||What do you mean by saying 'but granting access ...'?
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in
message news:94A130F7-B240-44DF-9054-9E2BC08D9DE3@.microsoft.com...[vbcol=seagreen]
> Hi Scott,
> Really best practice is changing the default port number of the SQL
> Server.
> But granting access to specific IP addresses only machine 1 to machine 2
> or
> ip address.
> Hugs,
> Rodrigo Fernandes
> MCSE/MCDBA
> "scott" wrote:
any best practice advice on changing the default port number and also wanted
to know if there was a way to secure a sql server by granting access only to
specific IP addresses?
Hi Scott,
Really best practice is changing the default port number of the SQL Server.
But granting access to specific IP addresses only machine 1 to machine 2 or
ip address.
Hugs,
Rodrigo Fernandes
MCSE/MCDBA
"scott" wrote:
> I'm going to be installing a sql server behind a firewall. I'd appreciate
> any best practice advice on changing the default port number and also wanted
> to know if there was a way to secure a sql server by granting access only to
> specific IP addresses?
>
>
|||What do you mean by saying 'but granting access ...'?
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in
message news:94A130F7-B240-44DF-9054-9E2BC08D9DE3@.microsoft.com...[vbcol=seagreen]
> Hi Scott,
> Really best practice is changing the default port number of the SQL
> Server.
> But granting access to specific IP addresses only machine 1 to machine 2
> or
> ip address.
> Hugs,
> Rodrigo Fernandes
> MCSE/MCDBA
> "scott" wrote:
Restrict Port Security
I'm going to be installing a sql server behind a firewall. I'd appreciate
any best practice advice on changing the default port number and also wanted
to know if there was a way to secure a sql server by granting access only to
specific IP addresses?Hi Scott,
Really best practice is changing the default port number of the SQL Server.
But granting access to specific IP addresses only machine 1 to machine 2 or
ip address.
Hugs,
Rodrigo Fernandes
MCSE/MCDBA
"scott" wrote:
> I'm going to be installing a sql server behind a firewall. I'd appreciate
> any best practice advice on changing the default port number and also want
ed
> to know if there was a way to secure a sql server by granting access only
to
> specific IP addresses?
>
>|||What do you mean by saying 'but granting access ...'?
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in
message news:94A130F7-B240-44DF-9054-9E2BC08D9DE3@.microsoft.com...[vbcol=seagreen]
> Hi Scott,
> Really best practice is changing the default port number of the SQL
> Server.
> But granting access to specific IP addresses only machine 1 to machine 2
> or
> ip address.
> Hugs,
> Rodrigo Fernandes
> MCSE/MCDBA
> "scott" wrote:
>
any best practice advice on changing the default port number and also wanted
to know if there was a way to secure a sql server by granting access only to
specific IP addresses?Hi Scott,
Really best practice is changing the default port number of the SQL Server.
But granting access to specific IP addresses only machine 1 to machine 2 or
ip address.
Hugs,
Rodrigo Fernandes
MCSE/MCDBA
"scott" wrote:
> I'm going to be installing a sql server behind a firewall. I'd appreciate
> any best practice advice on changing the default port number and also want
ed
> to know if there was a way to secure a sql server by granting access only
to
> specific IP addresses?
>
>|||What do you mean by saying 'but granting access ...'?
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in
message news:94A130F7-B240-44DF-9054-9E2BC08D9DE3@.microsoft.com...[vbcol=seagreen]
> Hi Scott,
> Really best practice is changing the default port number of the SQL
> Server.
> But granting access to specific IP addresses only machine 1 to machine 2
> or
> ip address.
> Hugs,
> Rodrigo Fernandes
> MCSE/MCDBA
> "scott" wrote:
>
Restrict Port Security
I'm going to be installing a sql server behind a firewall. I'd appreciate
any best practice advice on changing the default port number and also wanted
to know if there was a way to secure a sql server by granting access only to
specific IP addresses?Hi Scott,
Really best practice is changing the default port number of the SQL Server.
But granting access to specific IP addresses only machine 1 to machine 2 or
ip address.
Hugs,
Rodrigo Fernandes
MCSE/MCDBA
"scott" wrote:
> I'm going to be installing a sql server behind a firewall. I'd appreciate
> any best practice advice on changing the default port number and also wanted
> to know if there was a way to secure a sql server by granting access only to
> specific IP addresses?
>
>|||What do you mean by saying 'but granting access ...'?
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in
message news:94A130F7-B240-44DF-9054-9E2BC08D9DE3@.microsoft.com...
> Hi Scott,
> Really best practice is changing the default port number of the SQL
> Server.
> But granting access to specific IP addresses only machine 1 to machine 2
> or
> ip address.
> Hugs,
> Rodrigo Fernandes
> MCSE/MCDBA
> "scott" wrote:
>> I'm going to be installing a sql server behind a firewall. I'd appreciate
>> any best practice advice on changing the default port number and also
>> wanted
>> to know if there was a way to secure a sql server by granting access only
>> to
>> specific IP addresses?
>>sql
any best practice advice on changing the default port number and also wanted
to know if there was a way to secure a sql server by granting access only to
specific IP addresses?Hi Scott,
Really best practice is changing the default port number of the SQL Server.
But granting access to specific IP addresses only machine 1 to machine 2 or
ip address.
Hugs,
Rodrigo Fernandes
MCSE/MCDBA
"scott" wrote:
> I'm going to be installing a sql server behind a firewall. I'd appreciate
> any best practice advice on changing the default port number and also wanted
> to know if there was a way to secure a sql server by granting access only to
> specific IP addresses?
>
>|||What do you mean by saying 'but granting access ...'?
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in
message news:94A130F7-B240-44DF-9054-9E2BC08D9DE3@.microsoft.com...
> Hi Scott,
> Really best practice is changing the default port number of the SQL
> Server.
> But granting access to specific IP addresses only machine 1 to machine 2
> or
> ip address.
> Hugs,
> Rodrigo Fernandes
> MCSE/MCDBA
> "scott" wrote:
>> I'm going to be installing a sql server behind a firewall. I'd appreciate
>> any best practice advice on changing the default port number and also
>> wanted
>> to know if there was a way to secure a sql server by granting access only
>> to
>> specific IP addresses?
>>sql
Restrict number of connections to a databse.
Hi
Is there anyway I can restrict the number of connections to a SQL Server
database ?
How can we do that.?
Also what is the best way to find out number of current connections to the
database using a query ?
Thanksselect @.@.maxconnections
can be set using sp_configure
sp_who and sp_who2 show you the number of connections
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>|||Hi,
Is there anyway I can restrict the number of connections to a SQL Server
database ?
You can restrict the connections only SQL Server wide not database level.
How can we do that.?
SP_configure 'user connections',numberofconnections
(By default it is 0 and allow maximum)
Also what is the best way to find out number of current connections to the
database using a query ?
select substring(db_name(dbid),1,30) as DB_name ,count(*) as Connection
from sysprocesses
group by substring(db_name(dbid),1,30)
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>|||bear in mind that the connections is only configurable at a server level and
not at a database level, thus sp_configure 'show advanced'
,1 must be set inorder to be able to view and set this option.
also to finding the current active connections can be done by using sp_who2
active
this shows you the active users only.
Olu Adedeji
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>
Is there anyway I can restrict the number of connections to a SQL Server
database ?
How can we do that.?
Also what is the best way to find out number of current connections to the
database using a query ?
Thanksselect @.@.maxconnections
can be set using sp_configure
sp_who and sp_who2 show you the number of connections
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>|||Hi,
Is there anyway I can restrict the number of connections to a SQL Server
database ?
You can restrict the connections only SQL Server wide not database level.
How can we do that.?
SP_configure 'user connections',numberofconnections
(By default it is 0 and allow maximum)
Also what is the best way to find out number of current connections to the
database using a query ?
select substring(db_name(dbid),1,30) as DB_name ,count(*) as Connection
from sysprocesses
group by substring(db_name(dbid),1,30)
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>|||bear in mind that the connections is only configurable at a server level and
not at a database level, thus sp_configure 'show advanced'
,1 must be set inorder to be able to view and set this option.
also to finding the current active connections can be done by using sp_who2
active
this shows you the active users only.
Olu Adedeji
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>
Restrict number of connections to a databse.
Hi
Is there anyway I can restrict the number of connections to a SQL Server
database ?
How can we do that.?
Also what is the best way to find out number of current connections to the
database using a query ?
Thanksselect @.@.maxconnections
can be set using sp_configure
sp_who and sp_who2 show you the number of connections
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>|||Hi,
Is there anyway I can restrict the number of connections to a SQL Server
database ?
You can restrict the connections only SQL Server wide not database level.
How can we do that.?
SP_configure 'user connections',numberofconnections
(By default it is 0 and allow maximum)
Also what is the best way to find out number of current connections to the
database using a query ?
select substring(db_name(dbid),1,30) as DB_name ,count(*) as Connection
from sysprocesses
group by substring(db_name(dbid),1,30)
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>|||bear in mind that the connections is only configurable at a server level and
not at a database level, thus sp_configure 'show advanced'
,1 must be set inorder to be able to view and set this option.
also to finding the current active connections can be done by using sp_who2
active
this shows you the active users only.
--
Olu Adedeji
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>
Is there anyway I can restrict the number of connections to a SQL Server
database ?
How can we do that.?
Also what is the best way to find out number of current connections to the
database using a query ?
Thanksselect @.@.maxconnections
can be set using sp_configure
sp_who and sp_who2 show you the number of connections
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>|||Hi,
Is there anyway I can restrict the number of connections to a SQL Server
database ?
You can restrict the connections only SQL Server wide not database level.
How can we do that.?
SP_configure 'user connections',numberofconnections
(By default it is 0 and allow maximum)
Also what is the best way to find out number of current connections to the
database using a query ?
select substring(db_name(dbid),1,30) as DB_name ,count(*) as Connection
from sysprocesses
group by substring(db_name(dbid),1,30)
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>|||bear in mind that the connections is only configurable at a server level and
not at a database level, thus sp_configure 'show advanced'
,1 must be set inorder to be able to view and set this option.
also to finding the current active connections can be done by using sp_who2
active
this shows you the active users only.
--
Olu Adedeji
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:ODXIsYx#DHA.3500@.tk2msftngp13.phx.gbl...
> Hi
> Is there anyway I can restrict the number of connections to a SQL Server
> database ?
> How can we do that.?
> Also what is the best way to find out number of current connections to the
> database using a query ?
> Thanks
>
Restrict Logins
Hi All:
I'm running SQL Server 7 in an NT environment using SQL Server
Authentication for the logins.
I'm a developer who has recently accepted the responsibilities for SQL
Server maintenance. I'm not really qualified to do this, but I'm
learning. Lord knows I need all the help and suggestions I can get;
I'm readily maintaining the status quo and make any modifications only
after what I consider exhaustive testing on our "test server". I've
just been handed the task of restricting certain logins to a specific
machine.
I do know that when I look at the running processes in EM I can see
the machines the users are connecting from.
Is there some way I can trap this information, issue an alert that
will start a job to kill the offending process? Or, is there some way
I can use DMO to do this'
I had originally thought that maybe I could intercept the login and
make the appropriate checks and deny the login... But, I haven't
found anything that indicates this approach is "do-able".
Then I found the active processes information and I began to wonder if
there wasn't some way to trap this information and issue a process
kill all in unattended mode.
I'm a developer and am not "married" to the EM ( it does make life and
learning easier).
Any and all help and suggestions are definitely appreciated.
TIA,
cathyHi,
If it is SQL Server Login or Windows user connecting to SQL server then you
can DENY the user to access the tables, If it is Host name
then you cant deny or revoke directly , But you can follow the below steps
to kill the user.
1. Create a script which looks like:-
use master
go
declare @.x varchar(1000)
set @.x=''
select @.x = @.x + ' kill ' + convert(varchar(5), spid)
from master.dbo.sysprocesses
where hostname = 'host_name' -- replace this with the actual host name
exec (@.x)
go
2. Schedule this script every 1 minute daily in SQL Agent -- Jobs. This will
check and the process if it logs in from the host name you mentioned.
Thanks
Hari
MCDBA
"Catherine Finnegan" <remove_for_nospam_caeriel1@.cablespeed.com> wrote in
message news:erfa90tjrncj89pugk4qr1bp7trkegui5n@.
4ax.com...
> Hi All:
> I'm running SQL Server 7 in an NT environment using SQL Server
> Authentication for the logins.
> I'm a developer who has recently accepted the responsibilities for SQL
> Server maintenance. I'm not really qualified to do this, but I'm
> learning. Lord knows I need all the help and suggestions I can get;
> I'm readily maintaining the status quo and make any modifications only
> after what I consider exhaustive testing on our "test server". I've
> just been handed the task of restricting certain logins to a specific
> machine.
> I do know that when I look at the running processes in EM I can see
> the machines the users are connecting from.
> Is there some way I can trap this information, issue an alert that
> will start a job to kill the offending process? Or, is there some way
> I can use DMO to do this'
> I had originally thought that maybe I could intercept the login and
> make the appropriate checks and deny the login... But, I haven't
> found anything that indicates this approach is "do-able".
> Then I found the active processes information and I began to wonder if
> there wasn't some way to trap this information and issue a process
> kill all in unattended mode.
> I'm a developer and am not "married" to the EM ( it does make life and
> learning easier).
>
> Any and all help and suggestions are definitely appreciated.
> TIA,
> cathy
I'm running SQL Server 7 in an NT environment using SQL Server
Authentication for the logins.
I'm a developer who has recently accepted the responsibilities for SQL
Server maintenance. I'm not really qualified to do this, but I'm
learning. Lord knows I need all the help and suggestions I can get;
I'm readily maintaining the status quo and make any modifications only
after what I consider exhaustive testing on our "test server". I've
just been handed the task of restricting certain logins to a specific
machine.
I do know that when I look at the running processes in EM I can see
the machines the users are connecting from.
Is there some way I can trap this information, issue an alert that
will start a job to kill the offending process? Or, is there some way
I can use DMO to do this'
I had originally thought that maybe I could intercept the login and
make the appropriate checks and deny the login... But, I haven't
found anything that indicates this approach is "do-able".
Then I found the active processes information and I began to wonder if
there wasn't some way to trap this information and issue a process
kill all in unattended mode.
I'm a developer and am not "married" to the EM ( it does make life and
learning easier).
Any and all help and suggestions are definitely appreciated.
TIA,
cathyHi,
If it is SQL Server Login or Windows user connecting to SQL server then you
can DENY the user to access the tables, If it is Host name
then you cant deny or revoke directly , But you can follow the below steps
to kill the user.
1. Create a script which looks like:-
use master
go
declare @.x varchar(1000)
set @.x=''
select @.x = @.x + ' kill ' + convert(varchar(5), spid)
from master.dbo.sysprocesses
where hostname = 'host_name' -- replace this with the actual host name
exec (@.x)
go
2. Schedule this script every 1 minute daily in SQL Agent -- Jobs. This will
check and the process if it logs in from the host name you mentioned.
Thanks
Hari
MCDBA
"Catherine Finnegan" <remove_for_nospam_caeriel1@.cablespeed.com> wrote in
message news:erfa90tjrncj89pugk4qr1bp7trkegui5n@.
4ax.com...
> Hi All:
> I'm running SQL Server 7 in an NT environment using SQL Server
> Authentication for the logins.
> I'm a developer who has recently accepted the responsibilities for SQL
> Server maintenance. I'm not really qualified to do this, but I'm
> learning. Lord knows I need all the help and suggestions I can get;
> I'm readily maintaining the status quo and make any modifications only
> after what I consider exhaustive testing on our "test server". I've
> just been handed the task of restricting certain logins to a specific
> machine.
> I do know that when I look at the running processes in EM I can see
> the machines the users are connecting from.
> Is there some way I can trap this information, issue an alert that
> will start a job to kill the offending process? Or, is there some way
> I can use DMO to do this'
> I had originally thought that maybe I could intercept the login and
> make the appropriate checks and deny the login... But, I haven't
> found anything that indicates this approach is "do-able".
> Then I found the active processes information and I began to wonder if
> there wasn't some way to trap this information and issue a process
> kill all in unattended mode.
> I'm a developer and am not "married" to the EM ( it does make life and
> learning easier).
>
> Any and all help and suggestions are definitely appreciated.
> TIA,
> cathy
Restrict login to one instance
Hello All,
I'm using SQL Server 2000 and wish to restrict the user login to one
instance only. Is this possible? Is this wise?"Fred Payne" <fpayne@.ryerson.ca> wrote in message
news:bnohcm$u46$1@.news.ryerson.ca...
> Hello All,
> I'm using SQL Server 2000 and wish to restrict the user login to one
> instance only. Is this possible? Is this wise?
I'm using SQL Server 2000 and wish to restrict the user login to one
instance only. Is this possible? Is this wise?"Fred Payne" <fpayne@.ryerson.ca> wrote in message
news:bnohcm$u46$1@.news.ryerson.ca...
> Hello All,
> I'm using SQL Server 2000 and wish to restrict the user login to one
> instance only. Is this possible? Is this wise?
Not sure what you mean. You can sit a database to single user, but that's
generally only for maintenance purposes.
Without knowing more, I can only say it's probably NOT wise.
|||"Fred Payne" <fpayne@.ryerson.ca> wrote in message
news:bnohcm$u46$1@.news.ryerson.ca...
> Hello All,
> I'm using SQL Server 2000 and wish to restrict the user login to one
> instance only. Is this possible? Is this wise?
Not sure what you mean. You can sit a database to single user, but that's
generally only for maintenance purposes.
Without knowing more, I can only say it's probably NOT wise.
|||"Fred Payne" <fpayne@.ryerson.ca> wrote in message news:<bnohcm$u46$1@.news.ryerson.ca>...
> Hello All,
> I'm using SQL Server 2000 and wish to restrict the user login to one
> instance only. Is this possible? Is this wise?
Assuming that you mean 'instance' as in 'named instance' (see Books
Online), then each instance is a separate installation, so you manage
security separately for each one. If a user doesn't need access to one
of the instances, then you simply don't grant access to that one. If
this doesn't help, perhaps you could clarify exactly what you want to
achieve?
Simon
restrict local cube
Does anyone know how can we prevent users from creating offline/local cube (in Excel) from our OLAP/Analysis Service server ?
Thank you.I'm almost certain that this isn't possible. There are ways that you can make it a bit more difficult, but those will only annoy your users.
If you find some way to do this, please let me know how you went about it.
-PatP|||Deny them access to the cubes ?|||Deny them access to the cubes ?
No, users still allowed to perform live access but must be prevented from creating local (offline) cube which they can bring home.
I'm aware of a new property introduced on SQL 2000 SP3a named RESTRICTED CLIENT but not sure if it has anything to do with our need nor how to set it up.sql
Thank you.I'm almost certain that this isn't possible. There are ways that you can make it a bit more difficult, but those will only annoy your users.
If you find some way to do this, please let me know how you went about it.
-PatP|||Deny them access to the cubes ?|||Deny them access to the cubes ?
No, users still allowed to perform live access but must be prevented from creating local (offline) cube which they can bring home.
I'm aware of a new property introduced on SQL 2000 SP3a named RESTRICTED CLIENT but not sure if it has anything to do with our need nor how to set it up.sql
Restrict Key to SA only in SQL 2005
I have data that I want to encrypt and have the key avaiable to SA
users only. How can I revoke (or prevent) DBO from using the key? I
tried
deny view definition on symmetric key::SSKey to dbo
and got...
Cannot grant, deny, or revoke permissions to sa, dbo,
information_schema, sys, or yourself.
Any suggestions?
JimYou can have SA be the only dbo in that database or you can protect the key
with a password that is only known to SA and is the only encryption method
for the key.
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1147976354.347670.55540@.i40g2000cwc.googlegroups.com...
>I have data that I want to encrypt and have the key avaiable to SA
> users only. How can I revoke (or prevent) DBO from using the key? I
> tried
> deny view definition on symmetric key::SSKey to dbo
> and got...
> Cannot grant, deny, or revoke permissions to sa, dbo,
> information_schema, sys, or yourself.
> Any suggestions?
> Jim
>
users only. How can I revoke (or prevent) DBO from using the key? I
tried
deny view definition on symmetric key::SSKey to dbo
and got...
Cannot grant, deny, or revoke permissions to sa, dbo,
information_schema, sys, or yourself.
Any suggestions?
JimYou can have SA be the only dbo in that database or you can protect the key
with a password that is only known to SA and is the only encryption method
for the key.
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1147976354.347670.55540@.i40g2000cwc.googlegroups.com...
>I have data that I want to encrypt and have the key avaiable to SA
> users only. How can I revoke (or prevent) DBO from using the key? I
> tried
> deny view definition on symmetric key::SSKey to dbo
> and got...
> Cannot grant, deny, or revoke permissions to sa, dbo,
> information_schema, sys, or yourself.
> Any suggestions?
> Jim
>
Restrict IP connections
I need to modify SQL Server 2000 Enterprise Edition so that it only
listens on the two local IP addresses, not the IP address of the
Internet.
I found KB article Q244980, but the "Maintain a Virtual Server for
Failover Clustering" can be selected.
Is there a registry setting or something where I can set which IP's SQL
server listens on?
--DaveHi Dave,
As far as I know that's not possible. Your best option is probably to close
ports 1433 and 1434 on your firewall. These are the default ports SQL Server
listens on. If you changed the ports, you of course need to close these. If
you're running on Windows 2003, you can restrict IP's on the network level.
Karl Gram, BSc, MBA
http://www.gramonline.com
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1acfa74cf9ba4b9a9897d5@.news-40.giganews.com...
> I need to modify SQL Server 2000 Enterprise Edition so that it only
> listens on the two local IP addresses, not the IP address of the
> Internet.
> I found KB article Q244980, but the "Maintain a Virtual Server for
> Failover Clustering" can be selected.
> Is there a registry setting or something where I can set which IP's SQL
> server listens on?
> --Dave
listens on the two local IP addresses, not the IP address of the
Internet.
I found KB article Q244980, but the "Maintain a Virtual Server for
Failover Clustering" can be selected.
Is there a registry setting or something where I can set which IP's SQL
server listens on?
--DaveHi Dave,
As far as I know that's not possible. Your best option is probably to close
ports 1433 and 1434 on your firewall. These are the default ports SQL Server
listens on. If you changed the ports, you of course need to close these. If
you're running on Windows 2003, you can restrict IP's on the network level.
Karl Gram, BSc, MBA
http://www.gramonline.com
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1acfa74cf9ba4b9a9897d5@.news-40.giganews.com...
> I need to modify SQL Server 2000 Enterprise Edition so that it only
> listens on the two local IP addresses, not the IP address of the
> Internet.
> I found KB article Q244980, but the "Maintain a Virtual Server for
> Failover Clustering" can be selected.
> Is there a registry setting or something where I can set which IP's SQL
> server listens on?
> --Dave
Restrict internet access using ODBC?
I was wondering if it was possible to remove internet access from a PC but,
still run tracsactions over the internet using our software program which is
connected the an internet accessable sql server via odbc? Our SQL sales
program processes credit and gift cards, because of this all of the PCs have
had internet access. Will OBDC still act as a "proxy" for us for internet
access instead of having to have the PC "on the internet"?
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>I was wondering if it was possible to remove internet access from a PC but,
> still run tracsactions over the internet using our software program which
> is
> connected the an internet accessable sql server via odbc? Our SQL sales
> program processes credit and gift cards, because of this all of the PCs
> have
> had internet access. Will OBDC still act as a "proxy" for us for internet
> access instead of having to have the PC "on the internet"?
Hi James,
I'm not an expert in this area but...
When you say you want to "remove internet access" do you mean that you want
to prevent users from using browsers or email? In order to communicate with
the offsite SQL Server you'll need "internet access" but you may be able to
block some outgoing and incoming ports, restricting the internet access to
your application.
Cindy Winegarden
cindy@.cindywinegarden.com
VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
|||I'm sorry if I was not totally clear. The SQL box is on the same physical
lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
chat, browsing, etc.)
"Cindy Winegarden" wrote:
> "JamesB" <JamesB@.discussions.microsoft.com> wrote in message
> news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>
> Hi James,
> I'm not an expert in this area but...
> When you say you want to "remove internet access" do you mean that you want
> to prevent users from using browsers or email? In order to communicate with
> the offsite SQL Server you'll need "internet access" but you may be able to
> block some outgoing and incoming ports, restricting the internet access to
> your application.
> --
> Cindy Winegarden
> cindy@.cindywinegarden.com
> VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
> VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
>
|||That scenario should work, if the SQL Server box is on the same internal
network, you don't need internet access to connect. You can just use TCP/IP
on the local network.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:53EED798-0059-483B-85AD-08907B30E59A@.microsoft.com...[vbcol=seagreen]
> I'm sorry if I was not totally clear. The SQL box is on the same physical
> lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
> chat, browsing, etc.)
> "Cindy Winegarden" wrote:
still run tracsactions over the internet using our software program which is
connected the an internet accessable sql server via odbc? Our SQL sales
program processes credit and gift cards, because of this all of the PCs have
had internet access. Will OBDC still act as a "proxy" for us for internet
access instead of having to have the PC "on the internet"?
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>I was wondering if it was possible to remove internet access from a PC but,
> still run tracsactions over the internet using our software program which
> is
> connected the an internet accessable sql server via odbc? Our SQL sales
> program processes credit and gift cards, because of this all of the PCs
> have
> had internet access. Will OBDC still act as a "proxy" for us for internet
> access instead of having to have the PC "on the internet"?
Hi James,
I'm not an expert in this area but...
When you say you want to "remove internet access" do you mean that you want
to prevent users from using browsers or email? In order to communicate with
the offsite SQL Server you'll need "internet access" but you may be able to
block some outgoing and incoming ports, restricting the internet access to
your application.
Cindy Winegarden
cindy@.cindywinegarden.com
VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
|||I'm sorry if I was not totally clear. The SQL box is on the same physical
lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
chat, browsing, etc.)
"Cindy Winegarden" wrote:
> "JamesB" <JamesB@.discussions.microsoft.com> wrote in message
> news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>
> Hi James,
> I'm not an expert in this area but...
> When you say you want to "remove internet access" do you mean that you want
> to prevent users from using browsers or email? In order to communicate with
> the offsite SQL Server you'll need "internet access" but you may be able to
> block some outgoing and incoming ports, restricting the internet access to
> your application.
> --
> Cindy Winegarden
> cindy@.cindywinegarden.com
> VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
> VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
>
|||That scenario should work, if the SQL Server box is on the same internal
network, you don't need internet access to connect. You can just use TCP/IP
on the local network.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:53EED798-0059-483B-85AD-08907B30E59A@.microsoft.com...[vbcol=seagreen]
> I'm sorry if I was not totally clear. The SQL box is on the same physical
> lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
> chat, browsing, etc.)
> "Cindy Winegarden" wrote:
Restrict internet access using ODBC?
I was wondering if it was possible to remove internet access from a PC but,
still run tracsactions over the internet using our software program which is
connected the an internet accessable sql server via odbc? Our SQL sales
program processes credit and gift cards, because of this all of the PCs have
had internet access. Will OBDC still act as a "proxy" for us for internet
access instead of having to have the PC "on the internet"?"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>I was wondering if it was possible to remove internet access from a PC but,
> still run tracsactions over the internet using our software program which
> is
> connected the an internet accessable sql server via odbc? Our SQL sales
> program processes credit and gift cards, because of this all of the PCs
> have
> had internet access. Will OBDC still act as a "proxy" for us for internet
> access instead of having to have the PC "on the internet"?
Hi James,
I'm not an expert in this area but...
When you say you want to "remove internet access" do you mean that you want
to prevent users from using browsers or email? In order to communicate with
the offsite SQL Server you'll need "internet access" but you may be able to
block some outgoing and incoming ports, restricting the internet access to
your application.
Cindy Winegarden
cindy@.cindywinegarden.com
VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx|||I'm sorry if I was not totally clear. The SQL box is on the same physical
lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
chat, browsing, etc.)
"Cindy Winegarden" wrote:
> "JamesB" <JamesB@.discussions.microsoft.com> wrote in message
> news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>
> Hi James,
> I'm not an expert in this area but...
> When you say you want to "remove internet access" do you mean that you wan
t
> to prevent users from using browsers or email? In order to communicate wit
h
> the offsite SQL Server you'll need "internet access" but you may be able t
o
> block some outgoing and incoming ports, restricting the internet access to
> your application.
> --
> Cindy Winegarden
> cindy@.cindywinegarden.com
> VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
> VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
>|||That scenario should work, if the SQL Server box is on the same internal
network, you don't need internet access to connect. You can just use TCP/IP
on the local network.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:53EED798-0059-483B-85AD-08907B30E59A@.microsoft.com...[vbcol=seagreen]
> I'm sorry if I was not totally clear. The SQL box is on the same physical
> lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
> chat, browsing, etc.)
> "Cindy Winegarden" wrote:
>
still run tracsactions over the internet using our software program which is
connected the an internet accessable sql server via odbc? Our SQL sales
program processes credit and gift cards, because of this all of the PCs have
had internet access. Will OBDC still act as a "proxy" for us for internet
access instead of having to have the PC "on the internet"?"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>I was wondering if it was possible to remove internet access from a PC but,
> still run tracsactions over the internet using our software program which
> is
> connected the an internet accessable sql server via odbc? Our SQL sales
> program processes credit and gift cards, because of this all of the PCs
> have
> had internet access. Will OBDC still act as a "proxy" for us for internet
> access instead of having to have the PC "on the internet"?
Hi James,
I'm not an expert in this area but...
When you say you want to "remove internet access" do you mean that you want
to prevent users from using browsers or email? In order to communicate with
the offsite SQL Server you'll need "internet access" but you may be able to
block some outgoing and incoming ports, restricting the internet access to
your application.
Cindy Winegarden
cindy@.cindywinegarden.com
VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx|||I'm sorry if I was not totally clear. The SQL box is on the same physical
lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
chat, browsing, etc.)
"Cindy Winegarden" wrote:
> "JamesB" <JamesB@.discussions.microsoft.com> wrote in message
> news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>
> Hi James,
> I'm not an expert in this area but...
> When you say you want to "remove internet access" do you mean that you wan
t
> to prevent users from using browsers or email? In order to communicate wit
h
> the offsite SQL Server you'll need "internet access" but you may be able t
o
> block some outgoing and incoming ports, restricting the internet access to
> your application.
> --
> Cindy Winegarden
> cindy@.cindywinegarden.com
> VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
> VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
>|||That scenario should work, if the SQL Server box is on the same internal
network, you don't need internet access to connect. You can just use TCP/IP
on the local network.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:53EED798-0059-483B-85AD-08907B30E59A@.microsoft.com...[vbcol=seagreen]
> I'm sorry if I was not totally clear. The SQL box is on the same physical
> lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
> chat, browsing, etc.)
> "Cindy Winegarden" wrote:
>
Restrict ing Sum(...) in Report Table Footer?
Hello,
My Report Table contains a Date Field and a numeric Value field. In the
Footer row of the table I use the Sum Aggregate function to sum up all the
values for all the rows that get displayed
=sum(Fields!CountFld.Value)
I also have an conditional Expression for the background color of each cell
in the detail row
=iif(Parameters!EndDate.Value > Fields!
CurExpireDate.Value, "Gainsboro", "White")
which changes the background color of each cell in the row (I add this IIF
expression to each field in the row).
So, if a value is in a row that is the gainsboro color, I do not want to sum
that value. Is there a way to restrict sum(Fields!CountFld.Value) ?
PseudoCode
=Sum(iif(Parameters!EndDate.Value > Fields!
CurExpireDate.Value, Fields!CountFld.Value, 0))
something like this?
Thanks,
RichWell, I tried my own pseudocode and it worked!
=Sum(iif(Parameters!EndDate.Value > Fields!
> CurExpireDate.Value, 0, Fields!CountFld.Value))
I just had to change the values around
"Rich" wrote:
> Hello,
> My Report Table contains a Date Field and a numeric Value field. In the
> Footer row of the table I use the Sum Aggregate function to sum up all the
> values for all the rows that get displayed
> =sum(Fields!CountFld.Value)
> I also have an conditional Expression for the background color of each cell
> in the detail row
> =iif(Parameters!EndDate.Value > Fields!
> CurExpireDate.Value, "Gainsboro", "White")
> which changes the background color of each cell in the row (I add this IIF
> expression to each field in the row).
> So, if a value is in a row that is the gainsboro color, I do not want to sum
> that value. Is there a way to restrict sum(Fields!CountFld.Value) ?
> PseudoCode
> =Sum(iif(Parameters!EndDate.Value > Fields!
> CurExpireDate.Value, Fields!CountFld.Value, 0))
> something like this?
> Thanks,
> Rich
>|||I used the same method, but have run into 1 problem. If the table or group
spans multiple pages, the sum fails (I get #Error). Not sure if you'll
encounter this, but I thought I would make you aware of my issue using that
method.
Michael
"Rich" wrote:
> Well, I tried my own pseudocode and it worked!
> =Sum(iif(Parameters!EndDate.Value > Fields!
> > CurExpireDate.Value, 0, Fields!CountFld.Value))
> I just had to change the values around
>
> "Rich" wrote:
> > Hello,
> >
> > My Report Table contains a Date Field and a numeric Value field. In the
> > Footer row of the table I use the Sum Aggregate function to sum up all the
> > values for all the rows that get displayed
> >
> > =sum(Fields!CountFld.Value)
> >
> > I also have an conditional Expression for the background color of each cell
> > in the detail row
> >
> > =iif(Parameters!EndDate.Value > Fields!
> > CurExpireDate.Value, "Gainsboro", "White")
> >
> > which changes the background color of each cell in the row (I add this IIF
> > expression to each field in the row).
> >
> > So, if a value is in a row that is the gainsboro color, I do not want to sum
> > that value. Is there a way to restrict sum(Fields!CountFld.Value) ?
> >
> > PseudoCode
> > =Sum(iif(Parameters!EndDate.Value > Fields!
> > CurExpireDate.Value, Fields!CountFld.Value, 0))
> >
> > something like this?
> >
> > Thanks,
> > Rich
> >|||Thanks. Not the kind of surprises I am looking forward to. Hopefully, I
won't run into that problem, but if I do, I will now know that I am not
alone. This is actually quite encouraging because I have had some seroius
heartaches with RS (2000).
"Michael C" wrote:
> I used the same method, but have run into 1 problem. If the table or group
> spans multiple pages, the sum fails (I get #Error). Not sure if you'll
> encounter this, but I thought I would make you aware of my issue using that
> method.
> Michael
> "Rich" wrote:
> > Well, I tried my own pseudocode and it worked!
> >
> > =Sum(iif(Parameters!EndDate.Value > Fields!
> > > CurExpireDate.Value, 0, Fields!CountFld.Value))
> >
> > I just had to change the values around
> >
> >
> >
> > "Rich" wrote:
> >
> > > Hello,
> > >
> > > My Report Table contains a Date Field and a numeric Value field. In the
> > > Footer row of the table I use the Sum Aggregate function to sum up all the
> > > values for all the rows that get displayed
> > >
> > > =sum(Fields!CountFld.Value)
> > >
> > > I also have an conditional Expression for the background color of each cell
> > > in the detail row
> > >
> > > =iif(Parameters!EndDate.Value > Fields!
> > > CurExpireDate.Value, "Gainsboro", "White")
> > >
> > > which changes the background color of each cell in the row (I add this IIF
> > > expression to each field in the row).
> > >
> > > So, if a value is in a row that is the gainsboro color, I do not want to sum
> > > that value. Is there a way to restrict sum(Fields!CountFld.Value) ?
> > >
> > > PseudoCode
> > > =Sum(iif(Parameters!EndDate.Value > Fields!
> > > CurExpireDate.Value, Fields!CountFld.Value, 0))
> > >
> > > something like this?
> > >
> > > Thanks,
> > > Rich
> > >sql
My Report Table contains a Date Field and a numeric Value field. In the
Footer row of the table I use the Sum Aggregate function to sum up all the
values for all the rows that get displayed
=sum(Fields!CountFld.Value)
I also have an conditional Expression for the background color of each cell
in the detail row
=iif(Parameters!EndDate.Value > Fields!
CurExpireDate.Value, "Gainsboro", "White")
which changes the background color of each cell in the row (I add this IIF
expression to each field in the row).
So, if a value is in a row that is the gainsboro color, I do not want to sum
that value. Is there a way to restrict sum(Fields!CountFld.Value) ?
PseudoCode
=Sum(iif(Parameters!EndDate.Value > Fields!
CurExpireDate.Value, Fields!CountFld.Value, 0))
something like this?
Thanks,
RichWell, I tried my own pseudocode and it worked!
=Sum(iif(Parameters!EndDate.Value > Fields!
> CurExpireDate.Value, 0, Fields!CountFld.Value))
I just had to change the values around
"Rich" wrote:
> Hello,
> My Report Table contains a Date Field and a numeric Value field. In the
> Footer row of the table I use the Sum Aggregate function to sum up all the
> values for all the rows that get displayed
> =sum(Fields!CountFld.Value)
> I also have an conditional Expression for the background color of each cell
> in the detail row
> =iif(Parameters!EndDate.Value > Fields!
> CurExpireDate.Value, "Gainsboro", "White")
> which changes the background color of each cell in the row (I add this IIF
> expression to each field in the row).
> So, if a value is in a row that is the gainsboro color, I do not want to sum
> that value. Is there a way to restrict sum(Fields!CountFld.Value) ?
> PseudoCode
> =Sum(iif(Parameters!EndDate.Value > Fields!
> CurExpireDate.Value, Fields!CountFld.Value, 0))
> something like this?
> Thanks,
> Rich
>|||I used the same method, but have run into 1 problem. If the table or group
spans multiple pages, the sum fails (I get #Error). Not sure if you'll
encounter this, but I thought I would make you aware of my issue using that
method.
Michael
"Rich" wrote:
> Well, I tried my own pseudocode and it worked!
> =Sum(iif(Parameters!EndDate.Value > Fields!
> > CurExpireDate.Value, 0, Fields!CountFld.Value))
> I just had to change the values around
>
> "Rich" wrote:
> > Hello,
> >
> > My Report Table contains a Date Field and a numeric Value field. In the
> > Footer row of the table I use the Sum Aggregate function to sum up all the
> > values for all the rows that get displayed
> >
> > =sum(Fields!CountFld.Value)
> >
> > I also have an conditional Expression for the background color of each cell
> > in the detail row
> >
> > =iif(Parameters!EndDate.Value > Fields!
> > CurExpireDate.Value, "Gainsboro", "White")
> >
> > which changes the background color of each cell in the row (I add this IIF
> > expression to each field in the row).
> >
> > So, if a value is in a row that is the gainsboro color, I do not want to sum
> > that value. Is there a way to restrict sum(Fields!CountFld.Value) ?
> >
> > PseudoCode
> > =Sum(iif(Parameters!EndDate.Value > Fields!
> > CurExpireDate.Value, Fields!CountFld.Value, 0))
> >
> > something like this?
> >
> > Thanks,
> > Rich
> >|||Thanks. Not the kind of surprises I am looking forward to. Hopefully, I
won't run into that problem, but if I do, I will now know that I am not
alone. This is actually quite encouraging because I have had some seroius
heartaches with RS (2000).
"Michael C" wrote:
> I used the same method, but have run into 1 problem. If the table or group
> spans multiple pages, the sum fails (I get #Error). Not sure if you'll
> encounter this, but I thought I would make you aware of my issue using that
> method.
> Michael
> "Rich" wrote:
> > Well, I tried my own pseudocode and it worked!
> >
> > =Sum(iif(Parameters!EndDate.Value > Fields!
> > > CurExpireDate.Value, 0, Fields!CountFld.Value))
> >
> > I just had to change the values around
> >
> >
> >
> > "Rich" wrote:
> >
> > > Hello,
> > >
> > > My Report Table contains a Date Field and a numeric Value field. In the
> > > Footer row of the table I use the Sum Aggregate function to sum up all the
> > > values for all the rows that get displayed
> > >
> > > =sum(Fields!CountFld.Value)
> > >
> > > I also have an conditional Expression for the background color of each cell
> > > in the detail row
> > >
> > > =iif(Parameters!EndDate.Value > Fields!
> > > CurExpireDate.Value, "Gainsboro", "White")
> > >
> > > which changes the background color of each cell in the row (I add this IIF
> > > expression to each field in the row).
> > >
> > > So, if a value is in a row that is the gainsboro color, I do not want to sum
> > > that value. Is there a way to restrict sum(Fields!CountFld.Value) ?
> > >
> > > PseudoCode
> > > =Sum(iif(Parameters!EndDate.Value > Fields!
> > > CurExpireDate.Value, Fields!CountFld.Value, 0))
> > >
> > > something like this?
> > >
> > > Thanks,
> > > Rich
> > >sql
restrict enterprise manager to see all the databases
Hi,
my question is about security, but I am sure it has something to do
with the server configuration. It is about normal users other than SA.
In the server, multiple users, and each user has ownership of a
seperate database. When any of these users connect to the sql server
via Enterprise Manager, they are able to see all the databases, not
only they owned, also other databases they do not have ownership of.
Not that, they can see the tables or the data, but this still
bothers the user(s). How can I restrict the normal users to see only
their database(s) when they logged in thru enterprise manager with
their username and password ?
I aplogize if this quesiton has asked before and answered. If so,
please direct me to the correct link, I could not seek it.
Thank you.
Hi,
As far as I know there is a no way to restrict this in enterprise manager.
You can only hide system databases and system tables
from SQL Server's Registration Properties.
Thanks
Hari
SQL Server MVP
"murtix van basten" <murtix@.gmail.com> wrote in message
news:1158899177.310068.275240@.d34g2000cwd.googlegr oups.com...
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>
|||murtix van basten wrote:
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>
Can't do this with Enterprise Manager...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thank you for the answers.
Tracy McKibben wrote:
> murtix van basten wrote:
> Can't do this with Enterprise Manager...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
my question is about security, but I am sure it has something to do
with the server configuration. It is about normal users other than SA.
In the server, multiple users, and each user has ownership of a
seperate database. When any of these users connect to the sql server
via Enterprise Manager, they are able to see all the databases, not
only they owned, also other databases they do not have ownership of.
Not that, they can see the tables or the data, but this still
bothers the user(s). How can I restrict the normal users to see only
their database(s) when they logged in thru enterprise manager with
their username and password ?
I aplogize if this quesiton has asked before and answered. If so,
please direct me to the correct link, I could not seek it.
Thank you.
Hi,
As far as I know there is a no way to restrict this in enterprise manager.
You can only hide system databases and system tables
from SQL Server's Registration Properties.
Thanks
Hari
SQL Server MVP
"murtix van basten" <murtix@.gmail.com> wrote in message
news:1158899177.310068.275240@.d34g2000cwd.googlegr oups.com...
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>
|||murtix van basten wrote:
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>
Can't do this with Enterprise Manager...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thank you for the answers.
Tracy McKibben wrote:
> murtix van basten wrote:
> Can't do this with Enterprise Manager...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
restrict enterprise manager to see all the databases
Hi,
my question is about security, but I am sure it has something to do
with the server configuration. It is about normal users other than SA.
In the server, multiple users, and each user has ownership of a
seperate database. When any of these users connect to the sql server
via Enterprise Manager, they are able to see all the databases, not
only they owned, also other databases they do not have ownership of.
Not that, they can see the tables or the data, but this still
bothers the user(s). How can I restrict the normal users to see only
their database(s) when they logged in thru enterprise manager with
their username and password ?
I aplogize if this quesiton has asked before and answered. If so,
please direct me to the correct link, I could not seek it.
Thank you.Hi,
As far as I know there is a no way to restrict this in enterprise manager.
You can only hide system databases and system tables
from SQL Server's Registration Properties.
Thanks
Hari
SQL Server MVP
"murtix van basten" <murtix@.gmail.com> wrote in message
news:1158899177.310068.275240@.d34g2000cwd.googlegroups.com...
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>|||murtix van basten wrote:
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>
Can't do this with Enterprise Manager...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for the answers.
Tracy McKibben wrote:
> murtix van basten wrote:
> > Hi,
> >
> > my question is about security, but I am sure it has something to do
> > with the server configuration. It is about normal users other than SA.
> >
> > In the server, multiple users, and each user has ownership of a
> > seperate database. When any of these users connect to the sql server
> > via Enterprise Manager, they are able to see all the databases, not
> > only they owned, also other databases they do not have ownership of.
> >
> > Not that, they can see the tables or the data, but this still
> > bothers the user(s). How can I restrict the normal users to see only
> > their database(s) when they logged in thru enterprise manager with
> > their username and password ?
> >
> > I aplogize if this quesiton has asked before and answered. If so,
> > please direct me to the correct link, I could not seek it.
> >
> > Thank you.
> >
> Can't do this with Enterprise Manager...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
my question is about security, but I am sure it has something to do
with the server configuration. It is about normal users other than SA.
In the server, multiple users, and each user has ownership of a
seperate database. When any of these users connect to the sql server
via Enterprise Manager, they are able to see all the databases, not
only they owned, also other databases they do not have ownership of.
Not that, they can see the tables or the data, but this still
bothers the user(s). How can I restrict the normal users to see only
their database(s) when they logged in thru enterprise manager with
their username and password ?
I aplogize if this quesiton has asked before and answered. If so,
please direct me to the correct link, I could not seek it.
Thank you.Hi,
As far as I know there is a no way to restrict this in enterprise manager.
You can only hide system databases and system tables
from SQL Server's Registration Properties.
Thanks
Hari
SQL Server MVP
"murtix van basten" <murtix@.gmail.com> wrote in message
news:1158899177.310068.275240@.d34g2000cwd.googlegroups.com...
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>|||murtix van basten wrote:
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>
Can't do this with Enterprise Manager...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for the answers.
Tracy McKibben wrote:
> murtix van basten wrote:
> > Hi,
> >
> > my question is about security, but I am sure it has something to do
> > with the server configuration. It is about normal users other than SA.
> >
> > In the server, multiple users, and each user has ownership of a
> > seperate database. When any of these users connect to the sql server
> > via Enterprise Manager, they are able to see all the databases, not
> > only they owned, also other databases they do not have ownership of.
> >
> > Not that, they can see the tables or the data, but this still
> > bothers the user(s). How can I restrict the normal users to see only
> > their database(s) when they logged in thru enterprise manager with
> > their username and password ?
> >
> > I aplogize if this quesiton has asked before and answered. If so,
> > please direct me to the correct link, I could not seek it.
> >
> > Thank you.
> >
> Can't do this with Enterprise Manager...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
restrict enterprise manager to see all the databases
Hi,
my question is about security, but I am sure it has something to do
with the server configuration. It is about normal users other than SA.
In the server, multiple users, and each user has ownership of a
seperate database. When any of these users connect to the sql server
via Enterprise Manager, they are able to see all the databases, not
only they owned, also other databases they do not have ownership of.
Not that, they can see the tables or the data, but this still
bothers the user(s). How can I restrict the normal users to see only
their database(s) when they logged in thru enterprise manager with
their username and password ?
I aplogize if this quesiton has asked before and answered. If so,
please direct me to the correct link, I could not seek it.
Thank you.Hi,
As far as I know there is a no way to restrict this in enterprise manager.
You can only hide system databases and system tables
from SQL Server's Registration Properties.
Thanks
Hari
SQL Server MVP
"murtix van basten" <murtix@.gmail.com> wrote in message
news:1158899177.310068.275240@.d34g2000cwd.googlegroups.com...
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>|||murtix van basten wrote:
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>
Can't do this with Enterprise Manager...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for the answers.
Tracy McKibben wrote:
> murtix van basten wrote:
> Can't do this with Enterprise Manager...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
my question is about security, but I am sure it has something to do
with the server configuration. It is about normal users other than SA.
In the server, multiple users, and each user has ownership of a
seperate database. When any of these users connect to the sql server
via Enterprise Manager, they are able to see all the databases, not
only they owned, also other databases they do not have ownership of.
Not that, they can see the tables or the data, but this still
bothers the user(s). How can I restrict the normal users to see only
their database(s) when they logged in thru enterprise manager with
their username and password ?
I aplogize if this quesiton has asked before and answered. If so,
please direct me to the correct link, I could not seek it.
Thank you.Hi,
As far as I know there is a no way to restrict this in enterprise manager.
You can only hide system databases and system tables
from SQL Server's Registration Properties.
Thanks
Hari
SQL Server MVP
"murtix van basten" <murtix@.gmail.com> wrote in message
news:1158899177.310068.275240@.d34g2000cwd.googlegroups.com...
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>|||murtix van basten wrote:
> Hi,
> my question is about security, but I am sure it has something to do
> with the server configuration. It is about normal users other than SA.
> In the server, multiple users, and each user has ownership of a
> seperate database. When any of these users connect to the sql server
> via Enterprise Manager, they are able to see all the databases, not
> only they owned, also other databases they do not have ownership of.
> Not that, they can see the tables or the data, but this still
> bothers the user(s). How can I restrict the normal users to see only
> their database(s) when they logged in thru enterprise manager with
> their username and password ?
> I aplogize if this quesiton has asked before and answered. If so,
> please direct me to the correct link, I could not seek it.
> Thank you.
>
Can't do this with Enterprise Manager...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for the answers.
Tracy McKibben wrote:
> murtix van basten wrote:
> Can't do this with Enterprise Manager...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Restrict DTS execution
Hi,
Does anyone know how to restrict the exceution of DTS packages?
The case is I've got some users which want to see the packages that are
currently created but I do not want them to run those packages. I already
know how to restrict the creation via sp_add_dtspackage.
Hi!
> Does anyone know how to restrict the exceution of DTS packages?
> The case is I've got some users which want to see the packages that are
> currently created but I do not want them to run those packages. I already
> know how to restrict the creation via sp_add_dtspackage.
I find this article very helpful:
http://www.windowsitpro.com/Article/...11/27111.html.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
Does anyone know how to restrict the exceution of DTS packages?
The case is I've got some users which want to see the packages that are
currently created but I do not want them to run those packages. I already
know how to restrict the creation via sp_add_dtspackage.
Hi!
> Does anyone know how to restrict the exceution of DTS packages?
> The case is I've got some users which want to see the packages that are
> currently created but I do not want them to run those packages. I already
> know how to restrict the creation via sp_add_dtspackage.
I find this article very helpful:
http://www.windowsitpro.com/Article/...11/27111.html.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
Restrict DTS execution
Hi,
Does anyone know how to restrict the exceution of DTS packages?
The case is I've got some users which want to see the packages that are
currently created but I do not want them to run those packages. I already
know how to restrict the creation via sp_add_dtspackage.Hi!
> Does anyone know how to restrict the exceution of DTS packages?
> The case is I've got some users which want to see the packages that are
> currently created but I do not want them to run those packages. I already
> know how to restrict the creation via sp_add_dtspackage.
I find this article very helpful:
http://www.windowsitpro.com/Article...111/27111.html.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this messagesql
Does anyone know how to restrict the exceution of DTS packages?
The case is I've got some users which want to see the packages that are
currently created but I do not want them to run those packages. I already
know how to restrict the creation via sp_add_dtspackage.Hi!
> Does anyone know how to restrict the exceution of DTS packages?
> The case is I've got some users which want to see the packages that are
> currently created but I do not want them to run those packages. I already
> know how to restrict the creation via sp_add_dtspackage.
I find this article very helpful:
http://www.windowsitpro.com/Article...111/27111.html.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this messagesql
Restrict DTS execution
Hi,
Does anyone know how to restrict the exceution of DTS packages?
The case is I've got some users which want to see the packages that are
currently created but I do not want them to run those packages. I already
know how to restrict the creation via sp_add_dtspackage.Hi!
> Does anyone know how to restrict the exceution of DTS packages?
> The case is I've got some users which want to see the packages that are
> currently created but I do not want them to run those packages. I already
> know how to restrict the creation via sp_add_dtspackage.
I find this article very helpful:
http://www.windowsitpro.com/Article/ArticleID/27111/27111.html.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
Does anyone know how to restrict the exceution of DTS packages?
The case is I've got some users which want to see the packages that are
currently created but I do not want them to run those packages. I already
know how to restrict the creation via sp_add_dtspackage.Hi!
> Does anyone know how to restrict the exceution of DTS packages?
> The case is I've got some users which want to see the packages that are
> currently created but I do not want them to run those packages. I already
> know how to restrict the creation via sp_add_dtspackage.
I find this article very helpful:
http://www.windowsitpro.com/Article/ArticleID/27111/27111.html.
--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
restrict deletion
What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query?
Thank you in advance.
Hi fpena,
You can just compare the username again to the current user to see if he is the owner of this record. If yes, delete it. All this can be done in a single stored procedure.
HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!
|||could you point me on the right direction? Thank you.|||Hi fpena,
You can use a single sentence to achieve that. Assume that there is a field in the table named RecordOwner, and your stored procedure passes in a parameter named @.Deleter. You can use
DELETE FROM Table1 WHERERowID=@.RowID ANDRecordOwner=@.Deleter
You can check the AffectedRows in your code. If it is 0, it means that the row does not exist or the deleter is not the owner, so that he cannot delete it.
|||i'm not sure i can accomplish what i'm looking for with that. i have a gridview visible to everyone all records visible no matter what userID owns the record what i'm trying to do is prevent a user who doesn't own a record from deleting when trying to delete. hope my question is clear enough. Thank you.|||Hi fpena,
I'm sure that this will do what you need. You just need to modify the delete command you're currently using to check the ownership info.
If you have any questions on how to do this, please feel free to reply to my post.
|||how is it done?
restrict delete function on odbc connection
i have the odbc connection connected to our SQL databasse via sql user
account through MS access Frontend and having problem controling the delete
function of the account.
In SQL i am only allow the account to read, insert,update and denied delete
on the database and tables. However, the account still be able delete the
records.
Can someone let me know what am i missing in the permission granting.
Check individual permissions, groups permissions and what is important the permissions granted to the public role where every database user will be added to.
Jens K. Suessmeyer
http://www.sqlserver2005.de
Restrict delete from a table
Hi
I need to restrict delete from one table by any user of SQL. How can we do this? This is our master table and we dont want any one to delete data from this table.
Thanks
BalaTry Cascade ON DELETE NO ACTION, you can use either code or set it on the table properties. Run a search for Cascade Delete in the BOL(books online) for more examples. NO ACTION is an ANSI SQL DRI(Declarative Referential Integrity) rule that prevents Deletes from tables. Hope this helps
CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int)
GO
Kind regards,
Gift Peddie
Restrict decimals
I have a SQL Query which is returning result as:
1000.0000
2000.0000
I want to restrict it to two decimal places. Which function to use? I
want to show as 1000.00.
Hi
You could change the datatype to be decimal (n,2) or use cast to force it.
Alternatively just leave the front end to format the value (assuming that
this is not QA!)
John
"RP" wrote:
> I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>
|||However, I believe that it would be more appropriate to format this kind of
data in the client app where possible. This would take over this conversion
overhead from the SQL Server. Especially for SQL Server servers which has
already heavy workload on it.
Ekrem nsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23IQBy0dIIHA.4688@.TK2MSFTNGP06.phx.gbl...
> Formatting of the binary data that SQL Server returns from a query is done
> by the client application. What client app are you using? Sometimes you
> don't have control over the client app and need to hint or force
> formatting so client app does it the way you want. This is generally done
> by CAST(colname AS datatype) in the SELECT list. What datatype to cast to
> you need to experiment to find out (since different client apps has
> different default formats). However some MUMERIC(x,2) seems a reasonable
> guess.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RP" <rpk.general@.gmail.com> wrote in message
> news:1194500049.949584.282260@.v29g2000prd.googlegr oups.com...
>
|||I am using C# 2005. The column data type is decimal (10,2) but when an
expression is retrieved by multiplying with anything this result is
returned.
How to use cast in SQL itself?
|||Can you illustrate with an example.
On Nov 8, 4:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SELECT
> CAST(col1*col2 AS desiredType) AS colname
> ,col3
|||Hi
You would need to know what your desiredType is, then just use the statement
Tibor gave, for instance if it was decimal(15,2)
SELECT
CAST(col1*col2 AS decimal(15,2)) AS colname
,col3
FROM yrTable
This may be clearer!
USE TEMPDB
GO
CREATE TABLE yrTable ( col1 decimal(10,2),
col2 decimal(10,2),
col3 decimal(10,3),
col4 decimal(10,4))
GO
INSERT INTO yrTable ( col1, col2, col3, col4 )
SELECT 2.05, 2.06, 2.005, 2.0005
GO
SELECT col1* col2 AS res1,
CAST(col1* col2 as decimal(15,2)) AS res2,
col1* col3 AS res3,
CAST(col1* col3 as decimal(15,2)) AS res4,
col1* col4 AS res5,
CAST(col1* col4 as decimal(15,2)) AS res6,
col3* col4 AS res7,
CAST(col3* col4 as decimal(15,2)) AS res8
FROM yrTable
/* returns
res1 res2 res3 res4 res5 res6 res7 res8
-- -- -- -- -- -- -- --
4.2230 4.22 4.11025 4.11 4.101025 4.10 4.0110025 4.01
(1 row(s) affected)
*/
"RP" wrote:
> Can you illustrate with an example.
> On Nov 8, 4:09 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>
>
sql
1000.0000
2000.0000
I want to restrict it to two decimal places. Which function to use? I
want to show as 1000.00.
Hi
You could change the datatype to be decimal (n,2) or use cast to force it.
Alternatively just leave the front end to format the value (assuming that
this is not QA!)
John
"RP" wrote:
> I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>
|||However, I believe that it would be more appropriate to format this kind of
data in the client app where possible. This would take over this conversion
overhead from the SQL Server. Especially for SQL Server servers which has
already heavy workload on it.
Ekrem nsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23IQBy0dIIHA.4688@.TK2MSFTNGP06.phx.gbl...
> Formatting of the binary data that SQL Server returns from a query is done
> by the client application. What client app are you using? Sometimes you
> don't have control over the client app and need to hint or force
> formatting so client app does it the way you want. This is generally done
> by CAST(colname AS datatype) in the SELECT list. What datatype to cast to
> you need to experiment to find out (since different client apps has
> different default formats). However some MUMERIC(x,2) seems a reasonable
> guess.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RP" <rpk.general@.gmail.com> wrote in message
> news:1194500049.949584.282260@.v29g2000prd.googlegr oups.com...
>
|||I am using C# 2005. The column data type is decimal (10,2) but when an
expression is retrieved by multiplying with anything this result is
returned.
How to use cast in SQL itself?
|||Can you illustrate with an example.
On Nov 8, 4:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SELECT
> CAST(col1*col2 AS desiredType) AS colname
> ,col3
|||Hi
You would need to know what your desiredType is, then just use the statement
Tibor gave, for instance if it was decimal(15,2)
SELECT
CAST(col1*col2 AS decimal(15,2)) AS colname
,col3
FROM yrTable
This may be clearer!
USE TEMPDB
GO
CREATE TABLE yrTable ( col1 decimal(10,2),
col2 decimal(10,2),
col3 decimal(10,3),
col4 decimal(10,4))
GO
INSERT INTO yrTable ( col1, col2, col3, col4 )
SELECT 2.05, 2.06, 2.005, 2.0005
GO
SELECT col1* col2 AS res1,
CAST(col1* col2 as decimal(15,2)) AS res2,
col1* col3 AS res3,
CAST(col1* col3 as decimal(15,2)) AS res4,
col1* col4 AS res5,
CAST(col1* col4 as decimal(15,2)) AS res6,
col3* col4 AS res7,
CAST(col3* col4 as decimal(15,2)) AS res8
FROM yrTable
/* returns
res1 res2 res3 res4 res5 res6 res7 res8
-- -- -- -- -- -- -- --
4.2230 4.22 4.11025 4.11 4.101025 4.10 4.0110025 4.01
(1 row(s) affected)
*/
"RP" wrote:
> Can you illustrate with an example.
> On Nov 8, 4:09 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>
>
sql
Subscribe to:
Posts (Atom)