benefits of full recovery**

Hi
What's the benefit usage to select Full recovery model for a database?
I know it's better to select simple mode for developement and test
enviroment.
But I don't know excatly how can the full recovery model be usefull for
critical enviroment?
Can any body give me an example?
Thanks alot
Maryam,
Here is a nice article:
http://insight.zdnet.co.uk/software/...4171-1,00.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>
|||maryam rezvani wrote:
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull
> for critical enviroment?
> Can any body give me an example?
> Thanks alot
short:
if your DB is in full recovery model, you will have to backup the log, if
you can several times/day. In case of a restore, you probably will not lose
any data, and you can recovery to the point in time (useful if someone
deletes something or some other human error)
long:
see BooksOnLine (mssql help)
|||Hi
By using 'FULL recovery' mode you will be able to restore your database at
point time
For example , you do a full backup on sunday night and every hour a log file
backup during the work day ,so on tuesday at 11 AM the database is crashed
..If you have the database in 'SIMPLE recovery' you will be able to get the
database back only from last backup (sunday night) ,that means you lost the
data of monday,tuesday . However , if you have 'FULL recovery' you have to
restore the last database backup and the apply all LOG backups till 11AM
(see more details in the BOL)
Another important point is
Let say you do a FULL backup database every sunday night and every hour a
LOG file backup. Now, you lost the FULL database backup of the second week
and the database is get corrupted now. So what would you do, you are about
to lost the data of two weeks ( if you do only FULL backup database (SIMPLE
recovery) ) ,but in that case you restore the last full backup (two weeks
ago) and the apply all LOG files you perfom till the corruption. You will
lost nothing because LOG file backup contains all info since last LOG file
backup.
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>
|||Thanks
Do you mean that if I found that some false insertion happenning to a
special table
in 9:00 am and my db is in full recovery model so I can restore my db to the
point
exaclty before the false insertion through the log file?
but if I set my db to simple mode have to restore the previous backup
related to the day before(cause I back up my db every night)?
"Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
news:1099553502.722414@.internet.fina.hr...
> maryam rezvani wrote:
> short:
> if your DB is in full recovery model, you will have to backup the log, if
> you can several times/day. In case of a restore, you probably will not
lose
> any data, and you can recovery to the point in time (useful if someone
> deletes something or some other human error)
> long:
> see BooksOnLine (mssql help)
>
|||Correct. But for the first scenario, you also need to perform regular transaction log backups. When
you restore a transaction log backup, you can stop at a certain point in time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:egP72OkwEHA.4004@.tk2msftngp13.phx.gbl...
> Thanks
> Do you mean that if I found that some false insertion happenning to a
> special table
> in 9:00 am and my db is in full recovery model so I can restore my db to the
> point
> exaclty before the false insertion through the log file?
> but if I set my db to simple mode have to restore the previous backup
> related to the day before(cause I back up my db every night)?
> "Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
> news:1099553502.722414@.internet.fina.hr...
> lose
>

benefits of full recovery**

Hi
What's the benefit usage to select Full recovery model for a database?
I know it's better to select simple mode for developement and test
enviroment.
But I don't know excatly how can the full recovery model be usefull for
critical enviroment?
Can any body give me an example?
Thanks alotMaryam,
Here is a nice article:
http://insight.zdnet.co.uk/software...34171-1,00.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>|||maryam rezvani wrote:
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull
> for critical enviroment?
> Can any body give me an example?
> Thanks alot
short:
if your DB is in full recovery model, you will have to backup the log, if
you can several times/day. In case of a restore, you probably will not lose
any data, and you can recovery to the point in time (useful if someone
deletes something or some other human error)
long:
see BooksOnLine (mssql help)|||Hi
By using 'FULL recovery' mode you will be able to restore your database at
point time
For example , you do a full backup on sunday night and every hour a log file
backup during the work day ,so on tuesday at 11 AM the database is crashed
.If you have the database in 'SIMPLE recovery' you will be able to get the
database back only from last backup (sunday night) ,that means you lost the
data of monday,tuesday . However , if you have 'FULL recovery' you have to
restore the last database backup and the apply all LOG backups till 11AM
(see more details in the BOL)
Another important point is
Let say you do a FULL backup database every sunday night and every hour a
LOG file backup. Now, you lost the FULL database backup of the second week
and the database is get corrupted now. So what would you do, you are about
to lost the data of two weeks ( if you do only FULL backup database (SIMPLE
recovery) ) ,but in that case you restore the last full backup (two weeks
ago) and the apply all LOG files you perfom till the corruption. You will
lost nothing because LOG file backup contains all info since last LOG file
backup.
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>|||Thanks
Do you mean that if I found that some false insertion happenning to a
special table
in 9:00 am and my db is in full recovery model so I can restore my db to the
point
exaclty before the false insertion through the log file?
but if I set my db to simple mode have to restore the previous backup
related to the day before(cause I back up my db every night)?
"Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
news:1099553502.722414@.internet.fina.hr...
> maryam rezvani wrote:
> short:
> if your DB is in full recovery model, you will have to backup the log, if
> you can several times/day. In case of a restore, you probably will not
lose
> any data, and you can recovery to the point in time (useful if someone
> deletes something or some other human error)
> long:
> see BooksOnLine (mssql help)
>|||Correct. But for the first scenario, you also need to perform regular transa
ction log backups. When
you restore a transaction log backup, you can stop at a certain point in tim
e.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:egP72OkwEHA.4004@.tk2msftngp13.phx.gbl...
> Thanks
> Do you mean that if I found that some false insertion happenning to a
> special table
> in 9:00 am and my db is in full recovery model so I can restore my db to t
he
> point
> exaclty before the false insertion through the log file?
> but if I set my db to simple mode have to restore the previous backup
> related to the day before(cause I back up my db every night)?
> "Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
> news:1099553502.722414@.internet.fina.hr...
> lose
>

