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!
Showing posts with label restrict. Show all posts
Showing posts with label restrict. Show all posts
Friday, March 30, 2012
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...
Subscribe to:
Posts (Atom)