Hi,
Can anyone tell me when to use oleDB, ODBC, SqlConnection and Database
explorer to connect a sql server (from VB.net) where users have windows
security.
ThxHDI
Visit http://www.carlprothman.net/Default.aspx?tabid=81
"HDI" <hdinf@.hotmail.com> wrote in message
news:1168252383.802235.66360@.i15g2000cwa.googlegroups.com...
> Hi,
> Can anyone tell me when to use oleDB, ODBC, SqlConnection and Database
> explorer to connect a sql server (from VB.net) where users have windows
> security.
>
> Thx
>|||Thanx , but this paper explains how to use them not when/ why using
them.
Uri Dimant schreef:
> HDI
> Visit http://www.carlprothman.net/Default.aspx?tabid=81
>
>
> "HDI" <hdinf@.hotmail.com> wrote in message
> news:1168252383.802235.66360@.i15g2000cwa.googlegroups.com...
> > Hi,
> >
> > Can anyone tell me when to use oleDB, ODBC, SqlConnection and Database
> > explorer to connect a sql server (from VB.net) where users have windows
> > security.
> >
> >
> > Thx
> >|||HDI
I thought it was pretty clear if you use VB.NET
"HDI" <hdinf@.hotmail.com> wrote in message
news:1168253291.539504.44540@.38g2000cwa.googlegroups.com...
> Thanx , but this paper explains how to use them not when/ why using
> them.
>
> Uri Dimant schreef:
>> HDI
>> Visit http://www.carlprothman.net/Default.aspx?tabid=81
>>
>>
>> "HDI" <hdinf@.hotmail.com> wrote in message
>> news:1168252383.802235.66360@.i15g2000cwa.googlegroups.com...
>> > Hi,
>> >
>> > Can anyone tell me when to use oleDB, ODBC, SqlConnection and Database
>> > explorer to connect a sql server (from VB.net) where users have windows
>> > security.
>> >
>> >
>> > Thx
>> >
>|||Go for SqlConnection because oleDB and ODBC involve more layers between
the app and SQL Server. See
http://msdn2.microsoft.com/en-us/library/a6cd7c08.aspx for further info.
Showing posts with label net. Show all posts
Showing posts with label net. Show all posts
Thursday, March 29, 2012
Database Configuration Problems
I am trying to learn ASP.NET using Visual Basic Standard Edition 2003 and an MSDE database. I am having problems configuring the login. Is this a workable configuration? Are there instructions somewhere for setting up this configuration? I have no problem using Access 2003, but I would like to use MSDE.Hold everything!!! If you're just starting out, then why not learn ASP.NET 2.0 instead? Go to the Home page of this site and follow the 3 steps of "Getting Started with ASP.NET." You get a great free developement tool (Visual Web Developer Express) and download SQL Express 2005 (which replaces MSDE) too (not sure if this is part of the download... if not, google for download... it's free too). That way, you're up-to-date with the latest technology.
Database concurrent access issue
Dear All,
I have this .NET application that inserts lucky draw entries into SQL server. Each entry may have a range of values that the winning number will be drawn from. For example,
Entry 1: [1, 2]
Entry 2: [3, 8]
Entry 3: [9, 10]
Entry 4: [11, 11]
Entry 5: [12, 20]
The winning number will be picked from [1, 20]. Given this scenario, what is the best design that can handle the concurrency issue? If two entries are input at the same time, how to make sure it won't have the same starting value? Anyway to lock a table when one is accessing, disallowing other to run select query?
Thanks.You'll have to explain that better.|||For example, Entry 3: [9, 10] means Entry 3 has the number range starting from 9 and ending at 10.
The winning number will be drawn from the number range.
You'll have to explain that better.|||Clear as mud.
If you want to ensure two entries do not share the same starting value, then make the starting value the primary key or a unique index.|||blindman: qxz wants to validate new entries to ensure that it does not conflict with any existing entry and wants to enforce this validation in case of multiple users updating the Db same time...
qxz: a table lock is needed to ensure proper insert. remember explicit locks can have negative effect on performance
create procedure CheckIt (@.StVal int, @.EndVal int)
as
BEGIN TRANSACTION
declare @.CheckFailed char(1)
set @.CheckFailed = 'N'
if exists (select * from MyTable with (tablockx) where @.StVal between StartVal and EndVal)
set @.CheckFailed = 'Y'
if exists (select * from MyTable where @.EndVal between StartVal and EndVal)
set @.CheckFailed = 'Y'
if @.CheckFailed = 'Y'
begin
ROLLBACK TRANSACTION
return 0
end
else
begin
insert into MyTable .....
COMMIT TRANSACTION
return 1
end
I have this .NET application that inserts lucky draw entries into SQL server. Each entry may have a range of values that the winning number will be drawn from. For example,
Entry 1: [1, 2]
Entry 2: [3, 8]
Entry 3: [9, 10]
Entry 4: [11, 11]
Entry 5: [12, 20]
The winning number will be picked from [1, 20]. Given this scenario, what is the best design that can handle the concurrency issue? If two entries are input at the same time, how to make sure it won't have the same starting value? Anyway to lock a table when one is accessing, disallowing other to run select query?
Thanks.You'll have to explain that better.|||For example, Entry 3: [9, 10] means Entry 3 has the number range starting from 9 and ending at 10.
The winning number will be drawn from the number range.
You'll have to explain that better.|||Clear as mud.
If you want to ensure two entries do not share the same starting value, then make the starting value the primary key or a unique index.|||blindman: qxz wants to validate new entries to ensure that it does not conflict with any existing entry and wants to enforce this validation in case of multiple users updating the Db same time...
qxz: a table lock is needed to ensure proper insert. remember explicit locks can have negative effect on performance
create procedure CheckIt (@.StVal int, @.EndVal int)
as
BEGIN TRANSACTION
declare @.CheckFailed char(1)
set @.CheckFailed = 'N'
if exists (select * from MyTable with (tablockx) where @.StVal between StartVal and EndVal)
set @.CheckFailed = 'Y'
if exists (select * from MyTable where @.EndVal between StartVal and EndVal)
set @.CheckFailed = 'Y'
if @.CheckFailed = 'Y'
begin
ROLLBACK TRANSACTION
return 0
end
else
begin
insert into MyTable .....
COMMIT TRANSACTION
return 1
end
Database concurrency
we are developing an ASP.NET application with SqlServer at backend..
there are an supplier and about 3000 customer, and each customer has about
3-4 users. they are selling mobile phone counters.
while a sale occurs, we are selecting customers stock amount, if it is
bigger then sale amount, we are updating its stock, and we increment
suppliers stock. The query is like that :
DECLARE @.StockAmount int
SELECT @.StockAmount = Amont FROM Stocks WHERE CustomerId = @.BuyerCustomerId
IF @.StockAmount > @.SaleAmount
UPDATE Stocks SET amount = @.StockAmount - @.SaleAmount WHERE CustomerId =
@.BuyerCustomerId
SELECT @.SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId =
@.SellerCustomerId
UPDATE Stocks SET amount = @.StockAmount + @.SaleAmount WHERE CustomerId =
@.SellerCustomerId
i know, each customer has got not so many users and a concurrency problem
seems to be not a big possibility. but there is just a 1 supplier record and
i think conlicts are possible. how can i alter this problem. after a
research, i found
SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK) WHERE
CustomerId = @.SellerCustomerId
seems to work fine for us, but it will block the row untill the transaction
finishes. any approaches are appreciated. thanks.Try
SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (UPDLOCK) WHERE
CustomerId = @.SellerCustomerId
Some amount of blocks is unavoidable.
This lock hint won't block others for reading. It will assure that the data
has not changed since you last read it
"The Crow" <q> wrote in message
news:ODUI3bVVFHA.2984@.tk2msftngp13.phx.gbl...
> we are developing an ASP.NET application with SqlServer at backend..
> there are an supplier and about 3000 customer, and each customer has about
> 3-4 users. they are selling mobile phone counters.
> while a sale occurs, we are selecting customers stock amount, if it is
> bigger then sale amount, we are updating its stock, and we increment
> suppliers stock. The query is like that :
> DECLARE @.StockAmount int
> SELECT @.StockAmount = Amont FROM Stocks WHERE CustomerId =
@.BuyerCustomerId
> IF @.StockAmount > @.SaleAmount
> UPDATE Stocks SET amount = @.StockAmount - @.SaleAmount WHERE CustomerId =
> @.BuyerCustomerId
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId =
> @.SellerCustomerId
> UPDATE Stocks SET amount = @.StockAmount + @.SaleAmount WHERE CustomerId =
> @.SellerCustomerId
> i know, each customer has got not so many users and a concurrency problem
> seems to be not a big possibility. but there is just a 1 supplier record
and
> i think conlicts are possible. how can i alter this problem. after a
> research, i found
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK)
WHERE
> CustomerId = @.SellerCustomerId
> seems to work fine for us, but it will block the row untill the
transaction
> finishes. any approaches are appreciated. thanks.
>|||You can use locking hint "UPDLOCK" or you can modify the statement and use
something like:
UPDATE Stocks
SET amount = amount - @.SaleAmount
WHERE CustomerId = @.BuyerCustomerId and amount > @.SaleAmount
...
AMB
"The Crow" wrote:
> we are developing an ASP.NET application with SqlServer at backend..
> there are an supplier and about 3000 customer, and each customer has about
> 3-4 users. they are selling mobile phone counters.
> while a sale occurs, we are selecting customers stock amount, if it is
> bigger then sale amount, we are updating its stock, and we increment
> suppliers stock. The query is like that :
> DECLARE @.StockAmount int
> SELECT @.StockAmount = Amont FROM Stocks WHERE CustomerId = @.BuyerCustomerI
d
> IF @.StockAmount > @.SaleAmount
> UPDATE Stocks SET amount = @.StockAmount - @.SaleAmount WHERE CustomerId =
> @.BuyerCustomerId
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId =
> @.SellerCustomerId
> UPDATE Stocks SET amount = @.StockAmount + @.SaleAmount WHERE CustomerId =
> @.SellerCustomerId
> i know, each customer has got not so many users and a concurrency problem
> seems to be not a big possibility. but there is just a 1 supplier record a
nd
> i think conlicts are possible. how can i alter this problem. after a
> research, i found
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK) WHE
RE
> CustomerId = @.SellerCustomerId
> seems to work fine for us, but it will block the row untill the transactio
n
> finishes. any approaches are appreciated. thanks.
>
>|||This is the SQL Server Books Online explanations :
UPDATE LOCK :
Used on resources that can be updated. Prevents a common form of deadlock
that occurs when multiple sessions are reading, locking, and potentially
updating resources later.
UPDLOCK :
Takes update locks instead of shared locks. Cannot be used with NOLOCK or
XLOCK.
so, shared lock as u may know doesnt prevent reading data but modyfying,
which is not the case.|||UPDATE Stocks
SET amount = amount - @.SaleAmount
WHERE CustomerId = @.BuyerCustomerId and amount > @.SaleAmount
this statement "SELECT"s the suitable row aquiring update lock which behaves
same as shared lock, and then converts it exclusive lock prior to doing
actual update. isnt it?
there are an supplier and about 3000 customer, and each customer has about
3-4 users. they are selling mobile phone counters.
while a sale occurs, we are selecting customers stock amount, if it is
bigger then sale amount, we are updating its stock, and we increment
suppliers stock. The query is like that :
DECLARE @.StockAmount int
SELECT @.StockAmount = Amont FROM Stocks WHERE CustomerId = @.BuyerCustomerId
IF @.StockAmount > @.SaleAmount
UPDATE Stocks SET amount = @.StockAmount - @.SaleAmount WHERE CustomerId =
@.BuyerCustomerId
SELECT @.SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId =
@.SellerCustomerId
UPDATE Stocks SET amount = @.StockAmount + @.SaleAmount WHERE CustomerId =
@.SellerCustomerId
i know, each customer has got not so many users and a concurrency problem
seems to be not a big possibility. but there is just a 1 supplier record and
i think conlicts are possible. how can i alter this problem. after a
research, i found
SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK) WHERE
CustomerId = @.SellerCustomerId
seems to work fine for us, but it will block the row untill the transaction
finishes. any approaches are appreciated. thanks.Try
SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (UPDLOCK) WHERE
CustomerId = @.SellerCustomerId
Some amount of blocks is unavoidable.
This lock hint won't block others for reading. It will assure that the data
has not changed since you last read it
"The Crow" <q> wrote in message
news:ODUI3bVVFHA.2984@.tk2msftngp13.phx.gbl...
> we are developing an ASP.NET application with SqlServer at backend..
> there are an supplier and about 3000 customer, and each customer has about
> 3-4 users. they are selling mobile phone counters.
> while a sale occurs, we are selecting customers stock amount, if it is
> bigger then sale amount, we are updating its stock, and we increment
> suppliers stock. The query is like that :
> DECLARE @.StockAmount int
> SELECT @.StockAmount = Amont FROM Stocks WHERE CustomerId =
@.BuyerCustomerId
> IF @.StockAmount > @.SaleAmount
> UPDATE Stocks SET amount = @.StockAmount - @.SaleAmount WHERE CustomerId =
> @.BuyerCustomerId
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId =
> @.SellerCustomerId
> UPDATE Stocks SET amount = @.StockAmount + @.SaleAmount WHERE CustomerId =
> @.SellerCustomerId
> i know, each customer has got not so many users and a concurrency problem
> seems to be not a big possibility. but there is just a 1 supplier record
and
> i think conlicts are possible. how can i alter this problem. after a
> research, i found
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK)
WHERE
> CustomerId = @.SellerCustomerId
> seems to work fine for us, but it will block the row untill the
transaction
> finishes. any approaches are appreciated. thanks.
>|||You can use locking hint "UPDLOCK" or you can modify the statement and use
something like:
UPDATE Stocks
SET amount = amount - @.SaleAmount
WHERE CustomerId = @.BuyerCustomerId and amount > @.SaleAmount
...
AMB
"The Crow" wrote:
> we are developing an ASP.NET application with SqlServer at backend..
> there are an supplier and about 3000 customer, and each customer has about
> 3-4 users. they are selling mobile phone counters.
> while a sale occurs, we are selecting customers stock amount, if it is
> bigger then sale amount, we are updating its stock, and we increment
> suppliers stock. The query is like that :
> DECLARE @.StockAmount int
> SELECT @.StockAmount = Amont FROM Stocks WHERE CustomerId = @.BuyerCustomerI
d
> IF @.StockAmount > @.SaleAmount
> UPDATE Stocks SET amount = @.StockAmount - @.SaleAmount WHERE CustomerId =
> @.BuyerCustomerId
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId =
> @.SellerCustomerId
> UPDATE Stocks SET amount = @.StockAmount + @.SaleAmount WHERE CustomerId =
> @.SellerCustomerId
> i know, each customer has got not so many users and a concurrency problem
> seems to be not a big possibility. but there is just a 1 supplier record a
nd
> i think conlicts are possible. how can i alter this problem. after a
> research, i found
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK) WHE
RE
> CustomerId = @.SellerCustomerId
> seems to work fine for us, but it will block the row untill the transactio
n
> finishes. any approaches are appreciated. thanks.
>
>|||This is the SQL Server Books Online explanations :
UPDATE LOCK :
Used on resources that can be updated. Prevents a common form of deadlock
that occurs when multiple sessions are reading, locking, and potentially
updating resources later.
UPDLOCK :
Takes update locks instead of shared locks. Cannot be used with NOLOCK or
XLOCK.
so, shared lock as u may know doesnt prevent reading data but modyfying,
which is not the case.|||UPDATE Stocks
SET amount = amount - @.SaleAmount
WHERE CustomerId = @.BuyerCustomerId and amount > @.SaleAmount
this statement "SELECT"s the suitable row aquiring update lock which behaves
same as shared lock, and then converts it exclusive lock prior to doing
actual update. isnt it?
Sunday, March 25, 2012
Database backup using c#
Hi can anybody know how to get the functionality of database backup and restore of a sql server2005 using asp.net 2.0 and c#..
My problem is here I'm creating an application which uses Emp file. and I'm trying to get backup of that database and again I'm trying to restore with that backup file. But here I'm getting the error message like "Database is already in use"
Is there any solution for that?
Regards,
Nagu
Hi Nagu,
Please check to see if the database is being used by some other objects, such as replications.
If that doesn't work, please show some of your restored database code. Thanks!
Monday, March 19, 2012
Database attach failed
Here's the scoop, I got a generic-purpose database (in the form of
*.mdb and *.ldf) from a 'reputable' source on the net. But when I
attempted to attach it to my sql server 2000 with SP3 with EM, it
failed complaining "Could not find row in sysindexes for database ID
10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes."
Then, I attempted to command line attach (thought it may have some
option...), same outcome. Then, ran a search on this NG, and found
the following thread, the question is, MS most likely would not
support something like this, so now what? Also thought about
manually adding a row to sysindexes table to 'fool the attach process'
but after looking at some sample data in this table, I don't think
it's a good idea to try, what can I do? Thanks.
That message appears familiar. I seem to recall that is what happens
if you try to attach an SQL 2005 database on SQL 2000. As far as
SQL 2000 that is a database that is alien, and for which it cannot
really have any graceful handling off.
Which is from 1998, and applies to really old versions of SQL Server.
>
That message appears familiar. I seem to recall that is what happens
if you try to attach an SQL 2005 database on SQL 2000. As far as
SQL 2000 that is a database that is alien, and for which it cannot
really have any graceful handling off.
>
>
Which is from 1998, and applies to really old versions of SQL Server.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks for the follow-up, Erland. I tried to attach it with sql
server 2005 as well, the restored db seemed weired with "D:\aName
\blakdsm\blad..", empty, not workable. Now, suppose I can get a
clean/good copy for server 2005, what are the chances of success of
using DUMB database then BACKUP or LOAD back into server 2000? These
two boxes are not connected and they can't.
If you need to move a database from SQL 2005 to SQL 2000 you need to
create from scripts and copy data to file with bulk copy. If the database
uses features that do not exist in SQL 2000, you will have to make some
compromises.
>
If you need to move a database from SQL 2005 to SQL 2000 you need to
create from scripts and copy data to file with bulk copy. If the database
uses features that do not exist in SQL 2000, you will have to make some
compromises.
>
You cannot restore a backup from SQL 2005 on SQL 2000. For quite obvious
reasons: there are features in SQl 2005 for which SQL 2000 is not prepared.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks, Erland, bcp is clumsy or because I'm not an expert of it :)
Viable options for the task all seem to consume quite a bit of time
but hey got to do the job. Once again I appreciate it.
*.mdb and *.ldf) from a 'reputable' source on the net. But when I
attempted to attach it to my sql server 2000 with SP3 with EM, it
failed complaining "Could not find row in sysindexes for database ID
10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes."
Then, I attempted to command line attach (thought it may have some
option...), same outcome. Then, ran a search on this NG, and found
the following thread, the question is, MS most likely would not
support something like this, so now what? Also thought about
manually adding a row to sysindexes table to 'fool the attach process'
but after looking at some sample data in this table, I don't think
it's a good idea to try, what can I do? Thanks.
http://groups.google.com/group/comp...3bee910fa30aa9atime management (tatata9999@.gmail.com) writes:
Quote:
Originally Posted by
Here's the scoop, I got a generic-purpose database (in the form of
*.mdb and *.ldf) from a 'reputable' source on the net. But when I
attempted to attach it to my sql server 2000 with SP3 with EM, it
failed complaining "Could not find row in sysindexes for database ID
10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes."
That message appears familiar. I seem to recall that is what happens
if you try to attach an SQL 2005 database on SQL 2000. As far as
SQL 2000 that is a database that is alien, and for which it cannot
really have any graceful handling off.
Quote:
Originally Posted by
Then, I attempted to command line attach (thought it may have some
option...), same outcome. Then, ran a search on this NG, and found
the following thread,
Which is from 1998, and applies to really old versions of SQL Server.
--
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|||On Nov 19, 5:32 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
time management (tatata9...@.gmail.com) writes:
Quote:
Originally Posted by
Here's the scoop, I got a generic-purpose database (in the form of
*.mdb and *.ldf) from a 'reputable' source on the net. But when I
attempted to attach it to my sql server 2000 with SP3 with EM, it
failed complaining "Could not find row in sysindexes for database ID
10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes."
>
That message appears familiar. I seem to recall that is what happens
if you try to attach an SQL 2005 database on SQL 2000. As far as
SQL 2000 that is a database that is alien, and for which it cannot
really have any graceful handling off.
>
Quote:
Originally Posted by
Then, I attempted to command line attach (thought it may have some
option...), same outcome. Then, ran a search on this NG, and found
the following thread,
>
Which is from 1998, and applies to really old versions of SQL Server.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks for the follow-up, Erland. I tried to attach it with sql
server 2005 as well, the restored db seemed weired with "D:\aName
\blakdsm\blad..", empty, not workable. Now, suppose I can get a
clean/good copy for server 2005, what are the chances of success of
using DUMB database then BACKUP or LOAD back into server 2000? These
two boxes are not connected and they can't.
Don|||time management (tatata9999@.gmail.com) writes:
Quote:
Originally Posted by
Thanks for the follow-up, Erland. I tried to attach it with sql
server 2005 as well, the restored db seemed weired with "D:\aName
\blakdsm\blad..", empty, not workable. Now, suppose I can get a
clean/good copy for server 2005, what are the chances of success of
using DUMB database then BACKUP or LOAD back into server 2000? These
two boxes are not connected and they can't.
If you need to move a database from SQL 2005 to SQL 2000 you need to
create from scripts and copy data to file with bulk copy. If the database
uses features that do not exist in SQL 2000, you will have to make some
compromises.
You cannot restore a backup from SQL 2005 on SQL 2000. For quite obvious
reasons: there are features in SQl 2005 for which SQL 2000 is not prepared.
--
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|||On Nov 20, 5:31 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
time management (tatata9...@.gmail.com) writes:
Quote:
Originally Posted by
Thanks for the follow-up, Erland. I tried to attach it with sql
server 2005 as well, the restored db seemed weired with "D:\aName
\blakdsm\blad..", empty, not workable. Now, suppose I can get a
clean/good copy for server 2005, what are the chances of success of
using DUMB database then BACKUP or LOAD back into server 2000? These
two boxes are not connected and they can't.
>
If you need to move a database from SQL 2005 to SQL 2000 you need to
create from scripts and copy data to file with bulk copy. If the database
uses features that do not exist in SQL 2000, you will have to make some
compromises.
>
You cannot restore a backup from SQL 2005 on SQL 2000. For quite obvious
reasons: there are features in SQl 2005 for which SQL 2000 is not prepared.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks, Erland, bcp is clumsy or because I'm not an expert of it :)
Viable options for the task all seem to consume quite a bit of time
but hey got to do the job. Once again I appreciate it.
Don
Sunday, March 11, 2012
Database Architecture
hi
i am haveing 1.5 years exp in asp.net(c#),i want to improve my knowlege in Database Architecture (datmodeling,uml,normalization,etc..).could anyone suggest me course or any booksPerhaps Pro SQL Server 2005 Database Design and Optimization by Louis Davidson|||
i am haveing 1.5 years exp in asp.net(c#),i want to improve my knowlege in Database Architecture (datmodeling,uml,normalization,etc..).could anyone suggest me course or any booksPerhaps Pro SQL Server 2005 Database Design and Optimization by Louis Davidson|||
If you are trying to get a grounding in data modeling, I'd recommend starting with entity-relationship models. These are the classic models used in transactional systems. You'll need to learn basic methodologies as well as the first 4 normal forms (1NF, 2NF, 3NF, Boyce-Codd NF aka BCNF aka 3.5NF). I've found Graeme Simsion's Data Modeling Essentials to be a good starting point for this. In addition, you may want to check out books by Joe Celko (though his stuff can be a bit more advanced).
Once you get a solid footing in entity-relationship (E-R) modeling, you may want to look into dimensional modeling. This is foundation of data warehousing. Ralph Kimball's Data Warehousing Toolkit is considered the Bible on this topic. (His class, Dimensional Modeling In-Depth, which follows this book is also top-notch.)
Good luck,
Bryan Smith
Thursday, March 8, 2012
Database Admin Tool Prerequisites
The prerequisites for the Database Admin Tool says:
a.. Microsoft .NET Framework 2.0
a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
2005 SP2 (32-bit only)
Does that mean the Database Admin Tool will work with SQL Server 2005
Workgroup Edition?
Hi
"Carel" wrote:
> The prerequisites for the Database Admin Tool says:
> a.. Microsoft .NET Framework 2.0
> a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
> 2005 SP2 (32-bit only)
> Does that mean the Database Admin Tool will work with SQL Server 2005
> Workgroup Edition?
>
The prerequisites say you have installed service pack 2 and .NET Framework
2.0. Workgroup Edition is only available as 32bit
John
|||Thanks John,
But I am still not clear. The prerequisities only mention SQL Express & SQL
Server.
Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
database?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4B6538F3-6F44-4F37-B945-BBACA7EEEA00@.microsoft.com...
> Hi
> "Carel" wrote:
> The prerequisites say you have installed service pack 2 and .NET Framework
> 2.0. Workgroup Edition is only available as 32bit
> John
|||Hi Carel
"Carel" wrote:
> Thanks John,
> But I am still not clear. The prerequisities only mention SQL Express & SQL
> Server.
> Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
> database?
>
Yes, Microsoft SQL Server 2005 (32 bit) will refer to all editions
(Standard, Enterprise, Workgroup and Developer) including Workgroup.
John
a.. Microsoft .NET Framework 2.0
a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
2005 SP2 (32-bit only)
Does that mean the Database Admin Tool will work with SQL Server 2005
Workgroup Edition?
Hi
"Carel" wrote:
> The prerequisites for the Database Admin Tool says:
> a.. Microsoft .NET Framework 2.0
> a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
> 2005 SP2 (32-bit only)
> Does that mean the Database Admin Tool will work with SQL Server 2005
> Workgroup Edition?
>
The prerequisites say you have installed service pack 2 and .NET Framework
2.0. Workgroup Edition is only available as 32bit
John
|||Thanks John,
But I am still not clear. The prerequisities only mention SQL Express & SQL
Server.
Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
database?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4B6538F3-6F44-4F37-B945-BBACA7EEEA00@.microsoft.com...
> Hi
> "Carel" wrote:
> The prerequisites say you have installed service pack 2 and .NET Framework
> 2.0. Workgroup Edition is only available as 32bit
> John
|||Hi Carel
"Carel" wrote:
> Thanks John,
> But I am still not clear. The prerequisities only mention SQL Express & SQL
> Server.
> Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
> database?
>
Yes, Microsoft SQL Server 2005 (32 bit) will refer to all editions
(Standard, Enterprise, Workgroup and Developer) including Workgroup.
John
Database Admin Tool Prerequisites
The prerequisites for the Database Admin Tool says:
a.. Microsoft .NET Framework 2.0
a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
2005 SP2 (32-bit only)
Does that mean the Database Admin Tool will work with SQL Server 2005
Workgroup Edition?Hi
"Carel" wrote:
> The prerequisites for the Database Admin Tool says:
> a.. Microsoft .NET Framework 2.0
> a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
> 2005 SP2 (32-bit only)
> Does that mean the Database Admin Tool will work with SQL Server 2005
> Workgroup Edition?
>
The prerequisites say you have installed service pack 2 and .NET Framework
2.0. Workgroup Edition is only available as 32bit
John|||Thanks John,
But I am still not clear. The prerequisities only mention SQL Express & SQL
Server.
Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
database?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4B6538F3-6F44-4F37-B945-BBACA7EEEA00@.microsoft.com...
> Hi
> "Carel" wrote:
>
> The prerequisites say you have installed service pack 2 and .NET Framework
> 2.0. Workgroup Edition is only available as 32bit
> John|||Hi Carel
"Carel" wrote:
> Thanks John,
> But I am still not clear. The prerequisities only mention SQL Express & SQ
L
> Server.
> Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
> database?
>
Yes, Microsoft SQL Server 2005 (32 bit) will refer to all editions
(Standard, Enterprise, Workgroup and Developer) including Workgroup.
John
a.. Microsoft .NET Framework 2.0
a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
2005 SP2 (32-bit only)
Does that mean the Database Admin Tool will work with SQL Server 2005
Workgroup Edition?Hi
"Carel" wrote:
> The prerequisites for the Database Admin Tool says:
> a.. Microsoft .NET Framework 2.0
> a.. Microsoft SQL Express 2005 SP2 (32-bit only) or Microsoft SQL Server
> 2005 SP2 (32-bit only)
> Does that mean the Database Admin Tool will work with SQL Server 2005
> Workgroup Edition?
>
The prerequisites say you have installed service pack 2 and .NET Framework
2.0. Workgroup Edition is only available as 32bit
John|||Thanks John,
But I am still not clear. The prerequisities only mention SQL Express & SQL
Server.
Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
database?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4B6538F3-6F44-4F37-B945-BBACA7EEEA00@.microsoft.com...
> Hi
> "Carel" wrote:
>
> The prerequisites say you have installed service pack 2 and .NET Framework
> 2.0. Workgroup Edition is only available as 32bit
> John|||Hi Carel
"Carel" wrote:
> Thanks John,
> But I am still not clear. The prerequisities only mention SQL Express & SQ
L
> Server.
> Will the Database Admin Tool work with a SQL Server 2005 Workgroup Edition
> database?
>
Yes, Microsoft SQL Server 2005 (32 bit) will refer to all editions
(Standard, Enterprise, Workgroup and Developer) including Workgroup.
John
Database access log
SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHello John,
This will have to be done when your application authenticates. It will need
to create an audit of who logs in and when. This isn't a function of the
database but a function of the way your application is designed.
Aaron Weiker
http://aaronweiker.com/
> SQL 2K
> We developed a VB.NET application (SQL 2k db) where users can log into
> the system and extract reports. We are planning to create a report
> with user list who accessed the application in last 30 days.
> Can this be achieved by querying directly to the database '
> Thanks
> John|||Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHello John,
This will have to be done when your application authenticates. It will need
to create an audit of who logs in and when. This isn't a function of the
database but a function of the way your application is designed.
Aaron Weiker
http://aaronweiker.com/
> SQL 2K
> We developed a VB.NET application (SQL 2k db) where users can log into
> the system and extract reports. We are planning to create a report
> with user list who accessed the application in last 30 days.
> Can this be achieved by querying directly to the database '
> Thanks
> John|||Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
Database access log
SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
Database access log
SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database ?
Thanks
John
Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database ?
Thanks
John
Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
Database access log
SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
Database Access Control
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
Check out Application Roles
(http://msdn.microsoft.com/library/de...urity_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:
>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>
|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
David Portas
SQL Server MVP
|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:
> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>
|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
Check out Application Roles
(http://msdn.microsoft.com/library/de...urity_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:
>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>
|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
David Portas
SQL Server MVP
|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:
> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>
|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
Database Access Control
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John BCheck out Application Roles
(http://msdn.microsoft.com/library/d...
rity_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:
>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
David Portas
SQL Server MVP
--|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:
> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John BCheck out Application Roles
(http://msdn.microsoft.com/library/d...
rity_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:
>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
David Portas
SQL Server MVP
--|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:
> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
Database Access Control
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John BThis is a multi-part message in MIME format.
--030706090505000407000501
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Check out Application Roles
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:
>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>
--030706090505000407000501
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Check out Application Roles (<a
href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp</a>).<br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp</a>).<br>
<br>
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in
force until the client connection drops out (ie. disconnects).<br>
<br>
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader
+ db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.<br>
<br>
HTH<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mailto:mike.hodgson@.mallesons.nospam.com</a>
|</font><b><font face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
MS User wrote:
<blockquote cite="midOxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
</pre>
</blockquote>
</body>
</html>
--030706090505000407000501--|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
--
David Portas
SQL Server MVP
--|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:
> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John BThis is a multi-part message in MIME format.
--030706090505000407000501
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Check out Application Roles
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:
>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>
--030706090505000407000501
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Check out Application Roles (<a
href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp</a>).<br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp</a>).<br>
<br>
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in
force until the client connection drops out (ie. disconnects).<br>
<br>
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader
+ db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.<br>
<br>
HTH<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mailto:mike.hodgson@.mallesons.nospam.com</a>
|</font><b><font face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
MS User wrote:
<blockquote cite="midOxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
</pre>
</blockquote>
</body>
</html>
--030706090505000407000501--|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
--
David Portas
SQL Server MVP
--|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:
> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
Database Access Control
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John BCheck out Application Roles
(http://msdn.microsoft.com/library/d...
rity_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:
>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
David Portas
SQL Server MVP
--|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:
> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John BCheck out Application Roles
(http://msdn.microsoft.com/library/d...
rity_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:
>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
David Portas
SQL Server MVP
--|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:
> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
Wednesday, March 7, 2012
Database & Flash Wear Management
Hello everyone,
I'm currently developping a windows .net compact framework application which is basically a local datalogger.
Since my application will log data to the database (located on compact flash card) a few times a second over long period, I wonder if SQL Server Compact Edition offers some mechanism to reduce disk access.
By example, can SQL Server compact edition wait let's say 5-10 "insert into" commands before actually write to the database located on the flash card ?.
Any ideas which could help me to reduce flash wear would be greatly appreciated !
Thanks
The storage engine introduced in SQL Mobile (v3.0) and currently in SQL CE is storage-card aware.
If you feel you need to queue up inserts to further reduce writes to the card, that would be something that
you would need to code into your logger application.
My suggestion, instead of queuing your DML commands would be to periodically perform a Verify
and if needed a Repair on the database itself. See the SqlCeEngine documentation for samples
of how to perform these checks. Repair will reorganize the database (reorder indexes, reclaim
unused page space, etc) and int he process the physical file will be rewritten on the storage card.
Regards,
Darren Shaffer
|||Thank you very much ! It's very appreciated
Regards,
Emmanuel
Friday, February 24, 2012
Data Types
I'm creating a windows .net form that has SQL as the back-end. One of the
controls is a group box with three radio buttons options. What data type
should this column have, so whatever option the user chooses will be saved o
n
the SQL table?
Another one, a YES/NO field, what data type should this column have?
--
TSHi TS,
You can use a numeric field and store 1 or 0.
please let me know if u have any questions.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"TS" wrote:
> I'm creating a windows .net form that has SQL as the back-end. One of the
> controls is a group box with three radio buttons options. What data type
> should this column have, so whatever option the user chooses will be saved
on
> the SQL table?
> Another one, a YES/NO field, what data type should this column have?
> --
> TS|||With three radio buttons you either have three or four options - if you allo
w
none of the options to be selected (maybe as an initial state).
E.g.
null - none selected
0 - first selected
1 - second selected
2 - third selected
etc.
I believe TINYINT could cover that. Look it up in Books Online if you
haven't already.
However, a more humanly-readable aproach would be to use more descriptive
values - make use of enumerations in .Net and map them to a table in your
database if appropriate. After all - this is the 21st century. ;)
ML|||On Thu, 27 Oct 2005 08:26:13 -0700, TS wrote:
>I'm creating a windows .net form that has SQL as the back-end. One of the
>controls is a group box with three radio buttons options. What data type
>should this column have, so whatever option the user chooses will be saved
on
>the SQL table?
>Another one, a YES/NO field, what data type should this column have?
Hi TS,
You're approaching this from the wrong side.
You should first investigate what data is needed for the application
that you are creating. Identify the dependencies and the business rules,
then normalize the data to at least third normal form and create your
tables.
Once that is done, the next step is to create a GUI that makes it as
easy as possible for the user to enter the required data into the
system. For a column where only a few specific options are legal, a
group box with radio buttons might be fine (but so might a textbox with
dropdown). For a column with only two legal values, a yes/no button
might be appropriate (but a checkbox, a dropdown, or a set of two radio
buttons might do as well).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
controls is a group box with three radio buttons options. What data type
should this column have, so whatever option the user chooses will be saved o
n
the SQL table?
Another one, a YES/NO field, what data type should this column have?
--
TSHi TS,
You can use a numeric field and store 1 or 0.
please let me know if u have any questions.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"TS" wrote:
> I'm creating a windows .net form that has SQL as the back-end. One of the
> controls is a group box with three radio buttons options. What data type
> should this column have, so whatever option the user chooses will be saved
on
> the SQL table?
> Another one, a YES/NO field, what data type should this column have?
> --
> TS|||With three radio buttons you either have three or four options - if you allo
w
none of the options to be selected (maybe as an initial state).
E.g.
null - none selected
0 - first selected
1 - second selected
2 - third selected
etc.
I believe TINYINT could cover that. Look it up in Books Online if you
haven't already.
However, a more humanly-readable aproach would be to use more descriptive
values - make use of enumerations in .Net and map them to a table in your
database if appropriate. After all - this is the 21st century. ;)
ML|||On Thu, 27 Oct 2005 08:26:13 -0700, TS wrote:
>I'm creating a windows .net form that has SQL as the back-end. One of the
>controls is a group box with three radio buttons options. What data type
>should this column have, so whatever option the user chooses will be saved
on
>the SQL table?
>Another one, a YES/NO field, what data type should this column have?
Hi TS,
You're approaching this from the wrong side.
You should first investigate what data is needed for the application
that you are creating. Identify the dependencies and the business rules,
then normalize the data to at least third normal form and create your
tables.
Once that is done, the next step is to create a GUI that makes it as
easy as possible for the user to enter the required data into the
system. For a column where only a few specific options are legal, a
group box with radio buttons might be fine (but so might a textbox with
dropdown). For a column with only two legal values, a yes/no button
might be appropriate (but a checkbox, a dropdown, or a set of two radio
buttons might do as well).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Data Types
Hello, I am following this article about building an ASP.Net application which uses SQL Express 2005. The document calls for building a couple of tables. One column is identified with data type of "Byte" which are is available for selection within the Visual Studio 2005 interface. The following values show in the drop down of data type in VS2005:
Bigint
Binary(5)
Bit
Char(10)
DateTime
Decimal (18,0)
Float
Image
Int
Money
NChar(10)
NText
Numeric(18,0)
Nvarchar(50)
Nvarchar(max)
Real
SmallDateTime
SmallInt
SmallMoney
Sql_Variant
Text
Timestamp
TinyInt
UniqueIdentifier
Varbinary(50)
VarBinary(max)
varchar(10)
Varchar(max)
XML
I choose Char with a size of 1. Would this be correct, or am I missing something?
Thanks in advance for your assistance!!
SqlByte maps to tinyint.
Subscribe to:
Posts (Atom)