benefits of full recovery**

Hi
What's the benefit usage to select Full recovery model for a database?
I know it's better to select simple mode for developement and test
enviroment.
But I don't know excatly how can the full recovery model be usefull for
critical enviroment?
Can any body give me an example?
Thanks alotMaryam,
Here is a nice article:
http://insight.zdnet.co.uk/software/developer/0,39020469,2134171-1,00.htm.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>|||maryam rezvani wrote:
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull
> for critical enviroment?
> Can any body give me an example?
> Thanks alot
short:
if your DB is in full recovery model, you will have to backup the log, if
you can several times/day. In case of a restore, you probably will not lose
any data, and you can recovery to the point in time (useful if someone
deletes something or some other human error)
long:
see BooksOnLine (mssql help)|||Hi
By using 'FULL recovery' mode you will be able to restore your database at
point time
For example , you do a full backup on sunday night and every hour a log file
backup during the work day ,so on tuesday at 11 AM the database is crashed
.If you have the database in 'SIMPLE recovery' you will be able to get the
database back only from last backup (sunday night) ,that means you lost the
data of monday,tuesday . However , if you have 'FULL recovery' you have to
restore the last database backup and the apply all LOG backups till 11AM
(see more details in the BOL)
Another important point is
Let say you do a FULL backup database every sunday night and every hour a
LOG file backup. Now, you lost the FULL database backup of the second week
and the database is get corrupted now. So what would you do, you are about
to lost the data of two weeks ( if you do only FULL backup database (SIMPLE
recovery) ) ,but in that case you restore the last full backup (two weeks
ago) and the apply all LOG files you perfom till the corruption. You will
lost nothing because LOG file backup contains all info since last LOG file
backup.
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>|||Thanks
Do you mean that if I found that some false insertion happenning to a
special table
in 9:00 am and my db is in full recovery model so I can restore my db to the
point
exaclty before the false insertion through the log file?
but if I set my db to simple mode have to restore the previous backup
related to the day before(cause I back up my db every night)?
"Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
news:1099553502.722414@.internet.fina.hr...
> maryam rezvani wrote:
> > Hi
> >
> > What's the benefit usage to select Full recovery model for a database?
> > I know it's better to select simple mode for developement and test
> > enviroment.
> > But I don't know excatly how can the full recovery model be usefull
> > for critical enviroment?
> >
> > Can any body give me an example?
> > Thanks alot
> short:
> if your DB is in full recovery model, you will have to backup the log, if
> you can several times/day. In case of a restore, you probably will not
lose
> any data, and you can recovery to the point in time (useful if someone
> deletes something or some other human error)
> long:
> see BooksOnLine (mssql help)
>|||Correct. But for the first scenario, you also need to perform regular transaction log backups. When
you restore a transaction log backup, you can stop at a certain point in time.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:egP72OkwEHA.4004@.tk2msftngp13.phx.gbl...
> Thanks
> Do you mean that if I found that some false insertion happenning to a
> special table
> in 9:00 am and my db is in full recovery model so I can restore my db to the
> point
> exaclty before the false insertion through the log file?
> but if I set my db to simple mode have to restore the previous backup
> related to the day before(cause I back up my db every night)?
> "Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
> news:1099553502.722414@.internet.fina.hr...
> > maryam rezvani wrote:
> > > Hi
> > >
> > > What's the benefit usage to select Full recovery model for a database?
> > > I know it's better to select simple mode for developement and test
> > > enviroment.
> > > But I don't know excatly how can the full recovery model be usefull
> > > for critical enviroment?
> > >
> > > Can any body give me an example?
> > > Thanks alot
> >
> > short:
> >
> > if your DB is in full recovery model, you will have to backup the log, if
> > you can several times/day. In case of a restore, you probably will not
> lose
> > any data, and you can recovery to the point in time (useful if someone
> > deletes something or some other human error)
> >
> > long:
> >
> > see BooksOnLine (mssql help)
> >
> >
>

Benefit of SQL authentication.

I asked the question last week about if there would ever be any benefit to
SQL authentication over WINNT (if I had the option of using either one) and
the consensus seemed to be “no”. Thanks to those that replied, but now I
have
another question. I currently have 2 environments. Our “legacy “environm
ent
is opened up wide security wise. Way to loose. Lots of folks have excessive
permissions in our SQL Servers. Our new environment is very tightly
controlled. We only use SQL authentication for DB connections, and very few
people know those passwords. There is a proposal to start using WINNT Auth,
but here is the problem that I see. Currently, no end users can make direct
connections to the DB as they do not know the password to the SQL logins.
Essentially they have to use the app provided. But if I suddenly start using
WINNT Auth, each end user will then be able to make a direct connection to
the DB using Enterprise Manager, Access, whatever they want. Many of our App
s
are written to require direct Read/ Write access to our tables, so being abl
e
to enforce data manipulation only through the use of Stored Procs will never
happen. The only way I know to be able to avoid this is to use Application
Roles, but I wanted to see if anyone else had any other ideas, and what
others were doing to remedy this?
TIA, ChrisRHi Chris
An application role will give you similar benefits to your current setup and
only minimum permissions need to be given to the windows accounts. You could
also only allow members of a specific windows group a login to restrict the
number of people that can connect.
John
"ChrisR" wrote:

