I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, and
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, as
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also something
labor intensive, am looking for an easier way.
Thank you in advance.
Sam
Think I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table, and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner, as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs per
> table, this is too time consuming. I could script, but this is also something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam
|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Thursday, March 29, 2012
Granting permissions
I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, an
d
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, a
s
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also somethin
g
labor intensive, am looking for an easier way.
Thank you in advance.
SamThink I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs p
er
> table, this is too time consuming. I could script, but this is also someth
ing
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, an
d
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, a
s
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also somethin
g
labor intensive, am looking for an easier way.
Thank you in advance.
SamThink I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs p
er
> table, this is too time consuming. I could script, but this is also someth
ing
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam
Granting permissions
I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, and
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, as
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also something
labor intensive, am looking for an easier way.
Thank you in advance.
SamThink I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table, and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner, as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs per
> table, this is too time consuming. I could script, but this is also something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, and
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, as
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also something
labor intensive, am looking for an easier way.
Thank you in advance.
SamThink I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table, and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner, as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs per
> table, this is too time consuming. I could script, but this is also something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam
Wednesday, March 7, 2012
Going From SQL Enterprise to SQL Standard
We mistakenly installed SQL Enterprise on a production server, and it should
've been SQL Standard. How can we go from Enterprise to Standard? Will we
have to move the database to a different SQL server, then uninstall SQL Ente
rprise and install SQL Stan
dard? Or, is there an easier way?Hi,
I feel that we can not come down from Enterprise to Standard, but the other
way is possible.
I think you may need to Reinstall standard edition and copy back the
databases. Please wait for 2 more days till all our experts confirm the
possibilities.
Incase if you need to reinstall then,
As a precausion take a backup of all databases
do
1. Stop current sql server service
2. Copy all mdf and ldf to a safe folder
3. Uninstall SQL server EE
4. nstall SQL server Standard and SP3a with same folder structure as old
5. Copy back the mdf and ldf files to same folders as old
6. start sql server.
Mostly the sql server will be up will all the databases, if not use the
backup files we backup to restore each databases.
Thanks
Hari
MCDBA
Thanks
Hri
MCDBA
"Mickey" <mmorris@.tacenergy.com> wrote in message
news:FFF4D5BD-1569-4FEB-988C-D2D9820E78AD@.microsoft.com...
should've been SQL Standard. How can we go from Enterprise to Standard?
Will we have to move the database to a different SQL server, then uninstall
SQL Enterprise and install SQL Standard? Or, is there an easier way?|||Thanks Hari...I'll wait a couple more days and see. I appreciate the info.
've been SQL Standard. How can we go from Enterprise to Standard? Will we
have to move the database to a different SQL server, then uninstall SQL Ente
rprise and install SQL Stan
dard? Or, is there an easier way?Hi,
I feel that we can not come down from Enterprise to Standard, but the other
way is possible.
I think you may need to Reinstall standard edition and copy back the
databases. Please wait for 2 more days till all our experts confirm the
possibilities.
Incase if you need to reinstall then,
As a precausion take a backup of all databases
do
1. Stop current sql server service
2. Copy all mdf and ldf to a safe folder
3. Uninstall SQL server EE
4. nstall SQL server Standard and SP3a with same folder structure as old
5. Copy back the mdf and ldf files to same folders as old
6. start sql server.
Mostly the sql server will be up will all the databases, if not use the
backup files we backup to restore each databases.
Thanks
Hari
MCDBA
Thanks
Hri
MCDBA
"Mickey" <mmorris@.tacenergy.com> wrote in message
news:FFF4D5BD-1569-4FEB-988C-D2D9820E78AD@.microsoft.com...
quote:
> We mistakenly installed SQL Enterprise on a production server, and it
should've been SQL Standard. How can we go from Enterprise to Standard?
Will we have to move the database to a different SQL server, then uninstall
SQL Enterprise and install SQL Standard? Or, is there an easier way?|||Thanks Hari...I'll wait a couple more days and see. I appreciate the info.
Going From SQL Enterprise to SQL Standard
We mistakenly installed SQL Enterprise on a production server, and it should've been SQL Standard. How can we go from Enterprise to Standard? Will we have to move the database to a different SQL server, then uninstall SQL Enterprise and install SQL Standard? Or, is there an easier way?Hi,
I feel that we can not come down from Enterprise to Standard, but the other
way is possible.
I think you may need to Reinstall standard edition and copy back the
databases. Please wait for 2 more days till all our experts confirm the
possibilities.
Incase if you need to reinstall then,
As a precausion take a backup of all databases
do
1. Stop current sql server service
2. Copy all mdf and ldf to a safe folder
3. Uninstall SQL server EE
4. nstall SQL server Standard and SP3a with same folder structure as old
5. Copy back the mdf and ldf files to same folders as old
6. start sql server.
Mostly the sql server will be up will all the databases, if not use the
backup files we backup to restore each databases.
Thanks
Hari
MCDBA
Thanks
Hri
MCDBA
"Mickey" <mmorris@.tacenergy.com> wrote in message
news:FFF4D5BD-1569-4FEB-988C-D2D9820E78AD@.microsoft.com...
> We mistakenly installed SQL Enterprise on a production server, and it
should've been SQL Standard. How can we go from Enterprise to Standard?
Will we have to move the database to a different SQL server, then uninstall
SQL Enterprise and install SQL Standard? Or, is there an easier way?
I feel that we can not come down from Enterprise to Standard, but the other
way is possible.
I think you may need to Reinstall standard edition and copy back the
databases. Please wait for 2 more days till all our experts confirm the
possibilities.
Incase if you need to reinstall then,
As a precausion take a backup of all databases
do
1. Stop current sql server service
2. Copy all mdf and ldf to a safe folder
3. Uninstall SQL server EE
4. nstall SQL server Standard and SP3a with same folder structure as old
5. Copy back the mdf and ldf files to same folders as old
6. start sql server.
Mostly the sql server will be up will all the databases, if not use the
backup files we backup to restore each databases.
Thanks
Hari
MCDBA
Thanks
Hri
MCDBA
"Mickey" <mmorris@.tacenergy.com> wrote in message
news:FFF4D5BD-1569-4FEB-988C-D2D9820E78AD@.microsoft.com...
> We mistakenly installed SQL Enterprise on a production server, and it
should've been SQL Standard. How can we go from Enterprise to Standard?
Will we have to move the database to a different SQL server, then uninstall
SQL Enterprise and install SQL Standard? Or, is there an easier way?
Sunday, February 19, 2012
Giving access to outside web site to SQL Server stored procedure
Hello, I have a sql server 2000 production database that will need to have
multiple web sites insert contact information into a table. I plan to
create a new sql user and only give it permission to execute one stored
procedure (that will do the insert row into the contact table).
Then I will provide to any outside vendor the SQL Server name, database,
user id, password and stored procedure(and input parameters) name. What are
the security concerns with this?
Would it make any sense to create a database with only this table in it, or
is this just overkill. I see that when I assign a new user to just one
database and one stored procedure that they can see the master database. Is
this an issue? Should I give the user
db_denydatareader on the master database?
Thanks
DonDon (dons100@.ameritech.net) writes:
> Hello, I have a sql server 2000 production database that will need to have
> multiple web sites insert contact information into a table. I plan to
> create a new sql user and only give it permission to execute one stored
> procedure (that will do the insert row into the contact table).
> Then I will provide to any outside vendor the SQL Server name, database,
> user id, password and stored procedure(and input parameters) name. What
> are the security concerns with this?
> Would it make any sense to create a database with only this table in it,
> or is this just overkill. I see that when I assign a new user to just
> one database and one stored procedure that they can see the master
> database. Is this an issue? Should I give the user db_denydatareader
> on the master database?
I would rather not expose an SQL Server on the Internet at all, least of
all SQL 2000. SQL 2000 no protection against brute-force attacks whatsoever.
Even if you have a really strong password for sa, someone might be able to
crack it. The server would also be open for denial-of-service attacks.
I think you should better implement some sort of web service. Not
necessarily with XML. But something that listens on a TCP/IP port, possibly
authenticates the user, and then connects to SQL Server with this low-
priv user you described. Username and password would be hard-coded into
this service, or read from a file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
multiple web sites insert contact information into a table. I plan to
create a new sql user and only give it permission to execute one stored
procedure (that will do the insert row into the contact table).
Then I will provide to any outside vendor the SQL Server name, database,
user id, password and stored procedure(and input parameters) name. What are
the security concerns with this?
Would it make any sense to create a database with only this table in it, or
is this just overkill. I see that when I assign a new user to just one
database and one stored procedure that they can see the master database. Is
this an issue? Should I give the user
db_denydatareader on the master database?
Thanks
DonDon (dons100@.ameritech.net) writes:
> Hello, I have a sql server 2000 production database that will need to have
> multiple web sites insert contact information into a table. I plan to
> create a new sql user and only give it permission to execute one stored
> procedure (that will do the insert row into the contact table).
> Then I will provide to any outside vendor the SQL Server name, database,
> user id, password and stored procedure(and input parameters) name. What
> are the security concerns with this?
> Would it make any sense to create a database with only this table in it,
> or is this just overkill. I see that when I assign a new user to just
> one database and one stored procedure that they can see the master
> database. Is this an issue? Should I give the user db_denydatareader
> on the master database?
I would rather not expose an SQL Server on the Internet at all, least of
all SQL 2000. SQL 2000 no protection against brute-force attacks whatsoever.
Even if you have a really strong password for sa, someone might be able to
crack it. The server would also be open for denial-of-service attacks.
I think you should better implement some sort of web service. Not
necessarily with XML. But something that listens on a TCP/IP port, possibly
authenticates the user, and then connects to SQL Server with this low-
priv user you described. Username and password would be hard-coded into
this service, or read from a file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)