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.
>
Friday, March 30, 2012
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)
Subscribe to:
Posts (Atom)