> I asked the question last week about if there would ever be any benefit to
> SQL authentication over WINNT (if I had the option of using either one) an
d
> the consensus seemed to be “no”. Thanks to those that replied, but now
I have
> another question. I currently have 2 environments. Our “legacy “enviro
nment
> is opened up wide security wise. Way to loose. Lots of folks have excessiv
e
> permissions in our SQL Servers. Our new environment is very tightly
> controlled. We only use SQL authentication for DB connections, and very fe
w
> people know those passwords. There is a proposal to start using WINNT Auth
,
> but here is the problem that I see. Currently, no end users can make direc
t
> connections to the DB as they do not know the password to the SQL logins.
> Essentially they have to use the app provided. But if I suddenly start usi
ng
> WINNT Auth, each end user will then be able to make a direct connection to
> the DB using Enterprise Manager, Access, whatever they want. Many of our A
pps
> are written to require direct Read/ Write access to our tables, so being a
ble
> to enforce data manipulation only through the use of Stored Procs will nev
er
> happen. The only way I know to be able to avoid this is to use Application
> Roles, but I wanted to see if anyone else had any other ideas, and what
> others were doing to remedy this?
> TIA, ChrisR|||You are confusing the authentication and authorization. In SQL Server, using
Windows security means SQL Server authenticate user access by their windows
account (so no need to pass username/password since the user has logged on
to his computer with his windows user account and he has been authenticated
on who he is. However, just knowing who he is, does not mean you have to
give all accesses to him. He still can be denied any access at all, or is
given only necessary accesses.
Common and simple approach is create one or more windows user group. In SQL
Server, you assign different access permission to different groups. Say,
Group1 has all access permission, Group2 only hass "SELECT" permission on
Table1. Then you add userA and UseB to Group1, and allother users to
Group2... and so on. Very simple and easy to manage. You do not give every
windows user account to all access permission to the SQL Server.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
>I asked the question last week about if there would ever be any benefit to
> SQL authentication over WINNT (if I had the option of using either one)
> and
> the consensus seemed to be "no". Thanks to those that replied, but now I
> have
> another question. I currently have 2 environments. Our "legacy
> "environment
> is opened up wide security wise. Way to loose. Lots of folks have
> excessive
> permissions in our SQL Servers. Our new environment is very tightly
> controlled. We only use SQL authentication for DB connections, and very
> few
> people know those passwords. There is a proposal to start using WINNT
> Auth,
> but here is the problem that I see. Currently, no end users can make
> direct
> connections to the DB as they do not know the password to the SQL logins.
> Essentially they have to use the app provided. But if I suddenly start
> using
> WINNT Auth, each end user will then be able to make a direct connection to
> the DB using Enterprise Manager, Access, whatever they want. Many of our
> Apps
> are written to require direct Read/ Write access to our tables, so being
> able
> to enforce data manipulation only through the use of Stored Procs will
> never
> happen. The only way I know to be able to avoid this is to use Application
> Roles, but I wanted to see if anyone else had any other ideas, and what
> others were doing to remedy this?
> TIA, ChrisR|||Hi
I think that Chris' problem is that the design of his application means that
authenticated user require a wide range of authorization, including access t
o
the underlying tables. If authorization was solely restriced on Windows
Accounts or Groups then there would be no way to stop users bypassing his
applications and using other tools to manipulate the data. The application
role will make it harder for users to do this.
Another method would be to use views and have a security check (say a join
to his users table to check a 'IsCurrentlyLoggedIn' flag), but that would no
t
be very secure even if the views were encrypted.
John
"Norman Yuan" wrote:

> You are confusing the authentication and authorization. In SQL Server, usi
ng
> Windows security means SQL Server authenticate user access by their window
s
> account (so no need to pass username/password since the user has logged on
> to his computer with his windows user account and he has been authenticate
d
> on who he is. However, just knowing who he is, does not mean you have to
> give all accesses to him. He still can be denied any access at all, or is
> given only necessary accesses.
> Common and simple approach is create one or more windows user group. In SQ
L
> Server, you assign different access permission to different groups. Say,
> Group1 has all access permission, Group2 only hass "SELECT" permission on
> Table1. Then you add userA and UseB to Group1, and allother users to
> Group2... and so on. Very simple and easy to manage. You do not give ever
y
> windows user account to all access permission to the SQL Server.
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
>
>|||Yes, this is the dilema. As you referenced, I only want folks to use certain
apps, not whatever they want. I personally see this as a big problem with
using WINNT authentication.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D9AF704D-4D60-41C2-A789-1631DEB51B14@.microsoft.com...
> Hi
> I think that Chris' problem is that the design of his application means
that
> authenticated user require a wide range of authorization, including access
to
> the underlying tables. If authorization was solely restriced on Windows
> Accounts or Groups then there would be no way to stop users bypassing his
> applications and using other tools to manipulate the data. The application
> role will make it harder for users to do this.
> Another method would be to use views and have a security check (say a join
> to his users table to check a 'IsCurrentlyLoggedIn' flag), but that would
not[vbcol=seagreen]
> be very secure even if the views were encrypted.
>
> John
> "Norman Yuan" wrote:
>
using[vbcol=seagreen]
windows[vbcol=seagreen]
on[vbcol=seagreen]
authenticated[vbcol=seagreen]
is[vbcol=seagreen]
SQL[vbcol=seagreen]
on[vbcol=seagreen]
every[vbcol=seagreen]
to[vbcol=seagreen]
one)[vbcol=seagreen]
I[vbcol=seagreen]
very[vbcol=seagreen]
logins.[vbcol=seagreen]
connection to[vbcol=seagreen]
our[vbcol=seagreen]
being[vbcol=seagreen]
Application[vbcol=seagreen]
what[vbcol=seagreen]|||Hi Chris
I would argue that the way your system has been designed that causes your
problems and not Windows Authentication per se. Using Windows Authentication
provides better security and management. By using shared logins these
benefits are lost along with auditability. You can still use Windows
Authentication along with the application role(s), but you will not get the
auditability.
John
"ChrisR" wrote:

> Yes, this is the dilema. As you referenced, I only want folks to use certa
in
> apps, not whatever they want. I personally see this as a big problem with
> using WINNT authentication.
> Thanks.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D9AF704D-4D60-41C2-A789-1631DEB51B14@.microsoft.com...
> that
> to
> not
> using
> windows
> on
> authenticated
> is
> SQL
> on
> every
> to
> one)
> I
> very
> logins.
> connection to
> our
> being
> Application
> what
>
>|||> You can still use Windows
> Authentication along with the application role(s), but you will not get th
e
> auditability.
Not is user names are audited, but if logins names are audited, then nothing
is lost. Or am I
missing something?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86706F06-7B2C-4CA7-A062-4CB6AD28B82A@.microsoft.com...[vbcol=seagreen]
> Hi Chris
> I would argue that the way your system has been designed that causes your
> problems and not Windows Authentication per se. Using Windows Authenticati
on
> provides better security and management. By using shared logins these
> benefits are lost along with auditability. You can still use Windows
> Authentication along with the application role(s), but you will not get th
e
> auditability.
> John
> "ChrisR" wrote:
>|||Hi Tibor
No, you are correct! Using SYSTEM_USER would give them more information than
they could have currently with the shared SQL Server logins (unless they
share the windows logins as well!!)
John
"Tibor Karaszi" wrote:

