We have 5 different users, and only the administrator can
open and use the programs, for example, Quickbooks can
only be opened by the administrator. How do we change
the "restriced access" to aloud the other users to open
the programs. All the programs and files are in the
server, now we need everyone to access them.
Thank you.This is a SQL Server newsgroup - your question doesn't have anything to do
with SQL Server!
How you go about unlocking security settings on your server based programs
will have much to do with how those programs work (ie are they client /
server programs or simple windows apps?) as well as how they are secured in
the first place - via policies, roles or individual dacls..
You should probably ask this question in a security related newsgroup
though..
Regards,
Greg Linwood
SQL Server MVP
"email@.caseymauldin.com" <anonymous@.discussions.microsoft.com> wrote in
message news:02c401c3c836$054987c0$a401280a@.phx.gbl...
> We have 5 different users, and only the administrator can
> open and use the programs, for example, Quickbooks can
> only be opened by the administrator. How do we change
> the "restriced access" to aloud the other users to open
> the programs. All the programs and files are in the
> server, now we need everyone to access them.
> Thank you.sql
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Friday, March 30, 2012
Restricted access to Enterprise manager
Hi,
I want to restrict the access to enterprise manager from the developer
computer. They need the access the develpment machine so they require
enterprise manager on their machines. But i do not want them to access
the production database from their workstation enterprise manager. Also
because they are developers they know the username/password for the
production database.
If there is any tool or utility which check who is login in and from
which program and than it can accept or reject the connection would ba
great help.
Regards,
Puneet
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
"Puneet Parashar" <parashar22@.hotmail.com> wrote in message
news:#tPxEX5YEHA.3432@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to restrict the access to enterprise manager from the developer
> computer. They need the access the develpment machine so they require
> enterprise manager on their machines. But i do not want them to access
> the production database from their workstation enterprise manager. Also
> because they are developers they know the username/password for the
> production database.
> If there is any tool or utility which check who is login in and from
> which program and than it can accept or reject the connection would ba
> great help.
>
> Regards,
> Puneet
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi,
Check the sql server security in BOL.
1) Delete the guest user account from the database.
2) Restrict the users and give the permissions only to the specific
databases which they need.
"Puneet Parashar" <parashar22@.hotmail.com> wrote in message
news:#tPxEX5YEHA.3432@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to restrict the access to enterprise manager from the developer
> computer. They need the access the develpment machine so they require
> enterprise manager on their machines. But i do not want them to access
> the production database from their workstation enterprise manager. Also
> because they are developers they know the username/password for the
> production database.
> If there is any tool or utility which check who is login in and from
> which program and than it can accept or reject the connection would ba
> great help.
>
> Regards,
> Puneet
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
I want to restrict the access to enterprise manager from the developer
computer. They need the access the develpment machine so they require
enterprise manager on their machines. But i do not want them to access
the production database from their workstation enterprise manager. Also
because they are developers they know the username/password for the
production database.
If there is any tool or utility which check who is login in and from
which program and than it can accept or reject the connection would ba
great help.
Regards,
Puneet
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
"Puneet Parashar" <parashar22@.hotmail.com> wrote in message
news:#tPxEX5YEHA.3432@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to restrict the access to enterprise manager from the developer
> computer. They need the access the develpment machine so they require
> enterprise manager on their machines. But i do not want them to access
> the production database from their workstation enterprise manager. Also
> because they are developers they know the username/password for the
> production database.
> If there is any tool or utility which check who is login in and from
> which program and than it can accept or reject the connection would ba
> great help.
>
> Regards,
> Puneet
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi,
Check the sql server security in BOL.
1) Delete the guest user account from the database.
2) Restrict the users and give the permissions only to the specific
databases which they need.
"Puneet Parashar" <parashar22@.hotmail.com> wrote in message
news:#tPxEX5YEHA.3432@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to restrict the access to enterprise manager from the developer
> computer. They need the access the develpment machine so they require
> enterprise manager on their machines. But i do not want them to access
> the production database from their workstation enterprise manager. Also
> because they are developers they know the username/password for the
> production database.
> If there is any tool or utility which check who is login in and from
> which program and than it can accept or reject the connection would ba
> great help.
>
> Regards,
> Puneet
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
Restrict 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 internet access using ODBC?
I was wondering if it was possible to remove internet access from a PC but,
still run tracsactions over the internet using our software program which is
connected the an internet accessable sql server via odbc? Our SQL sales
program processes credit and gift cards, because of this all of the PCs have
had internet access. Will OBDC still act as a "proxy" for us for internet
access instead of having to have the PC "on the internet"?
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>I was wondering if it was possible to remove internet access from a PC but,
> still run tracsactions over the internet using our software program which
> is
> connected the an internet accessable sql server via odbc? Our SQL sales
> program processes credit and gift cards, because of this all of the PCs
> have
> had internet access. Will OBDC still act as a "proxy" for us for internet
> access instead of having to have the PC "on the internet"?
Hi James,
I'm not an expert in this area but...
When you say you want to "remove internet access" do you mean that you want
to prevent users from using browsers or email? In order to communicate with
the offsite SQL Server you'll need "internet access" but you may be able to
block some outgoing and incoming ports, restricting the internet access to
your application.
Cindy Winegarden
cindy@.cindywinegarden.com
VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
|||I'm sorry if I was not totally clear. The SQL box is on the same physical
lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
chat, browsing, etc.)
"Cindy Winegarden" wrote:
> "JamesB" <JamesB@.discussions.microsoft.com> wrote in message
> news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>
> Hi James,
> I'm not an expert in this area but...
> When you say you want to "remove internet access" do you mean that you want
> to prevent users from using browsers or email? In order to communicate with
> the offsite SQL Server you'll need "internet access" but you may be able to
> block some outgoing and incoming ports, restricting the internet access to
> your application.
> --
> Cindy Winegarden
> cindy@.cindywinegarden.com
> VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
> VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
>
|||That scenario should work, if the SQL Server box is on the same internal
network, you don't need internet access to connect. You can just use TCP/IP
on the local network.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:53EED798-0059-483B-85AD-08907B30E59A@.microsoft.com...[vbcol=seagreen]
> I'm sorry if I was not totally clear. The SQL box is on the same physical
> lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
> chat, browsing, etc.)
> "Cindy Winegarden" wrote:
still run tracsactions over the internet using our software program which is
connected the an internet accessable sql server via odbc? Our SQL sales
program processes credit and gift cards, because of this all of the PCs have
had internet access. Will OBDC still act as a "proxy" for us for internet
access instead of having to have the PC "on the internet"?
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>I was wondering if it was possible to remove internet access from a PC but,
> still run tracsactions over the internet using our software program which
> is
> connected the an internet accessable sql server via odbc? Our SQL sales
> program processes credit and gift cards, because of this all of the PCs
> have
> had internet access. Will OBDC still act as a "proxy" for us for internet
> access instead of having to have the PC "on the internet"?
Hi James,
I'm not an expert in this area but...
When you say you want to "remove internet access" do you mean that you want
to prevent users from using browsers or email? In order to communicate with
the offsite SQL Server you'll need "internet access" but you may be able to
block some outgoing and incoming ports, restricting the internet access to
your application.
Cindy Winegarden
cindy@.cindywinegarden.com
VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
|||I'm sorry if I was not totally clear. The SQL box is on the same physical
lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
chat, browsing, etc.)
"Cindy Winegarden" wrote:
> "JamesB" <JamesB@.discussions.microsoft.com> wrote in message
> news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>
> Hi James,
> I'm not an expert in this area but...
> When you say you want to "remove internet access" do you mean that you want
> to prevent users from using browsers or email? In order to communicate with
> the offsite SQL Server you'll need "internet access" but you may be able to
> block some outgoing and incoming ports, restricting the internet access to
> your application.
> --
> Cindy Winegarden
> cindy@.cindywinegarden.com
> VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
> VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
>
|||That scenario should work, if the SQL Server box is on the same internal
network, you don't need internet access to connect. You can just use TCP/IP
on the local network.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:53EED798-0059-483B-85AD-08907B30E59A@.microsoft.com...[vbcol=seagreen]
> I'm sorry if I was not totally clear. The SQL box is on the same physical
> lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
> chat, browsing, etc.)
> "Cindy Winegarden" wrote:
Restrict internet access using ODBC?
I was wondering if it was possible to remove internet access from a PC but,
still run tracsactions over the internet using our software program which is
connected the an internet accessable sql server via odbc? Our SQL sales
program processes credit and gift cards, because of this all of the PCs have
had internet access. Will OBDC still act as a "proxy" for us for internet
access instead of having to have the PC "on the internet"?"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>I was wondering if it was possible to remove internet access from a PC but,
> still run tracsactions over the internet using our software program which
> is
> connected the an internet accessable sql server via odbc? Our SQL sales
> program processes credit and gift cards, because of this all of the PCs
> have
> had internet access. Will OBDC still act as a "proxy" for us for internet
> access instead of having to have the PC "on the internet"?
Hi James,
I'm not an expert in this area but...
When you say you want to "remove internet access" do you mean that you want
to prevent users from using browsers or email? In order to communicate with
the offsite SQL Server you'll need "internet access" but you may be able to
block some outgoing and incoming ports, restricting the internet access to
your application.
Cindy Winegarden
cindy@.cindywinegarden.com
VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx|||I'm sorry if I was not totally clear. The SQL box is on the same physical
lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
chat, browsing, etc.)
"Cindy Winegarden" wrote:
> "JamesB" <JamesB@.discussions.microsoft.com> wrote in message
> news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>
> Hi James,
> I'm not an expert in this area but...
> When you say you want to "remove internet access" do you mean that you wan
t
> to prevent users from using browsers or email? In order to communicate wit
h
> the offsite SQL Server you'll need "internet access" but you may be able t
o
> block some outgoing and incoming ports, restricting the internet access to
> your application.
> --
> Cindy Winegarden
> cindy@.cindywinegarden.com
> VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
> VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
>|||That scenario should work, if the SQL Server box is on the same internal
network, you don't need internet access to connect. You can just use TCP/IP
on the local network.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:53EED798-0059-483B-85AD-08907B30E59A@.microsoft.com...[vbcol=seagreen]
> I'm sorry if I was not totally clear. The SQL box is on the same physical
> lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
> chat, browsing, etc.)
> "Cindy Winegarden" wrote:
>
still run tracsactions over the internet using our software program which is
connected the an internet accessable sql server via odbc? Our SQL sales
program processes credit and gift cards, because of this all of the PCs have
had internet access. Will OBDC still act as a "proxy" for us for internet
access instead of having to have the PC "on the internet"?"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>I was wondering if it was possible to remove internet access from a PC but,
> still run tracsactions over the internet using our software program which
> is
> connected the an internet accessable sql server via odbc? Our SQL sales
> program processes credit and gift cards, because of this all of the PCs
> have
> had internet access. Will OBDC still act as a "proxy" for us for internet
> access instead of having to have the PC "on the internet"?
Hi James,
I'm not an expert in this area but...
When you say you want to "remove internet access" do you mean that you want
to prevent users from using browsers or email? In order to communicate with
the offsite SQL Server you'll need "internet access" but you may be able to
block some outgoing and incoming ports, restricting the internet access to
your application.
Cindy Winegarden
cindy@.cindywinegarden.com
VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx|||I'm sorry if I was not totally clear. The SQL box is on the same physical
lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
chat, browsing, etc.)
"Cindy Winegarden" wrote:
> "JamesB" <JamesB@.discussions.microsoft.com> wrote in message
> news:70B7E91C-D5DA-46ED-82DB-5F5CB467F47B@.microsoft.com...
>
> Hi James,
> I'm not an expert in this area but...
> When you say you want to "remove internet access" do you mean that you wan
t
> to prevent users from using browsers or email? In order to communicate wit
h
> the offsite SQL Server you'll need "internet access" but you may be able t
o
> block some outgoing and incoming ports, restricting the internet access to
> your application.
> --
> Cindy Winegarden
> cindy@.cindywinegarden.com
> VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
> VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
>|||That scenario should work, if the SQL Server box is on the same internal
network, you don't need internet access to connect. You can just use TCP/IP
on the local network.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"JamesB" <JamesB@.discussions.microsoft.com> wrote in message
news:53EED798-0059-483B-85AD-08907B30E59A@.microsoft.com...[vbcol=seagreen]
> I'm sorry if I was not totally clear. The SQL box is on the same physical
> lan as the PCs. Yes, I am trying to remove internet access (i.e. E-mail,
> chat, browsing, etc.)
> "Cindy Winegarden" wrote:
>
restrict delete function on odbc connection
i have the odbc connection connected to our SQL databasse via sql user
account through MS access Frontend and having problem controling the delete
function of the account.
In SQL i am only allow the account to read, insert,update and denied delete
on the database and tables. However, the account still be able delete the
records.
Can someone let me know what am i missing in the permission granting.
Check individual permissions, groups permissions and what is important the permissions granted to the public role where every database user will be added to.
Jens K. Suessmeyer
http://www.sqlserver2005.de
Wednesday, March 28, 2012
Restrict access to certain db
I need to install EM and QA on some users machines, but I only want them to
have access to certain db's, What is best way to manage this? ALso, in QA I
want them to only be able to run Select/Update/Make Table queries. How do
I force a user to logon whe
n they open EM or QA with a certain login account?Hi,
1. Create a new login in sql server (Use enterprise manager -- Security
option)
2. Go to database and select users option and add a new user
3. Assign that user db_reader and db_writer role (this will only allow that
user to browse and update/insert/delete contents in this database)
4. Login to QA as SA user
5. Select the database in which the user need create table prev.
6. Execute "Grant CREATE TABLE to <User name>
If you need to restrict the Insert and Delete from tables - Have a look into
DENY command .
Tahnks
Hari
MCDBA
"mikeb" <anonymous@.discussions.microsoft.com> wrote in message
news:AAC88C32-6D4C-44C8-B6D8-607F078F6A7A@.microsoft.com...
> I need to install EM and QA on some users machines, but I only want them
to have access to certain db's, What is best way to manage this? ALso, in
QA I want them to only be able to run Select/Update/Make Table queries. How
do I force a user to logon when they open EM or QA with a certain login
account?
have access to certain db's, What is best way to manage this? ALso, in QA I
want them to only be able to run Select/Update/Make Table queries. How do
I force a user to logon whe
n they open EM or QA with a certain login account?Hi,
1. Create a new login in sql server (Use enterprise manager -- Security
option)
2. Go to database and select users option and add a new user
3. Assign that user db_reader and db_writer role (this will only allow that
user to browse and update/insert/delete contents in this database)
4. Login to QA as SA user
5. Select the database in which the user need create table prev.
6. Execute "Grant CREATE TABLE to <User name>
If you need to restrict the Insert and Delete from tables - Have a look into
DENY command .
Tahnks
Hari
MCDBA
"mikeb" <anonymous@.discussions.microsoft.com> wrote in message
news:AAC88C32-6D4C-44C8-B6D8-607F078F6A7A@.microsoft.com...
> I need to install EM and QA on some users machines, but I only want them
to have access to certain db's, What is best way to manage this? ALso, in
QA I want them to only be able to run Select/Update/Make Table queries. How
do I force a user to logon when they open EM or QA with a certain login
account?
restrict access to a database
Hi all,
Okay, we've got a product which has SQL Server as the backend database.
The client has the product as well as the database.
But the DBA at the clients end messes up with the data & we are held
responsible (that the product is what is messing up the data & NOT their
DBA).
How can we prevent this,
ie. try to encrypt the database or restrict access to the database to the
DBA.
Also, another requirement is that we do NOT want them to see the DB
structures, the tables, the data in the Databse!!!!
options,
1. Get them to install a new instance of SQL Server & NOT give the DBA or
anyone the password & have the password embeded in the application (thus
only the application can access the SQL Database).
NOTE : THe product and the SQL Servers WILL have to be at the clients end.
any other options?
Hi
As you don't have the means to stop physical access this is difficult. You
may want to make sure that you are auditing such events. If you want to
carry out encryption then it is probably better on the client as the DBA
will have access to the methods used at the database end. You may also want
to look throught the products and articles on
http://www.sqlsecurity.com/DesktopDe...ndex=0&tabid=1
John
"D.Rudiani" <rudi@.www.com> wrote in message
news:ervb1anWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> Okay, we've got a product which has SQL Server as the backend database.
> The client has the product as well as the database.
> But the DBA at the clients end messes up with the data & we are held
> responsible (that the product is what is messing up the data & NOT their
> DBA).
> How can we prevent this,
> ie. try to encrypt the database or restrict access to the database to the
> DBA.
> Also, another requirement is that we do NOT want them to see the DB
> structures, the tables, the data in the Databse!!!!
> options,
> 1. Get them to install a new instance of SQL Server & NOT give the DBA or
> anyone the password & have the password embeded in the application (thus
> only the application can access the SQL Database).
> NOTE : THe product and the SQL Servers WILL have to be at the clients end.
> any other options?
>
Okay, we've got a product which has SQL Server as the backend database.
The client has the product as well as the database.
But the DBA at the clients end messes up with the data & we are held
responsible (that the product is what is messing up the data & NOT their
DBA).
How can we prevent this,
ie. try to encrypt the database or restrict access to the database to the
DBA.
Also, another requirement is that we do NOT want them to see the DB
structures, the tables, the data in the Databse!!!!
options,
1. Get them to install a new instance of SQL Server & NOT give the DBA or
anyone the password & have the password embeded in the application (thus
only the application can access the SQL Database).
NOTE : THe product and the SQL Servers WILL have to be at the clients end.
any other options?
Hi
As you don't have the means to stop physical access this is difficult. You
may want to make sure that you are auditing such events. If you want to
carry out encryption then it is probably better on the client as the DBA
will have access to the methods used at the database end. You may also want
to look throught the products and articles on
http://www.sqlsecurity.com/DesktopDe...ndex=0&tabid=1
John
"D.Rudiani" <rudi@.www.com> wrote in message
news:ervb1anWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> Okay, we've got a product which has SQL Server as the backend database.
> The client has the product as well as the database.
> But the DBA at the clients end messes up with the data & we are held
> responsible (that the product is what is messing up the data & NOT their
> DBA).
> How can we prevent this,
> ie. try to encrypt the database or restrict access to the database to the
> DBA.
> Also, another requirement is that we do NOT want them to see the DB
> structures, the tables, the data in the Databse!!!!
> options,
> 1. Get them to install a new instance of SQL Server & NOT give the DBA or
> anyone the password & have the password embeded in the application (thus
> only the application can access the SQL Database).
> NOTE : THe product and the SQL Servers WILL have to be at the clients end.
> any other options?
>
restrict access to a database
Hi all,
Okay, we've got a product which has SQL Server as the backend database.
The client has the product as well as the database.
But the DBA at the clients end messes up with the data & we are held
responsible (that the product is what is messing up the data & NOT their
DBA).
How can we prevent this,
ie. try to encrypt the database or restrict access to the database to the
DBA.
Also, another requirement is that we do NOT want them to see the DB
structures, the tables, the data in the Databse!!!!
options,
1. Get them to install a new instance of SQL Server & NOT give the DBA or
anyone the password & have the password embeded in the application (thus
only the application can access the SQL Database).
NOTE : THe product and the SQL Servers WILL have to be at the clients end.
any other options?Do you intend to manage that server 100%? In other words you will be solely
and entirely responsible for monitoring, security, backups, performance,
maintenance and troubleshooting to an agreed SLA? If the answer is yes, then
of course you could in principle lock down the server and deny user access
to the database (I'm assuming that you used stored procs as your data access
tier so that you can deny object-level permissions to users).
If you can't answer yes to all of the above then the client has a legitimate
need to manage the database themselves and IMO you cannot reasonably deny
them sy
min level access and you cannot and should not attempt to secure
your system from the DBA. It seems to me that your best course of action in
that case would be to come to an agreement with the customer about the
division of responsibilities for the system and maybe implement some logging
process so that you have an audit trail of what has occurred to the data.
David Portas
SQL Server MVP
--|||wow!
how can i set object level permissions for users (including the DBA)?
& yes, we do have SPs.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Is-dnXwsyoLKoxTfRVn-vA@.giganews.com...
> Do you intend to manage that server 100%? In other words you will be
solely
> and entirely responsible for monitoring, security, backups, performance,
> maintenance and troubleshooting to an agreed SLA? If the answer is yes,
then
> of course you could in principle lock down the server and deny user access
> to the database (I'm assuming that you used stored procs as your data
access
> tier so that you can deny object-level permissions to users).
> If you can't answer yes to all of the above then the client has a
legitimate
> need to manage the database themselves and IMO you cannot reasonably deny
> them symin level access and you cannot and should not attempt to secure
> your system from the DBA. It seems to me that your best course of action
in
> that case would be to come to an agreement with the customer about the
> division of responsibilities for the system and maybe implement some
logging
> process so that you have an audit trail of what has occurred to the data.
> --
> David Portas
> SQL Server MVP
> --
>|||EXEC sp_addrolemember 'db_denydatawriter', 'user name'
EXEC sp_addrolemember 'db_denydatareader', 'user name'
GRANT EXECUTE TO user_name ON usp_proc_name
David Portas
SQL Server MVP
--
Okay, we've got a product which has SQL Server as the backend database.
The client has the product as well as the database.
But the DBA at the clients end messes up with the data & we are held
responsible (that the product is what is messing up the data & NOT their
DBA).
How can we prevent this,
ie. try to encrypt the database or restrict access to the database to the
DBA.
Also, another requirement is that we do NOT want them to see the DB
structures, the tables, the data in the Databse!!!!
options,
1. Get them to install a new instance of SQL Server & NOT give the DBA or
anyone the password & have the password embeded in the application (thus
only the application can access the SQL Database).
NOTE : THe product and the SQL Servers WILL have to be at the clients end.
any other options?Do you intend to manage that server 100%? In other words you will be solely
and entirely responsible for monitoring, security, backups, performance,
maintenance and troubleshooting to an agreed SLA? If the answer is yes, then
of course you could in principle lock down the server and deny user access
to the database (I'm assuming that you used stored procs as your data access
tier so that you can deny object-level permissions to users).
If you can't answer yes to all of the above then the client has a legitimate
need to manage the database themselves and IMO you cannot reasonably deny
them sy
min level access and you cannot and should not attempt to secureyour system from the DBA. It seems to me that your best course of action in
that case would be to come to an agreement with the customer about the
division of responsibilities for the system and maybe implement some logging
process so that you have an audit trail of what has occurred to the data.
David Portas
SQL Server MVP
--|||wow!
how can i set object level permissions for users (including the DBA)?
& yes, we do have SPs.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Is-dnXwsyoLKoxTfRVn-vA@.giganews.com...
> Do you intend to manage that server 100%? In other words you will be
solely
> and entirely responsible for monitoring, security, backups, performance,
> maintenance and troubleshooting to an agreed SLA? If the answer is yes,
then
> of course you could in principle lock down the server and deny user access
> to the database (I'm assuming that you used stored procs as your data
access
> tier so that you can deny object-level permissions to users).
> If you can't answer yes to all of the above then the client has a
legitimate
> need to manage the database themselves and IMO you cannot reasonably deny
> them sy
min level access and you cannot and should not attempt to secure> your system from the DBA. It seems to me that your best course of action
in
> that case would be to come to an agreement with the customer about the
> division of responsibilities for the system and maybe implement some
logging
> process so that you have an audit trail of what has occurred to the data.
> --
> David Portas
> SQL Server MVP
> --
>|||EXEC sp_addrolemember 'db_denydatawriter', 'user name'
EXEC sp_addrolemember 'db_denydatareader', 'user name'
GRANT EXECUTE TO user_name ON usp_proc_name
David Portas
SQL Server MVP
--
restrict access to a database
Hi all,
Okay, we've got a product which has SQL Server as the backend database.
The client has the product as well as the database.
But the DBA at the clients end messes up with the data & we are held
responsible (that the product is what is messing up the data & NOT their
DBA).
How can we prevent this,
ie. try to encrypt the database or restrict access to the database to the
DBA.
Also, another requirement is that we do NOT want them to see the DB
structures, the tables, the data in the Databse!!!!
options,
1. Get them to install a new instance of SQL Server & NOT give the DBA or
anyone the password & have the password embeded in the application (thus
only the application can access the SQL Database).
NOTE : THe product and the SQL Servers WILL have to be at the clients end.
any other options?D.Rudiani
If you want to restict access to the database then remove him/her from
sysadmin server role and don't grant the access to the database
"D.Rudiani" <rudi@.www.com> wrote in message
news:%23dJk2anWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Okay, we've got a product which has SQL Server as the backend database.
> The client has the product as well as the database.
> But the DBA at the clients end messes up with the data & we are held
> responsible (that the product is what is messing up the data & NOT their
> DBA).
> How can we prevent this,
> ie. try to encrypt the database or restrict access to the database to the
> DBA.
> Also, another requirement is that we do NOT want them to see the DB
> structures, the tables, the data in the Databse!!!!
> options,
> 1. Get them to install a new instance of SQL Server & NOT give the DBA or
> anyone the password & have the password embeded in the application (thus
> only the application can access the SQL Database).
> NOTE : THe product and the SQL Servers WILL have to be at the clients end.
> any other options?
>|||Hi,
Since the SQL SERVER machine is in the clients place and DBA needs to
perform administrative tasks ( BACKUP , RECOVERY , DBCC, REINDEX..),
it is very difficult to restrict the DBA. Only way to restrict is change the
OS Level admin password and SA password for sql server and do not hand it to
client side DBA. In this case he can not perform any administrative tasks.
Also, another requirement is that we do NOT want them to see the DB
structures, the tables, the data in the Databse!!!!
See Application roles in Books online.
Thanks
Hari
SQL Server MVP
"D.Rudiani" <rudi@.www.com> wrote in message
news:%23dJk2anWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Okay, we've got a product which has SQL Server as the backend database.
> The client has the product as well as the database.
> But the DBA at the clients end messes up with the data & we are held
> responsible (that the product is what is messing up the data & NOT their
> DBA).
> How can we prevent this,
> ie. try to encrypt the database or restrict access to the database to the
> DBA.
> Also, another requirement is that we do NOT want them to see the DB
> structures, the tables, the data in the Databse!!!!
> options,
> 1. Get them to install a new instance of SQL Server & NOT give the DBA or
> anyone the password & have the password embeded in the application (thus
> only the application can access the SQL Database).
> NOTE : THe product and the SQL Servers WILL have to be at the clients end.
> any other options?
>sql
Okay, we've got a product which has SQL Server as the backend database.
The client has the product as well as the database.
But the DBA at the clients end messes up with the data & we are held
responsible (that the product is what is messing up the data & NOT their
DBA).
How can we prevent this,
ie. try to encrypt the database or restrict access to the database to the
DBA.
Also, another requirement is that we do NOT want them to see the DB
structures, the tables, the data in the Databse!!!!
options,
1. Get them to install a new instance of SQL Server & NOT give the DBA or
anyone the password & have the password embeded in the application (thus
only the application can access the SQL Database).
NOTE : THe product and the SQL Servers WILL have to be at the clients end.
any other options?D.Rudiani
If you want to restict access to the database then remove him/her from
sysadmin server role and don't grant the access to the database
"D.Rudiani" <rudi@.www.com> wrote in message
news:%23dJk2anWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Okay, we've got a product which has SQL Server as the backend database.
> The client has the product as well as the database.
> But the DBA at the clients end messes up with the data & we are held
> responsible (that the product is what is messing up the data & NOT their
> DBA).
> How can we prevent this,
> ie. try to encrypt the database or restrict access to the database to the
> DBA.
> Also, another requirement is that we do NOT want them to see the DB
> structures, the tables, the data in the Databse!!!!
> options,
> 1. Get them to install a new instance of SQL Server & NOT give the DBA or
> anyone the password & have the password embeded in the application (thus
> only the application can access the SQL Database).
> NOTE : THe product and the SQL Servers WILL have to be at the clients end.
> any other options?
>|||Hi,
Since the SQL SERVER machine is in the clients place and DBA needs to
perform administrative tasks ( BACKUP , RECOVERY , DBCC, REINDEX..),
it is very difficult to restrict the DBA. Only way to restrict is change the
OS Level admin password and SA password for sql server and do not hand it to
client side DBA. In this case he can not perform any administrative tasks.
Also, another requirement is that we do NOT want them to see the DB
structures, the tables, the data in the Databse!!!!
See Application roles in Books online.
Thanks
Hari
SQL Server MVP
"D.Rudiani" <rudi@.www.com> wrote in message
news:%23dJk2anWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Okay, we've got a product which has SQL Server as the backend database.
> The client has the product as well as the database.
> But the DBA at the clients end messes up with the data & we are held
> responsible (that the product is what is messing up the data & NOT their
> DBA).
> How can we prevent this,
> ie. try to encrypt the database or restrict access to the database to the
> DBA.
> Also, another requirement is that we do NOT want them to see the DB
> structures, the tables, the data in the Databse!!!!
> options,
> 1. Get them to install a new instance of SQL Server & NOT give the DBA or
> anyone the password & have the password embeded in the application (thus
> only the application can access the SQL Database).
> NOTE : THe product and the SQL Servers WILL have to be at the clients end.
> any other options?
>sql
Restrict Access Suddenly Active
I got called in on Monday because people couldn't get into
our SQL Server application. It's SQL Server 2000. After
talking to someone (I'm new to the application) I found
that the 'Single user' option for the database in
Enterprise Manager had been checked. Now I can't think
that anyone would have gone up to the server between
Friday and Monday (I was in at the weekend too, and didn't
touch the server). So I'm wondering how it might have
suddenly become checked and ruin my day off... :-)
TimA maintenance plan with the "repair minor problems" option set will do this.
"Tim Gowen" <tim.gowen@.rafmuseum.org> wrote in message
news:152b01c46f2c$30f715c0$a301280a@.phx.gbl...
> I got called in on Monday because people couldn't get into
> our SQL Server application. It's SQL Server 2000. After
> talking to someone (I'm new to the application) I found
> that the 'Single user' option for the database in
> Enterprise Manager had been checked. Now I can't think
> that anyone would have gone up to the server between
> Friday and Monday (I was in at the weekend too, and didn't
> touch the server). So I'm wondering how it might have
> suddenly become checked and ruin my day off... :-)
> Tim
our SQL Server application. It's SQL Server 2000. After
talking to someone (I'm new to the application) I found
that the 'Single user' option for the database in
Enterprise Manager had been checked. Now I can't think
that anyone would have gone up to the server between
Friday and Monday (I was in at the weekend too, and didn't
touch the server). So I'm wondering how it might have
suddenly become checked and ruin my day off... :-)
TimA maintenance plan with the "repair minor problems" option set will do this.
"Tim Gowen" <tim.gowen@.rafmuseum.org> wrote in message
news:152b01c46f2c$30f715c0$a301280a@.phx.gbl...
> I got called in on Monday because people couldn't get into
> our SQL Server application. It's SQL Server 2000. After
> talking to someone (I'm new to the application) I found
> that the 'Single user' option for the database in
> Enterprise Manager had been checked. Now I can't think
> that anyone would have gone up to the server between
> Friday and Monday (I was in at the weekend too, and didn't
> touch the server). So I'm wondering how it might have
> suddenly become checked and ruin my day off... :-)
> Tim
Restrict Access Suddenly Active
I got called in on Monday because people couldn't get into
our SQL Server application. It's SQL Server 2000. After
talking to someone (I'm new to the application) I found
that the 'Single user' option for the database in
Enterprise Manager had been checked. Now I can't think
that anyone would have gone up to the server between
Friday and Monday (I was in at the weekend too, and didn't
touch the server). So I'm wondering how it might have
suddenly become checked and ruin my day off... :-)
Tim
A maintenance plan with the "repair minor problems" option set will do this.
"Tim Gowen" <tim.gowen@.rafmuseum.org> wrote in message
news:152b01c46f2c$30f715c0$a301280a@.phx.gbl...
> I got called in on Monday because people couldn't get into
> our SQL Server application. It's SQL Server 2000. After
> talking to someone (I'm new to the application) I found
> that the 'Single user' option for the database in
> Enterprise Manager had been checked. Now I can't think
> that anyone would have gone up to the server between
> Friday and Monday (I was in at the weekend too, and didn't
> touch the server). So I'm wondering how it might have
> suddenly become checked and ruin my day off... :-)
> Tim
our SQL Server application. It's SQL Server 2000. After
talking to someone (I'm new to the application) I found
that the 'Single user' option for the database in
Enterprise Manager had been checked. Now I can't think
that anyone would have gone up to the server between
Friday and Monday (I was in at the weekend too, and didn't
touch the server). So I'm wondering how it might have
suddenly become checked and ruin my day off... :-)
Tim
A maintenance plan with the "repair minor problems" option set will do this.
"Tim Gowen" <tim.gowen@.rafmuseum.org> wrote in message
news:152b01c46f2c$30f715c0$a301280a@.phx.gbl...
> I got called in on Monday because people couldn't get into
> our SQL Server application. It's SQL Server 2000. After
> talking to someone (I'm new to the application) I found
> that the 'Single user' option for the database in
> Enterprise Manager had been checked. Now I can't think
> that anyone would have gone up to the server between
> Friday and Monday (I was in at the weekend too, and didn't
> touch the server). So I'm wondering how it might have
> suddenly become checked and ruin my day off... :-)
> Tim
Restrict access during a few hours
While we update our datawarehouse we don't want the users
to be able to connect to the database and update
their reports.
So we want to block access during certain hours.
What is the best way fix this?
The users use a SqlServer login with permission to read
data.Hi
It easiest would be to set it to dbo use only (assuming your users do not
have dbo rights)
ALTER DATABASE northwind
SET RESTRICTED_USER
Regards
Mike
"Leif" wrote:
> While we update our datawarehouse we don't want the users
> to be able to connect to the database and update
> their reports.
> So we want to block access during certain hours.
> What is the best way fix this?
> The users use a SqlServer login with permission to read
> data.
>
to be able to connect to the database and update
their reports.
So we want to block access during certain hours.
What is the best way fix this?
The users use a SqlServer login with permission to read
data.Hi
It easiest would be to set it to dbo use only (assuming your users do not
have dbo rights)
ALTER DATABASE northwind
SET RESTRICTED_USER
Regards
Mike
"Leif" wrote:
> While we update our datawarehouse we don't want the users
> to be able to connect to the database and update
> their reports.
> So we want to block access during certain hours.
> What is the best way fix this?
> The users use a SqlServer login with permission to read
> data.
>
Restrict access during a few hours
While we update our datawarehouse we don't want the users
to be able to connect to the database and update
their reports.
So we want to block access during certain hours.
What is the best way fix this?
The users use a SqlServer login with permission to read
data.
Hi
It easiest would be to set it to dbo use only (assuming your users do not
have dbo rights)
ALTER DATABASE northwind
SET RESTRICTED_USER
Regards
Mike
"Leif" wrote:
> While we update our datawarehouse we don't want the users
> to be able to connect to the database and update
> their reports.
> So we want to block access during certain hours.
> What is the best way fix this?
> The users use a SqlServer login with permission to read
> data.
>
to be able to connect to the database and update
their reports.
So we want to block access during certain hours.
What is the best way fix this?
The users use a SqlServer login with permission to read
data.
Hi
It easiest would be to set it to dbo use only (assuming your users do not
have dbo rights)
ALTER DATABASE northwind
SET RESTRICTED_USER
Regards
Mike
"Leif" wrote:
> While we update our datawarehouse we don't want the users
> to be able to connect to the database and update
> their reports.
> So we want to block access during certain hours.
> What is the best way fix this?
> The users use a SqlServer login with permission to read
> data.
>
restriced access
HI,
how can i restrict the access for my sqlserver DB.
I can connect with any Connection STring.
What do i have to change so that u can only access the db with the correct username and password from an app?
thats my connString.
"Data Source=TURM21;Database=Db1;User id= kr; password=hahn; Trusted_connection= true;pooling=false;"
Is it a Property Setting in Sql Server? Where is this Setting?
GreetzHi
You have two security settings in SQL Server:
Windows Authenication Only and mixed (Windows and SQL Server). Make sure you are set to use both.
Your connection string includes:
Trusted_connection= true
This means that your NT log is used to access SQL Server. The username and password you supply are ignored. It is your NT account that is giving you access to the server. Remove this from your connection string and the SQL Server username and password you supply will be used to authenticate you.
BTW - MS recommend you access SQL Server using your NT credentials.
HTH|||ok i switched to mixed authentification.
my connection string is:
Data Source=TURM21;Database=Db1;User Id=kroete;password=hahn;pooling=false;
the user was createdlike this:
CREATE LOGIN kroete
WITH PASSWORD = 'hahn';
use Db1;
create User kroete;
GO
But now i get an SQLException: error during login;
I don't know what the problem is?
Cheers|||Apart from your userID changing from the first post? ;)
Double check your password (not what I'd describe as strong BTW). Try logging via QA with those credentials. If you succeed then it is probably the client code. If you fail then your username\ password are wong.
HTH|||no the user id is correct:)
Ok i tried to connect directly (Sql Server Management Stduio) wit Sql Server Authentification and i can not connect.
Is the way, I created the user correct?
CREATE LOGIN kroete
WITH PASSWORD = 'hahn';
use Db1;
create User kroete;
GO|||ok - what's the exception description then? :)|||You forgot FROM clause in your CREATE USER statement.|||From clause? Why? And how?
Will post the exception later. Don#T have it here.
how can i restrict the access for my sqlserver DB.
I can connect with any Connection STring.
What do i have to change so that u can only access the db with the correct username and password from an app?
thats my connString.
"Data Source=TURM21;Database=Db1;User id= kr; password=hahn; Trusted_connection= true;pooling=false;"
Is it a Property Setting in Sql Server? Where is this Setting?
GreetzHi
You have two security settings in SQL Server:
Windows Authenication Only and mixed (Windows and SQL Server). Make sure you are set to use both.
Your connection string includes:
Trusted_connection= true
This means that your NT log is used to access SQL Server. The username and password you supply are ignored. It is your NT account that is giving you access to the server. Remove this from your connection string and the SQL Server username and password you supply will be used to authenticate you.
BTW - MS recommend you access SQL Server using your NT credentials.
HTH|||ok i switched to mixed authentification.
my connection string is:
Data Source=TURM21;Database=Db1;User Id=kroete;password=hahn;pooling=false;
the user was createdlike this:
CREATE LOGIN kroete
WITH PASSWORD = 'hahn';
use Db1;
create User kroete;
GO
But now i get an SQLException: error during login;
I don't know what the problem is?
Cheers|||Apart from your userID changing from the first post? ;)
Double check your password (not what I'd describe as strong BTW). Try logging via QA with those credentials. If you succeed then it is probably the client code. If you fail then your username\ password are wong.
HTH|||no the user id is correct:)
Ok i tried to connect directly (Sql Server Management Stduio) wit Sql Server Authentification and i can not connect.
Is the way, I created the user correct?
CREATE LOGIN kroete
WITH PASSWORD = 'hahn';
use Db1;
create User kroete;
GO|||ok - what's the exception description then? :)|||You forgot FROM clause in your CREATE USER statement.|||From clause? Why? And how?
Will post the exception later. Don#T have it here.
Monday, March 26, 2012
Restoring the database using stored procedure
hey guyz...
i used this stored procedure code my system.. but it crashes saying "exclusive access could not be obtained becuase the database is in use"
i have included the stored procedure below. is the stored procedure correct?
if it is.. how can i sovle this problem?
CREATE Procedure spRestoreDatabase
@.Path VARCHAR(100)
AS
Restore Database Test From Disk = @.Path
GOA database restore can only be done when you've got exclusive use of the database. No other users can be using the database besides the one doing the restore.
The procedure can't be in the database, because then the spid trying to restore the datbase would need to be executing code in the database which would prevent the restore.
An SQL Agent job would be a better choice than a stored procedure. You may need to think about using ALTER DATABASE to force the other users out of the database, but think that through carefully before you try to use it because it can cause other problems.
-PatP|||erm... so what can i do now?|||Put your stored procedure in a different database and run it from there.
Gotta ask why you need a stored proc to restore your database in the first place. Surely this isn't going to be an automated task?|||erm... so what can i do now?As blindman pointed out, your first step ought to be to rethink what you are doing. Try to determine if there is some better way than an automated restore.
If you really, truly need to automate the restore, I'd do it as a SQL Agent Job myself. You can actually get pretty tricky with jobs, and do nearly anything you can do with a stored procedure, plus a whole lot more.
Before you get too busy coding, think hard about what you are doing, why you are doing it, and whether there are any alternatives. You are headed into the area that cartographers of olde used to label "Here be dragons"
-PatPsql
i used this stored procedure code my system.. but it crashes saying "exclusive access could not be obtained becuase the database is in use"
i have included the stored procedure below. is the stored procedure correct?
if it is.. how can i sovle this problem?
CREATE Procedure spRestoreDatabase
@.Path VARCHAR(100)
AS
Restore Database Test From Disk = @.Path
GOA database restore can only be done when you've got exclusive use of the database. No other users can be using the database besides the one doing the restore.
The procedure can't be in the database, because then the spid trying to restore the datbase would need to be executing code in the database which would prevent the restore.
An SQL Agent job would be a better choice than a stored procedure. You may need to think about using ALTER DATABASE to force the other users out of the database, but think that through carefully before you try to use it because it can cause other problems.
-PatP|||erm... so what can i do now?|||Put your stored procedure in a different database and run it from there.
Gotta ask why you need a stored proc to restore your database in the first place. Surely this isn't going to be an automated task?|||erm... so what can i do now?As blindman pointed out, your first step ought to be to rethink what you are doing. Try to determine if there is some better way than an automated restore.
If you really, truly need to automate the restore, I'd do it as a SQL Agent Job myself. You can actually get pretty tricky with jobs, and do nearly anything you can do with a stored procedure, plus a whole lot more.
Before you get too busy coding, think hard about what you are doing, why you are doing it, and whether there are any alternatives. You are headed into the area that cartographers of olde used to label "Here be dragons"
-PatPsql
Subscribe to:
Posts (Atom)