> Not is user names are audited, but if logins names are audited, then nothi
ng is lost. Or am I
> missing something?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:86706F06-7B2C-4CA7-A062-4CB6AD28B82A@.microsoft.com...
>

Benefit of SQL authentication.

I asked the question last week about if there would ever be any benefit to
SQL authentication over WINNT (if I had the option of using either one) and
the consensus seemed to be â'noâ'. Thanks to those that replied, but now I have
another question. I currently have 2 environments. Our â'legacy â'environment
is opened up wide security wise. Way to loose. Lots of folks have excessive
permissions in our SQL Servers. Our new environment is very tightly
controlled. We only use SQL authentication for DB connections, and very few
people know those passwords. There is a proposal to start using WINNT Auth,
but here is the problem that I see. Currently, no end users can make direct
connections to the DB as they do not know the password to the SQL logins.
Essentially they have to use the app provided. But if I suddenly start using
WINNT Auth, each end user will then be able to make a direct connection to
the DB using Enterprise Manager, Access, whatever they want. Many of our Apps
are written to require direct Read/ Write access to our tables, so being able
to enforce data manipulation only through the use of Stored Procs will never
happen. The only way I know to be able to avoid this is to use Application
Roles, but I wanted to see if anyone else had any other ideas, and what
others were doing to remedy this?
TIA, ChrisRHi Chris
An application role will give you similar benefits to your current setup and
only minimum permissions need to be given to the windows accounts. You could
also only allow members of a specific windows group a login to restrict the
number of people that can connect.
John
"ChrisR" wrote:
> I asked the question last week about if there would ever be any benefit to
> SQL authentication over WINNT (if I had the option of using either one) and
> the consensus seemed to be â'noâ'. Thanks to those that replied, but now I have
> another question. I currently have 2 environments. Our â'legacy â'environment
> is opened up wide security wise. Way to loose. Lots of folks have excessive
> permissions in our SQL Servers. Our new environment is very tightly
> controlled. We only use SQL authentication for DB connections, and very few
> people know those passwords. There is a proposal to start using WINNT Auth,
> but here is the problem that I see. Currently, no end users can make direct
> connections to the DB as they do not know the password to the SQL logins.
> Essentially they have to use the app provided. But if I suddenly start using
> WINNT Auth, each end user will then be able to make a direct connection to
> the DB using Enterprise Manager, Access, whatever they want. Many of our Apps
> are written to require direct Read/ Write access to our tables, so being able
> to enforce data manipulation only through the use of Stored Procs will never
> happen. The only way I know to be able to avoid this is to use Application
> Roles, but I wanted to see if anyone else had any other ideas, and what
> others were doing to remedy this?
> TIA, ChrisR|||You are confusing the authentication and authorization. In SQL Server, using
Windows security means SQL Server authenticate user access by their windows
account (so no need to pass username/password since the user has logged on
to his computer with his windows user account and he has been authenticated
on who he is. However, just knowing who he is, does not mean you have to
give all accesses to him. He still can be denied any access at all, or is
given only necessary accesses.
Common and simple approach is create one or more windows user group. In SQL
Server, you assign different access permission to different groups. Say,
Group1 has all access permission, Group2 only hass "SELECT" permission on
Table1. Then you add userA and UseB to Group1, and allother users to
Group2... and so on. Very simple and easy to manage. You do not give every
windows user account to all access permission to the SQL Server.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
>I asked the question last week about if there would ever be any benefit to
> SQL authentication over WINNT (if I had the option of using either one)
> and
> the consensus seemed to be "no". Thanks to those that replied, but now I
> have
> another question. I currently have 2 environments. Our "legacy
> "environment
> is opened up wide security wise. Way to loose. Lots of folks have
> excessive
> permissions in our SQL Servers. Our new environment is very tightly
> controlled. We only use SQL authentication for DB connections, and very
> few
> people know those passwords. There is a proposal to start using WINNT
> Auth,
> but here is the problem that I see. Currently, no end users can make
> direct
> connections to the DB as they do not know the password to the SQL logins.
> Essentially they have to use the app provided. But if I suddenly start
> using
> WINNT Auth, each end user will then be able to make a direct connection to
> the DB using Enterprise Manager, Access, whatever they want. Many of our
> Apps
> are written to require direct Read/ Write access to our tables, so being
> able
> to enforce data manipulation only through the use of Stored Procs will
> never
> happen. The only way I know to be able to avoid this is to use Application
> Roles, but I wanted to see if anyone else had any other ideas, and what
> others were doing to remedy this?
> TIA, ChrisR|||Hi
I think that Chris' problem is that the design of his application means that
authenticated user require a wide range of authorization, including access to
the underlying tables. If authorization was solely restriced on Windows
Accounts or Groups then there would be no way to stop users bypassing his
applications and using other tools to manipulate the data. The application
role will make it harder for users to do this.
Another method would be to use views and have a security check (say a join
to his users table to check a 'IsCurrentlyLoggedIn' flag), but that would not
be very secure even if the views were encrypted.
John
"Norman Yuan" wrote:
> You are confusing the authentication and authorization. In SQL Server, using
> Windows security means SQL Server authenticate user access by their windows
> account (so no need to pass username/password since the user has logged on
> to his computer with his windows user account and he has been authenticated
> on who he is. However, just knowing who he is, does not mean you have to
> give all accesses to him. He still can be denied any access at all, or is
> given only necessary accesses.
> Common and simple approach is create one or more windows user group. In SQL
> Server, you assign different access permission to different groups. Say,
> Group1 has all access permission, Group2 only hass "SELECT" permission on
> Table1. Then you add userA and UseB to Group1, and allother users to
> Group2... and so on. Very simple and easy to manage. You do not give every
> windows user account to all access permission to the SQL Server.
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
> >I asked the question last week about if there would ever be any benefit to
> > SQL authentication over WINNT (if I had the option of using either one)
> > and
> > the consensus seemed to be "no". Thanks to those that replied, but now I
> > have
> > another question. I currently have 2 environments. Our "legacy
> > "environment
> > is opened up wide security wise. Way to loose. Lots of folks have
> > excessive
> > permissions in our SQL Servers. Our new environment is very tightly
> > controlled. We only use SQL authentication for DB connections, and very
> > few
> > people know those passwords. There is a proposal to start using WINNT
> > Auth,
> > but here is the problem that I see. Currently, no end users can make
> > direct
> > connections to the DB as they do not know the password to the SQL logins.
> > Essentially they have to use the app provided. But if I suddenly start
> > using
> > WINNT Auth, each end user will then be able to make a direct connection to
> > the DB using Enterprise Manager, Access, whatever they want. Many of our
> > Apps
> > are written to require direct Read/ Write access to our tables, so being
> > able
> > to enforce data manipulation only through the use of Stored Procs will
> > never
> > happen. The only way I know to be able to avoid this is to use Application
> > Roles, but I wanted to see if anyone else had any other ideas, and what
> > others were doing to remedy this?
> >
> > TIA, ChrisR
>
>|||Yes, this is the dilema. As you referenced, I only want folks to use certain
apps, not whatever they want. I personally see this as a big problem with
using WINNT authentication.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D9AF704D-4D60-41C2-A789-1631DEB51B14@.microsoft.com...
> Hi
> I think that Chris' problem is that the design of his application means
that
> authenticated user require a wide range of authorization, including access
to
> the underlying tables. If authorization was solely restriced on Windows
> Accounts or Groups then there would be no way to stop users bypassing his
> applications and using other tools to manipulate the data. The application
> role will make it harder for users to do this.
> Another method would be to use views and have a security check (say a join
> to his users table to check a 'IsCurrentlyLoggedIn' flag), but that would
not
> be very secure even if the views were encrypted.
>
> John
> "Norman Yuan" wrote:
> > You are confusing the authentication and authorization. In SQL Server,
using
> > Windows security means SQL Server authenticate user access by their
windows
> > account (so no need to pass username/password since the user has logged
on
> > to his computer with his windows user account and he has been
authenticated
> > on who he is. However, just knowing who he is, does not mean you have to
> > give all accesses to him. He still can be denied any access at all, or
is
> > given only necessary accesses.
> >
> > Common and simple approach is create one or more windows user group. In
SQL
> > Server, you assign different access permission to different groups. Say,
> > Group1 has all access permission, Group2 only hass "SELECT" permission
on
> > Table1. Then you add userA and UseB to Group1, and allother users to
> > Group2... and so on. Very simple and easy to manage. You do not give
every
> > windows user account to all access permission to the SQL Server.
> >
> >
> > "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> > news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
> > >I asked the question last week about if there would ever be any benefit
to
> > > SQL authentication over WINNT (if I had the option of using either
one)
> > > and
> > > the consensus seemed to be "no". Thanks to those that replied, but now
I
> > > have
> > > another question. I currently have 2 environments. Our "legacy
> > > "environment
> > > is opened up wide security wise. Way to loose. Lots of folks have
> > > excessive
> > > permissions in our SQL Servers. Our new environment is very tightly
> > > controlled. We only use SQL authentication for DB connections, and
very
> > > few
> > > people know those passwords. There is a proposal to start using WINNT
> > > Auth,
> > > but here is the problem that I see. Currently, no end users can make
> > > direct
> > > connections to the DB as they do not know the password to the SQL
logins.
> > > Essentially they have to use the app provided. But if I suddenly start
> > > using
> > > WINNT Auth, each end user will then be able to make a direct
connection to
> > > the DB using Enterprise Manager, Access, whatever they want. Many of
our
> > > Apps
> > > are written to require direct Read/ Write access to our tables, so
being
> > > able
> > > to enforce data manipulation only through the use of Stored Procs will
> > > never
> > > happen. The only way I know to be able to avoid this is to use
Application
> > > Roles, but I wanted to see if anyone else had any other ideas, and
what
> > > others were doing to remedy this?
> > >
> > > TIA, ChrisR
> >
> >
> >|||Hi Chris
I would argue that the way your system has been designed that causes your
problems and not Windows Authentication per se. Using Windows Authentication
provides better security and management. By using shared logins these
benefits are lost along with auditability. You can still use Windows
Authentication along with the application role(s), but you will not get the
auditability.
John
"ChrisR" wrote:
> Yes, this is the dilema. As you referenced, I only want folks to use certain
> apps, not whatever they want. I personally see this as a big problem with
> using WINNT authentication.
> Thanks.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D9AF704D-4D60-41C2-A789-1631DEB51B14@.microsoft.com...
> > Hi
> >
> > I think that Chris' problem is that the design of his application means
> that
> > authenticated user require a wide range of authorization, including access
> to
> > the underlying tables. If authorization was solely restriced on Windows
> > Accounts or Groups then there would be no way to stop users bypassing his
> > applications and using other tools to manipulate the data. The application
> > role will make it harder for users to do this.
> >
> > Another method would be to use views and have a security check (say a join
> > to his users table to check a 'IsCurrentlyLoggedIn' flag), but that would
> not
> > be very secure even if the views were encrypted.
> >
> >
> > John
> >
> > "Norman Yuan" wrote:
> >
> > > You are confusing the authentication and authorization. In SQL Server,
> using
> > > Windows security means SQL Server authenticate user access by their
> windows
> > > account (so no need to pass username/password since the user has logged
> on
> > > to his computer with his windows user account and he has been
> authenticated
> > > on who he is. However, just knowing who he is, does not mean you have to
> > > give all accesses to him. He still can be denied any access at all, or
> is
> > > given only necessary accesses.
> > >
> > > Common and simple approach is create one or more windows user group. In
> SQL
> > > Server, you assign different access permission to different groups. Say,
> > > Group1 has all access permission, Group2 only hass "SELECT" permission
> on
> > > Table1. Then you add userA and UseB to Group1, and allother users to
> > > Group2... and so on. Very simple and easy to manage. You do not give
> every
> > > windows user account to all access permission to the SQL Server.
> > >
> > >
> > > "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> > > news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
> > > >I asked the question last week about if there would ever be any benefit
> to
> > > > SQL authentication over WINNT (if I had the option of using either
> one)
> > > > and
> > > > the consensus seemed to be "no". Thanks to those that replied, but now
> I
> > > > have
> > > > another question. I currently have 2 environments. Our "legacy
> > > > "environment
> > > > is opened up wide security wise. Way to loose. Lots of folks have
> > > > excessive
> > > > permissions in our SQL Servers. Our new environment is very tightly
> > > > controlled. We only use SQL authentication for DB connections, and
> very
> > > > few
> > > > people know those passwords. There is a proposal to start using WINNT
> > > > Auth,
> > > > but here is the problem that I see. Currently, no end users can make
> > > > direct
> > > > connections to the DB as they do not know the password to the SQL
> logins.
> > > > Essentially they have to use the app provided. But if I suddenly start
> > > > using
> > > > WINNT Auth, each end user will then be able to make a direct
> connection to
> > > > the DB using Enterprise Manager, Access, whatever they want. Many of
> our
> > > > Apps
> > > > are written to require direct Read/ Write access to our tables, so
> being
> > > > able
> > > > to enforce data manipulation only through the use of Stored Procs will
> > > > never
> > > > happen. The only way I know to be able to avoid this is to use
> Application
> > > > Roles, but I wanted to see if anyone else had any other ideas, and
> what
> > > > others were doing to remedy this?
> > > >
> > > > TIA, ChrisR
> > >
> > >
> > >
>
>|||> You can still use Windows
> Authentication along with the application role(s), but you will not get the
> auditability.
Not is user names are audited, but if logins names are audited, then nothing is lost. Or am I
missing something?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86706F06-7B2C-4CA7-A062-4CB6AD28B82A@.microsoft.com...
> Hi Chris
> I would argue that the way your system has been designed that causes your
> problems and not Windows Authentication per se. Using Windows Authentication
> provides better security and management. By using shared logins these
> benefits are lost along with auditability. You can still use Windows
> Authentication along with the application role(s), but you will not get the
> auditability.
> John
> "ChrisR" wrote:
>> Yes, this is the dilema. As you referenced, I only want folks to use certain
>> apps, not whatever they want. I personally see this as a big problem with
>> using WINNT authentication.
>> Thanks.
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:D9AF704D-4D60-41C2-A789-1631DEB51B14@.microsoft.com...
>> > Hi
>> >
>> > I think that Chris' problem is that the design of his application means
>> that
>> > authenticated user require a wide range of authorization, including access
>> to
>> > the underlying tables. If authorization was solely restriced on Windows
>> > Accounts or Groups then there would be no way to stop users bypassing his
>> > applications and using other tools to manipulate the data. The application
>> > role will make it harder for users to do this.
>> >
>> > Another method would be to use views and have a security check (say a join
>> > to his users table to check a 'IsCurrentlyLoggedIn' flag), but that would
>> not
>> > be very secure even if the views were encrypted.
>> >
>> >
>> > John
>> >
>> > "Norman Yuan" wrote:
>> >
>> > > You are confusing the authentication and authorization. In SQL Server,
>> using
>> > > Windows security means SQL Server authenticate user access by their
>> windows
>> > > account (so no need to pass username/password since the user has logged
>> on
>> > > to his computer with his windows user account and he has been
>> authenticated
>> > > on who he is. However, just knowing who he is, does not mean you have to
>> > > give all accesses to him. He still can be denied any access at all, or
>> is
>> > > given only necessary accesses.
>> > >
>> > > Common and simple approach is create one or more windows user group. In
>> SQL
>> > > Server, you assign different access permission to different groups. Say,
>> > > Group1 has all access permission, Group2 only hass "SELECT" permission
>> on
>> > > Table1. Then you add userA and UseB to Group1, and allother users to
>> > > Group2... and so on. Very simple and easy to manage. You do not give
>> every
>> > > windows user account to all access permission to the SQL Server.
>> > >
>> > >
>> > > "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> > > news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
>> > > >I asked the question last week about if there would ever be any benefit
>> to
>> > > > SQL authentication over WINNT (if I had the option of using either
>> one)
>> > > > and
>> > > > the consensus seemed to be "no". Thanks to those that replied, but now
>> I
>> > > > have
>> > > > another question. I currently have 2 environments. Our "legacy
>> > > > "environment
>> > > > is opened up wide security wise. Way to loose. Lots of folks have
>> > > > excessive
>> > > > permissions in our SQL Servers. Our new environment is very tightly
>> > > > controlled. We only use SQL authentication for DB connections, and
>> very
>> > > > few
>> > > > people know those passwords. There is a proposal to start using WINNT
>> > > > Auth,
>> > > > but here is the problem that I see. Currently, no end users can make
>> > > > direct
>> > > > connections to the DB as they do not know the password to the SQL
>> logins.
>> > > > Essentially they have to use the app provided. But if I suddenly start
>> > > > using
>> > > > WINNT Auth, each end user will then be able to make a direct
>> connection to
>> > > > the DB using Enterprise Manager, Access, whatever they want. Many of
>> our
>> > > > Apps
>> > > > are written to require direct Read/ Write access to our tables, so
>> being
>> > > > able
>> > > > to enforce data manipulation only through the use of Stored Procs will
>> > > > never
>> > > > happen. The only way I know to be able to avoid this is to use
>> Application
>> > > > Roles, but I wanted to see if anyone else had any other ideas, and
>> what
>> > > > others were doing to remedy this?
>> > > >
>> > > > TIA, ChrisR
>> > >
>> > >
>> > >
>>|||Hi Tibor
No, you are correct! Using SYSTEM_USER would give them more information than
they could have currently with the shared SQL Server logins (unless they
share the windows logins as well!!)
John
"Tibor Karaszi" wrote:
> > You can still use Windows
> > Authentication along with the application role(s), but you will not get the
> > auditability.
> Not is user names are audited, but if logins names are audited, then nothing is lost. Or am I
> missing something?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:86706F06-7B2C-4CA7-A062-4CB6AD28B82A@.microsoft.com...
> > Hi Chris
> >
> > I would argue that the way your system has been designed that causes your
> > problems and not Windows Authentication per se. Using Windows Authentication
> > provides better security and management. By using shared logins these
> > benefits are lost along with auditability. You can still use Windows
> > Authentication along with the application role(s), but you will not get the
> > auditability.
> >
> > John
> >
> > "ChrisR" wrote:
> >
> >> Yes, this is the dilema. As you referenced, I only want folks to use certain
> >> apps, not whatever they want. I personally see this as a big problem with
> >> using WINNT authentication.
> >>
> >> Thanks.
> >>
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:D9AF704D-4D60-41C2-A789-1631DEB51B14@.microsoft.com...
> >> > Hi
> >> >
> >> > I think that Chris' problem is that the design of his application means
> >> that
> >> > authenticated user require a wide range of authorization, including access
> >> to
> >> > the underlying tables. If authorization was solely restriced on Windows
> >> > Accounts or Groups then there would be no way to stop users bypassing his
> >> > applications and using other tools to manipulate the data. The application
> >> > role will make it harder for users to do this.
> >> >
> >> > Another method would be to use views and have a security check (say a join
> >> > to his users table to check a 'IsCurrentlyLoggedIn' flag), but that would
> >> not
> >> > be very secure even if the views were encrypted.
> >> >
> >> >
> >> > John
> >> >
> >> > "Norman Yuan" wrote:
> >> >
> >> > > You are confusing the authentication and authorization. In SQL Server,
> >> using
> >> > > Windows security means SQL Server authenticate user access by their
> >> windows
> >> > > account (so no need to pass username/password since the user has logged
> >> on
> >> > > to his computer with his windows user account and he has been
> >> authenticated
> >> > > on who he is. However, just knowing who he is, does not mean you have to
> >> > > give all accesses to him. He still can be denied any access at all, or
> >> is
> >> > > given only necessary accesses.
> >> > >
> >> > > Common and simple approach is create one or more windows user group. In
> >> SQL
> >> > > Server, you assign different access permission to different groups. Say,
> >> > > Group1 has all access permission, Group2 only hass "SELECT" permission
> >> on
> >> > > Table1. Then you add userA and UseB to Group1, and allother users to
> >> > > Group2... and so on. Very simple and easy to manage. You do not give
> >> every
> >> > > windows user account to all access permission to the SQL Server.
> >> > >
> >> > >
> >> > > "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> >> > > news:F55EADA6-BAE8-4329-9007-06E1D1FF34AC@.microsoft.com...
> >> > > >I asked the question last week about if there would ever be any benefit
> >> to
> >> > > > SQL authentication over WINNT (if I had the option of using either
> >> one)
> >> > > > and
> >> > > > the consensus seemed to be "no". Thanks to those that replied, but now
> >> I
> >> > > > have
> >> > > > another question. I currently have 2 environments. Our "legacy
> >> > > > "environment
> >> > > > is opened up wide security wise. Way to loose. Lots of folks have
> >> > > > excessive
> >> > > > permissions in our SQL Servers. Our new environment is very tightly
> >> > > > controlled. We only use SQL authentication for DB connections, and
> >> very
> >> > > > few
> >> > > > people know those passwords. There is a proposal to start using WINNT
> >> > > > Auth,
> >> > > > but here is the problem that I see. Currently, no end users can make
> >> > > > direct
> >> > > > connections to the DB as they do not know the password to the SQL
> >> logins.
> >> > > > Essentially they have to use the app provided. But if I suddenly start
> >> > > > using
> >> > > > WINNT Auth, each end user will then be able to make a direct
> >> connection to
> >> > > > the DB using Enterprise Manager, Access, whatever they want. Many of
> >> our
> >> > > > Apps
> >> > > > are written to require direct Read/ Write access to our tables, so
> >> being
> >> > > > able
> >> > > > to enforce data manipulation only through the use of Stored Procs will
> >> > > > never
> >> > > > happen. The only way I know to be able to avoid this is to use
> >> Application
> >> > > > Roles, but I wanted to see if anyone else had any other ideas, and
> >> what
> >> > > > others were doing to remedy this?
> >> > > >
> >> > > > TIA, ChrisR
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>

Benefit of multiple databases?

Hi !

We're designing our data model, and have found that we have two groups of tables (about 10 tables in each group). The tables within each group are dependent, but the two groups are independent of eachother.

Now, our two choices are:

1. Put all tables into one database.

2. Put the two groups into two separate databases.

For simplicity, option 1 is the winner. However, my question is, will there be noticeable peformance benefits by using two databases? (In which case, option 2 will be the winner).

Thanks,

Martin

There will be a memory overhead with have connections open to two databases, I would recommend using just a single SDF file for all your tables.

Benefit of moving Master & TempDB to diff HD

I am interested to hear if people think it would be a good idea to move
the Master & TempDB to a different HD.

Here is my DB Server's set up:
1. Processor: (1) AMD XP 2800
2. 1st HD (IDE 0) is the system & boot drive
3. (3) SCSI HD make up a hardware RAID level 0 (striped without
parity)solution - these striped drives are just for my working DBs
4. (1) SCSI HD that's not doing anything.

I want to put the Master & TempDB on the SCSI HD that's not doing
anything. Would that be the best place for it for maximum performance or
should I put in the striped array. I am leaning more towards putting on
the SCSI HD that's not doing anything. What do you all think?

Ed

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!adude (nospam@.devdex.com) writes:
> I am interested to hear if people think it would be a good idea to move
> the Master & TempDB to a different HD.
> Here is my DB Server's set up:
> 1. Processor: (1) AMD XP 2800
> 2. 1st HD (IDE 0) is the system & boot drive
> 3. (3) SCSI HD make up a hardware RAID level 0 (striped without
> parity)solution - these striped drives are just for my working DBs
> 4. (1) SCSI HD that's not doing anything.
> I want to put the Master & TempDB on the SCSI HD that's not doing
> anything. Would that be the best place for it for maximum performance or
> should I put in the striped array. I am leaning more towards putting on
> the SCSI HD that's not doing anything. What do you all think?

I would not move master.

If that idle HD is on a different controller, moving something could be
good for performance. If you have lots of action in tempdb, this could
be a candidate. You could also consider moving transaction logs to the
idle disc.

If the disk in the same controller as the rest, I think it would be better
to move disk into the stripe. If the IDE disk is slow, you couls still
move tempdn into the RAID.

I need to add the disclaimer that hardware configuration is not my best
game.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp