Hi
When exporting a database from one server to another is
it then somehow possible to make sure that both the users
in the database and the server logins are being exported
too.
It seems that only the users in the database are being
exported. I know that you afterwards can use sp_addlogin
and sp_change_users_login. But this will force one to
change the existing passwords.
TIA
KlausI suggest you search KB for sp_help_revlogins.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:067301c49188$fe271350$a401280a@.phx.gbl...
> Hi
> When exporting a database from one server to another is
> it then somehow possible to make sure that both the users
> in the database and the server logins are being exported
> too.
> It seems that only the users in the database are being
> exported. I know that you afterwards can use sp_addlogin
> and sp_change_users_login. But this will force one to
> change the existing passwords.
> TIA
> Klaus|||Klaus
Here a links to a lot of articles which will give you help when moving
databases between servers.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
Hope this helps
John|||Thanks for your answer, Tibor.
This may be a stupid question but what is KB. The online
books don't seem to say anything about sp_help_revlogins.
-- Klaus
>--Original Message--
>I suggest you search KB for sp_help_revlogins.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Klaus" <anonymous@.discussions.microsoft.com> wrote in
message
>news:067301c49188$fe271350$a401280a@.phx.gbl...
>> Hi
>> When exporting a database from one server to another is
>> it then somehow possible to make sure that both the
users
>> in the database and the server logins are being
exported
>> too.
>> It seems that only the users in the database are being
>> exported. I know that you afterwards can use
sp_addlogin
>> and sp_change_users_login. But this will force one to
>> change the existing passwords.
>> TIA
>> Klaus
>
>.
>|||KB = Knowledge Base.
Collection of 'How it works' technical descriptions on the
Microsoft web site.
Peter
"Cauliflower is nothing but cabbage with a college
education."
Mark Twain
>--Original Message--
>Thanks for your answer, Tibor.
>This may be a stupid question but what is KB. The online
>books don't seem to say anything about sp_help_revlogins.
>-- Klaus
>
>>--Original Message--
>>I suggest you search KB for sp_help_revlogins.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Klaus" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:067301c49188$fe271350$a401280a@.phx.gbl...
>> Hi
>> When exporting a database from one server to another is
>> it then somehow possible to make sure that both the
>users
>> in the database and the server logins are being
>exported
>> too.
>> It seems that only the users in the database are being
>> exported. I know that you afterwards can use
>sp_addlogin
>> and sp_change_users_login. But this will force one to
>> change the existing passwords.
>> TIA
>> Klaus
>>
>>.
>.
>
Showing posts with label users. Show all posts
Showing posts with label users. Show all posts
Thursday, March 22, 2012
Monday, March 19, 2012
Database Encryption - Employee performance review
Hi,
I am writing an .NET 2.0 app that has different users logging in who
have different access levels in the app. One of the user roles is "HR".
When the user logs in with these credentials, they have a whole heap of
buttons/links/pages related to HR stuff that normal team members don't.
One of the sensitive information that needs to be displayed and more
importantly, stored in the database is the employee performance reviews
(HR access only).
I am wondering how I would go about storing/maintaining this
information as even the DBA's are not supposed to have access to this
information, but should still be able to administer it and/or retrieve
the encryption keys if the HR personnel forget their passwords etc.
The simplest way I can think of is to issue all the HR people with one
password, encrypt the data in the DB layer (in code) and then store it
in a table in the database. This way, anyone with access to the
Database won't necessarily have access to the data. The problem with
this one is that all the HR personnel share one password, which doesn't
seem like a nice (secure) solution.
The other option was to store the encryption key in the database and
encrypt the key itself with the password provided by the individual
user (HR person), which will be stored against that user's record. This
way, whenever a data request is made, the database sends the encrypted
performance review data and the password encrypted key to the user. The
user will then decrypt the key using their password, and then decrypt
the data using the key. This way, if the user ever forgets their
password, all the DBA (and/or App with owner acess) has to do is
reencrypt the key with a new user password and store it against the
user's record. The problem with this is storing the key in the database
and yet restricting access to the DBA. They should not have raw access
to the key (which essentially means they can see the data in the
database), but still should have enough access that if a user forgets
their password, the DBA can reset the password without losing all the
existing data.
I was thinking of storing the key on a key server somewhere with
different access rights etc. but that means the system starts to get
complex.
How would you guys go about building such a system? Does MS Sql Server
2005 provide any mechanisms for such functionality? Plus, as a design
issue, is it better to let the database handle the encryption, key
management, roles/privileges or is it better to do it in the
application itself? One of the advantages I can think of for doing it
in the application is that it gives a bit of database independence, i
can change the underlying database easily without having to rewrite a
whole heap of functionality. The other reason is that this way, i won't
have unencrypted data or keys on wire.
Looking forward to some expert opinions and discussions regarding this
problem.
Thanks in advance.Hello? Anyone? Surely this is not such a hard problem, or particularly
unusual. I would guess there are many times when you would want to
encrypt the data in a database, so as to make it inaccessible to the
DBA's, but still give them some administrative privileges. One of the
constraints is that the user with access to the data doesn't
necessarily "own" the data, so that if they forget the password (i.e
lose the encryption key), all the data is not lost.
On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> Hi,
> I am writing an .NET 2.0 app that has different users logging in who
> have different access levels in the app. One of the user roles is "HR".
> When the user logs in with these credentials, they have a whole heap of
> buttons/links/pages related to HR stuff that normal team members don't.
> One of the sensitive information that needs to be displayed and more
> importantly, stored in the database is the employee performance reviews
> (HR access only).
> I am wondering how I would go about storing/maintaining this
> information as even the DBA's are not supposed to have access to this
> information, but should still be able to administer it and/or retrieve
> the encryption keys if the HR personnel forget their passwords etc.
> The simplest way I can think of is to issue all the HR people with one
> password, encrypt the data in the DB layer (in code) and then store it
> in a table in the database. This way, anyone with access to the
> Database won't necessarily have access to the data. The problem with
> this one is that all the HR personnel share one password, which doesn't
> seem like a nice (secure) solution.
> The other option was to store the encryption key in the database and
> encrypt the key itself with the password provided by the individual
> user (HR person), which will be stored against that user's record. This
> way, whenever a data request is made, the database sends the encrypted
> performance review data and the password encrypted key to the user. The
> user will then decrypt the key using their password, and then decrypt
> the data using the key. This way, if the user ever forgets their
> password, all the DBA (and/or App with owner acess) has to do is
> reencrypt the key with a new user password and store it against the
> user's record. The problem with this is storing the key in the database
> and yet restricting access to the DBA. They should not have raw access
> to the key (which essentially means they can see the data in the
> database), but still should have enough access that if a user forgets
> their password, the DBA can reset the password without losing all the
> existing data.
> I was thinking of storing the key on a key server somewhere with
> different access rights etc. but that means the system starts to get
> complex.
> How would you guys go about building such a system? Does MS Sql Server
> 2005 provide any mechanisms for such functionality? Plus, as a design
> issue, is it better to let the database handle the encryption, key
> management, roles/privileges or is it better to do it in the
> application itself? One of the advantages I can think of for doing it
> in the application is that it gives a bit of database independence, i
> can change the underlying database easily without having to rewrite a
> whole heap of functionality. The other reason is that this way, i won't
> have unencrypted data or keys on wire.
> Looking forward to some expert opinions and discussions regarding this
> problem.
> Thanks in advance.|||The key (ahem) here is this:
>> Whoever has access to the decryption key has access to the data. <<
Plain and simple. If the DBAs can read the decryption key, they can
read the data. So the statement "but should still be able to administer
it and/or retrieve the encryption keys if the HR personnel forget their
passwords etc." is what I call a "non-starter". By definition, the DBAs
have access to the data.
You're right on one point though. If all people share access to the
same password or decryption key, you have an insecure solution. Think
of it this way -- the bigger the "secret" is, and the more people who
have access to that secret, the less secure the system.
Encrypting the key doesn't work either because you have to encrypt the
key with another key. Where are you going to put that key? Are the
DBAs going to have access to it too?
Here's my take on it:
Carefully analyze what is and what is not a secret in the system. Only
encrypt the real secrets. Limit the access to the database to the
smallest set of people possible. If you don't want the DBAs to have
access to the encryption key, that by definition means you have to store
the key outside the database. I recommend that you use .NET's
encryption routines to encrypt your secrets *outside* of the database,
and simply store the data in the system once it's encrypted.
So it boils down to this. SOMEBODY needs access to the keys. They need
to exist somewhere (even if it's not on your SQL Server). The
administrator of that machine will probably be able to read the key.
Deny that person read access to the database. That person needs to be
trustworthy, too. If he/she is not, they can share the key with an
untrustworthy DBA and get your data by writing a small program.
HTH
-Dave
nyathancha@.hotmail.com wrote:
> Hello? Anyone? Surely this is not such a hard problem, or particularly
> unusual. I would guess there are many times when you would want to
> encrypt the data in a database, so as to make it inaccessible to the
> DBA's, but still give them some administrative privileges. One of the
> constraints is that the user with access to the data doesn't
> necessarily "own" the data, so that if they forget the password (i.e
> lose the encryption key), all the data is not lost.
>
>
>
> On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
>> Hi,
>> I am writing an .NET 2.0 app that has different users logging in who
>> have different access levels in the app. One of the user roles is "HR".
>> When the user logs in with these credentials, they have a whole heap of
>> buttons/links/pages related to HR stuff that normal team members don't.
>> One of the sensitive information that needs to be displayed and more
>> importantly, stored in the database is the employee performance reviews
>> (HR access only).
>> I am wondering how I would go about storing/maintaining this
>> information as even the DBA's are not supposed to have access to this
>> information, but should still be able to administer it and/or retrieve
>> the encryption keys if the HR personnel forget their passwords etc.
>> The simplest way I can think of is to issue all the HR people with one
>> password, encrypt the data in the DB layer (in code) and then store it
>> in a table in the database. This way, anyone with access to the
>> Database won't necessarily have access to the data. The problem with
>> this one is that all the HR personnel share one password, which doesn't
>> seem like a nice (secure) solution.
>> The other option was to store the encryption key in the database and
>> encrypt the key itself with the password provided by the individual
>> user (HR person), which will be stored against that user's record. This
>> way, whenever a data request is made, the database sends the encrypted
>> performance review data and the password encrypted key to the user. The
>> user will then decrypt the key using their password, and then decrypt
>> the data using the key. This way, if the user ever forgets their
>> password, all the DBA (and/or App with owner acess) has to do is
>> reencrypt the key with a new user password and store it against the
>> user's record. The problem with this is storing the key in the database
>> and yet restricting access to the DBA. They should not have raw access
>> to the key (which essentially means they can see the data in the
>> database), but still should have enough access that if a user forgets
>> their password, the DBA can reset the password without losing all the
>> existing data.
>> I was thinking of storing the key on a key server somewhere with
>> different access rights etc. but that means the system starts to get
>> complex.
>> How would you guys go about building such a system? Does MS Sql Server
>> 2005 provide any mechanisms for such functionality? Plus, as a design
>> issue, is it better to let the database handle the encryption, key
>> management, roles/privileges or is it better to do it in the
>> application itself? One of the advantages I can think of for doing it
>> in the application is that it gives a bit of database independence, i
>> can change the underlying database easily without having to rewrite a
>> whole heap of functionality. The other reason is that this way, i won't
>> have unencrypted data or keys on wire.
>> Looking forward to some expert opinions and discussions regarding this
>> problem.
>> Thanks in advance.
>
-Dave Markle
http://www.markleconsulting.com/blog|||Hi Dave, Thanks for the reply.
I was thinking along the same lines as well. I couldn't think of anyway
of storing the key in the database and still restricting access to the
DBA. Which is why I mentioned in my original post the "key server
somewhere with
different access rights" . Unless sql server has some magic
features/functionality that I don't know about.
So how hard would it be to build/deploy a key server? Maybe this is
more of an ecryption/security question rather than a database question.
My idea of encrypting the key with another key was that the second
"key" would be the user's password. This way the DBA doesn't have
access to the original data encryption key even if its stored in the
database (because it can only be decrypted using the user's password),
but if the user does forget their password, the data is not lost
because it wasn't encrypted with their password(/key). The DBA can
"reset" their password by reencrypting the orginal data key with a new
user password key and storing it against the column. Which is where I
came to the key server. The fact that the DBA should be able to get the
data key encrypted with a user password, but not the raw data key
itself. I can't think of anyway of doing this without an independent
application.
Does anyone know any commercial encryption key storage and distribution
solutions/products?
I am sure at some stage the data encryption key will itself have to be
encrypted before being put on the wire. Other wise you would have the
encrypted data coming from the database, the plain encryption key
coming from the key server. Doesn't seem particularly secure.
What is the general industry solution to a situation like this, where
you want to store sensitive data in a database, but the data is
accessible (i.e belongs to) to more than one person (so that if that
one person forgets their password/key, the data itself is not lost)
On Jan 25, 1:28 pm, Dave Markle <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com>
wrote:
> The key (ahem) here is this:
> >> Whoever has access to the decryption key has access to the data. <<
> Plain and simple. If the DBAs can read the decryption key, they can
> read the data. So the statement "but should still be able to administer
> it and/or retrieve the encryption keys if the HR personnel forget their
> passwords etc." is what I call a "non-starter". By definition, the DBAs
> have access to the data.
> You're right on one point though. If all people share access to the
> same password or decryption key, you have an insecure solution. Think
> of it this way -- the bigger the "secret" is, and the more people who
> have access to that secret, the less secure the system.
> Encrypting the key doesn't work either because you have to encrypt the
> key with another key. Where are you going to put that key? Are the
> DBAs going to have access to it too?
> Here's my take on it:
> Carefully analyze what is and what is not a secret in the system. Only
> encrypt the real secrets. Limit the access to the database to the
> smallest set of people possible. If you don't want the DBAs to have
> access to the encryption key, that by definition means you have to store
> the key outside the database. I recommend that you use .NET's
> encryption routines to encrypt your secrets *outside* of the database,
> and simply store the data in the system once it's encrypted.
> So it boils down to this. SOMEBODY needs access to the keys. They need
> to exist somewhere (even if it's not on your SQL Server). The
> administrator of that machine will probably be able to read the key.
> Deny that person read access to the database. That person needs to be
> trustworthy, too. If he/she is not, they can share the key with an
> untrustworthy DBA and get your data by writing a small program.
> HTH
> -Dave
>
> nyathan...@.hotmail.com wrote:
> > Hello? Anyone? Surely this is not such a hard problem, or particularly
> > unusual. I would guess there are many times when you would want to
> > encrypt the data in a database, so as to make it inaccessible to the
> > DBA's, but still give them some administrative privileges. One of the
> > constraints is that the user with access to the data doesn't
> > necessarily "own" the data, so that if they forget the password (i.e
> > lose the encryption key), all the data is not lost.
> > On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> >> Hi,
> >> I am writing an .NET 2.0 app that has different users logging in who
> >> have different access levels in the app. One of the user roles is "HR".
> >> When the user logs in with these credentials, they have a whole heap of
> >> buttons/links/pages related to HR stuff that normal team members don't.
> >> One of the sensitive information that needs to be displayed and more
> >> importantly, stored in the database is the employee performance reviews
> >> (HR access only).
> >> I am wondering how I would go about storing/maintaining this
> >> information as even the DBA's are not supposed to have access to this
> >> information, but should still be able to administer it and/or retrieve
> >> the encryption keys if the HR personnel forget their passwords etc.
> >> The simplest way I can think of is to issue all the HR people with one
> >> password, encrypt the data in the DB layer (in code) and then store it
> >> in a table in the database. This way, anyone with access to the
> >> Database won't necessarily have access to the data. The problem with
> >> this one is that all the HR personnel share one password, which doesn't
> >> seem like a nice (secure) solution.
> >> The other option was to store the encryption key in the database and
> >> encrypt the key itself with the password provided by the individual
> >> user (HR person), which will be stored against that user's record. This
> >> way, whenever a data request is made, the database sends the encrypted
> >> performance review data and the password encrypted key to the user. The
> >> user will then decrypt the key using their password, and then decrypt
> >> the data using the key. This way, if the user ever forgets their
> >> password, all the DBA (and/or App with owner acess) has to do is
> >> reencrypt the key with a new user password and store it against the
> >> user's record. The problem with this is storing the key in the database
> >> and yet restricting access to the DBA. They should not have raw access
> >> to the key (which essentially means they can see the data in the
> >> database), but still should have enough access that if a user forgets
> >> their password, the DBA can reset the password without losing all the
> >> existing data.
> >> I was thinking of storing the key on a key server somewhere with
> >> different access rights etc. but that means the system starts to get
> >> complex.
> >> How would you guys go about building such a system? Does MS Sql Server
> >> 2005 provide any mechanisms for such functionality? Plus, as a design
> >> issue, is it better to let the database handle the encryption, key
> >> management, roles/privileges or is it better to do it in the
> >> application itself? One of the advantages I can think of for doing it
> >> in the application is that it gives a bit of database independence, i
> >> can change the underlying database easily without having to rewrite a
> >> whole heap of functionality. The other reason is that this way, i won't
> >> have unencrypted data or keys on wire.
> >> Looking forward to some expert opinions and discussions regarding this
> >> problem.
> >> Thanks in advance.--
> -Dave Markle
> http://www.markleconsulting.com/blog
I am writing an .NET 2.0 app that has different users logging in who
have different access levels in the app. One of the user roles is "HR".
When the user logs in with these credentials, they have a whole heap of
buttons/links/pages related to HR stuff that normal team members don't.
One of the sensitive information that needs to be displayed and more
importantly, stored in the database is the employee performance reviews
(HR access only).
I am wondering how I would go about storing/maintaining this
information as even the DBA's are not supposed to have access to this
information, but should still be able to administer it and/or retrieve
the encryption keys if the HR personnel forget their passwords etc.
The simplest way I can think of is to issue all the HR people with one
password, encrypt the data in the DB layer (in code) and then store it
in a table in the database. This way, anyone with access to the
Database won't necessarily have access to the data. The problem with
this one is that all the HR personnel share one password, which doesn't
seem like a nice (secure) solution.
The other option was to store the encryption key in the database and
encrypt the key itself with the password provided by the individual
user (HR person), which will be stored against that user's record. This
way, whenever a data request is made, the database sends the encrypted
performance review data and the password encrypted key to the user. The
user will then decrypt the key using their password, and then decrypt
the data using the key. This way, if the user ever forgets their
password, all the DBA (and/or App with owner acess) has to do is
reencrypt the key with a new user password and store it against the
user's record. The problem with this is storing the key in the database
and yet restricting access to the DBA. They should not have raw access
to the key (which essentially means they can see the data in the
database), but still should have enough access that if a user forgets
their password, the DBA can reset the password without losing all the
existing data.
I was thinking of storing the key on a key server somewhere with
different access rights etc. but that means the system starts to get
complex.
How would you guys go about building such a system? Does MS Sql Server
2005 provide any mechanisms for such functionality? Plus, as a design
issue, is it better to let the database handle the encryption, key
management, roles/privileges or is it better to do it in the
application itself? One of the advantages I can think of for doing it
in the application is that it gives a bit of database independence, i
can change the underlying database easily without having to rewrite a
whole heap of functionality. The other reason is that this way, i won't
have unencrypted data or keys on wire.
Looking forward to some expert opinions and discussions regarding this
problem.
Thanks in advance.Hello? Anyone? Surely this is not such a hard problem, or particularly
unusual. I would guess there are many times when you would want to
encrypt the data in a database, so as to make it inaccessible to the
DBA's, but still give them some administrative privileges. One of the
constraints is that the user with access to the data doesn't
necessarily "own" the data, so that if they forget the password (i.e
lose the encryption key), all the data is not lost.
On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> Hi,
> I am writing an .NET 2.0 app that has different users logging in who
> have different access levels in the app. One of the user roles is "HR".
> When the user logs in with these credentials, they have a whole heap of
> buttons/links/pages related to HR stuff that normal team members don't.
> One of the sensitive information that needs to be displayed and more
> importantly, stored in the database is the employee performance reviews
> (HR access only).
> I am wondering how I would go about storing/maintaining this
> information as even the DBA's are not supposed to have access to this
> information, but should still be able to administer it and/or retrieve
> the encryption keys if the HR personnel forget their passwords etc.
> The simplest way I can think of is to issue all the HR people with one
> password, encrypt the data in the DB layer (in code) and then store it
> in a table in the database. This way, anyone with access to the
> Database won't necessarily have access to the data. The problem with
> this one is that all the HR personnel share one password, which doesn't
> seem like a nice (secure) solution.
> The other option was to store the encryption key in the database and
> encrypt the key itself with the password provided by the individual
> user (HR person), which will be stored against that user's record. This
> way, whenever a data request is made, the database sends the encrypted
> performance review data and the password encrypted key to the user. The
> user will then decrypt the key using their password, and then decrypt
> the data using the key. This way, if the user ever forgets their
> password, all the DBA (and/or App with owner acess) has to do is
> reencrypt the key with a new user password and store it against the
> user's record. The problem with this is storing the key in the database
> and yet restricting access to the DBA. They should not have raw access
> to the key (which essentially means they can see the data in the
> database), but still should have enough access that if a user forgets
> their password, the DBA can reset the password without losing all the
> existing data.
> I was thinking of storing the key on a key server somewhere with
> different access rights etc. but that means the system starts to get
> complex.
> How would you guys go about building such a system? Does MS Sql Server
> 2005 provide any mechanisms for such functionality? Plus, as a design
> issue, is it better to let the database handle the encryption, key
> management, roles/privileges or is it better to do it in the
> application itself? One of the advantages I can think of for doing it
> in the application is that it gives a bit of database independence, i
> can change the underlying database easily without having to rewrite a
> whole heap of functionality. The other reason is that this way, i won't
> have unencrypted data or keys on wire.
> Looking forward to some expert opinions and discussions regarding this
> problem.
> Thanks in advance.|||The key (ahem) here is this:
>> Whoever has access to the decryption key has access to the data. <<
Plain and simple. If the DBAs can read the decryption key, they can
read the data. So the statement "but should still be able to administer
it and/or retrieve the encryption keys if the HR personnel forget their
passwords etc." is what I call a "non-starter". By definition, the DBAs
have access to the data.
You're right on one point though. If all people share access to the
same password or decryption key, you have an insecure solution. Think
of it this way -- the bigger the "secret" is, and the more people who
have access to that secret, the less secure the system.
Encrypting the key doesn't work either because you have to encrypt the
key with another key. Where are you going to put that key? Are the
DBAs going to have access to it too?
Here's my take on it:
Carefully analyze what is and what is not a secret in the system. Only
encrypt the real secrets. Limit the access to the database to the
smallest set of people possible. If you don't want the DBAs to have
access to the encryption key, that by definition means you have to store
the key outside the database. I recommend that you use .NET's
encryption routines to encrypt your secrets *outside* of the database,
and simply store the data in the system once it's encrypted.
So it boils down to this. SOMEBODY needs access to the keys. They need
to exist somewhere (even if it's not on your SQL Server). The
administrator of that machine will probably be able to read the key.
Deny that person read access to the database. That person needs to be
trustworthy, too. If he/she is not, they can share the key with an
untrustworthy DBA and get your data by writing a small program.
HTH
-Dave
nyathancha@.hotmail.com wrote:
> Hello? Anyone? Surely this is not such a hard problem, or particularly
> unusual. I would guess there are many times when you would want to
> encrypt the data in a database, so as to make it inaccessible to the
> DBA's, but still give them some administrative privileges. One of the
> constraints is that the user with access to the data doesn't
> necessarily "own" the data, so that if they forget the password (i.e
> lose the encryption key), all the data is not lost.
>
>
>
> On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
>> Hi,
>> I am writing an .NET 2.0 app that has different users logging in who
>> have different access levels in the app. One of the user roles is "HR".
>> When the user logs in with these credentials, they have a whole heap of
>> buttons/links/pages related to HR stuff that normal team members don't.
>> One of the sensitive information that needs to be displayed and more
>> importantly, stored in the database is the employee performance reviews
>> (HR access only).
>> I am wondering how I would go about storing/maintaining this
>> information as even the DBA's are not supposed to have access to this
>> information, but should still be able to administer it and/or retrieve
>> the encryption keys if the HR personnel forget their passwords etc.
>> The simplest way I can think of is to issue all the HR people with one
>> password, encrypt the data in the DB layer (in code) and then store it
>> in a table in the database. This way, anyone with access to the
>> Database won't necessarily have access to the data. The problem with
>> this one is that all the HR personnel share one password, which doesn't
>> seem like a nice (secure) solution.
>> The other option was to store the encryption key in the database and
>> encrypt the key itself with the password provided by the individual
>> user (HR person), which will be stored against that user's record. This
>> way, whenever a data request is made, the database sends the encrypted
>> performance review data and the password encrypted key to the user. The
>> user will then decrypt the key using their password, and then decrypt
>> the data using the key. This way, if the user ever forgets their
>> password, all the DBA (and/or App with owner acess) has to do is
>> reencrypt the key with a new user password and store it against the
>> user's record. The problem with this is storing the key in the database
>> and yet restricting access to the DBA. They should not have raw access
>> to the key (which essentially means they can see the data in the
>> database), but still should have enough access that if a user forgets
>> their password, the DBA can reset the password without losing all the
>> existing data.
>> I was thinking of storing the key on a key server somewhere with
>> different access rights etc. but that means the system starts to get
>> complex.
>> How would you guys go about building such a system? Does MS Sql Server
>> 2005 provide any mechanisms for such functionality? Plus, as a design
>> issue, is it better to let the database handle the encryption, key
>> management, roles/privileges or is it better to do it in the
>> application itself? One of the advantages I can think of for doing it
>> in the application is that it gives a bit of database independence, i
>> can change the underlying database easily without having to rewrite a
>> whole heap of functionality. The other reason is that this way, i won't
>> have unencrypted data or keys on wire.
>> Looking forward to some expert opinions and discussions regarding this
>> problem.
>> Thanks in advance.
>
-Dave Markle
http://www.markleconsulting.com/blog|||Hi Dave, Thanks for the reply.
I was thinking along the same lines as well. I couldn't think of anyway
of storing the key in the database and still restricting access to the
DBA. Which is why I mentioned in my original post the "key server
somewhere with
different access rights" . Unless sql server has some magic
features/functionality that I don't know about.
So how hard would it be to build/deploy a key server? Maybe this is
more of an ecryption/security question rather than a database question.
My idea of encrypting the key with another key was that the second
"key" would be the user's password. This way the DBA doesn't have
access to the original data encryption key even if its stored in the
database (because it can only be decrypted using the user's password),
but if the user does forget their password, the data is not lost
because it wasn't encrypted with their password(/key). The DBA can
"reset" their password by reencrypting the orginal data key with a new
user password key and storing it against the column. Which is where I
came to the key server. The fact that the DBA should be able to get the
data key encrypted with a user password, but not the raw data key
itself. I can't think of anyway of doing this without an independent
application.
Does anyone know any commercial encryption key storage and distribution
solutions/products?
I am sure at some stage the data encryption key will itself have to be
encrypted before being put on the wire. Other wise you would have the
encrypted data coming from the database, the plain encryption key
coming from the key server. Doesn't seem particularly secure.
What is the general industry solution to a situation like this, where
you want to store sensitive data in a database, but the data is
accessible (i.e belongs to) to more than one person (so that if that
one person forgets their password/key, the data itself is not lost)
On Jan 25, 1:28 pm, Dave Markle <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com>
wrote:
> The key (ahem) here is this:
> >> Whoever has access to the decryption key has access to the data. <<
> Plain and simple. If the DBAs can read the decryption key, they can
> read the data. So the statement "but should still be able to administer
> it and/or retrieve the encryption keys if the HR personnel forget their
> passwords etc." is what I call a "non-starter". By definition, the DBAs
> have access to the data.
> You're right on one point though. If all people share access to the
> same password or decryption key, you have an insecure solution. Think
> of it this way -- the bigger the "secret" is, and the more people who
> have access to that secret, the less secure the system.
> Encrypting the key doesn't work either because you have to encrypt the
> key with another key. Where are you going to put that key? Are the
> DBAs going to have access to it too?
> Here's my take on it:
> Carefully analyze what is and what is not a secret in the system. Only
> encrypt the real secrets. Limit the access to the database to the
> smallest set of people possible. If you don't want the DBAs to have
> access to the encryption key, that by definition means you have to store
> the key outside the database. I recommend that you use .NET's
> encryption routines to encrypt your secrets *outside* of the database,
> and simply store the data in the system once it's encrypted.
> So it boils down to this. SOMEBODY needs access to the keys. They need
> to exist somewhere (even if it's not on your SQL Server). The
> administrator of that machine will probably be able to read the key.
> Deny that person read access to the database. That person needs to be
> trustworthy, too. If he/she is not, they can share the key with an
> untrustworthy DBA and get your data by writing a small program.
> HTH
> -Dave
>
> nyathan...@.hotmail.com wrote:
> > Hello? Anyone? Surely this is not such a hard problem, or particularly
> > unusual. I would guess there are many times when you would want to
> > encrypt the data in a database, so as to make it inaccessible to the
> > DBA's, but still give them some administrative privileges. One of the
> > constraints is that the user with access to the data doesn't
> > necessarily "own" the data, so that if they forget the password (i.e
> > lose the encryption key), all the data is not lost.
> > On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> >> Hi,
> >> I am writing an .NET 2.0 app that has different users logging in who
> >> have different access levels in the app. One of the user roles is "HR".
> >> When the user logs in with these credentials, they have a whole heap of
> >> buttons/links/pages related to HR stuff that normal team members don't.
> >> One of the sensitive information that needs to be displayed and more
> >> importantly, stored in the database is the employee performance reviews
> >> (HR access only).
> >> I am wondering how I would go about storing/maintaining this
> >> information as even the DBA's are not supposed to have access to this
> >> information, but should still be able to administer it and/or retrieve
> >> the encryption keys if the HR personnel forget their passwords etc.
> >> The simplest way I can think of is to issue all the HR people with one
> >> password, encrypt the data in the DB layer (in code) and then store it
> >> in a table in the database. This way, anyone with access to the
> >> Database won't necessarily have access to the data. The problem with
> >> this one is that all the HR personnel share one password, which doesn't
> >> seem like a nice (secure) solution.
> >> The other option was to store the encryption key in the database and
> >> encrypt the key itself with the password provided by the individual
> >> user (HR person), which will be stored against that user's record. This
> >> way, whenever a data request is made, the database sends the encrypted
> >> performance review data and the password encrypted key to the user. The
> >> user will then decrypt the key using their password, and then decrypt
> >> the data using the key. This way, if the user ever forgets their
> >> password, all the DBA (and/or App with owner acess) has to do is
> >> reencrypt the key with a new user password and store it against the
> >> user's record. The problem with this is storing the key in the database
> >> and yet restricting access to the DBA. They should not have raw access
> >> to the key (which essentially means they can see the data in the
> >> database), but still should have enough access that if a user forgets
> >> their password, the DBA can reset the password without losing all the
> >> existing data.
> >> I was thinking of storing the key on a key server somewhere with
> >> different access rights etc. but that means the system starts to get
> >> complex.
> >> How would you guys go about building such a system? Does MS Sql Server
> >> 2005 provide any mechanisms for such functionality? Plus, as a design
> >> issue, is it better to let the database handle the encryption, key
> >> management, roles/privileges or is it better to do it in the
> >> application itself? One of the advantages I can think of for doing it
> >> in the application is that it gives a bit of database independence, i
> >> can change the underlying database easily without having to rewrite a
> >> whole heap of functionality. The other reason is that this way, i won't
> >> have unencrypted data or keys on wire.
> >> Looking forward to some expert opinions and discussions regarding this
> >> problem.
> >> Thanks in advance.--
> -Dave Markle
> http://www.markleconsulting.com/blog
Database Encryption - Employee performance review
Hi,
I am writing an .NET 2.0 app that has different users logging in who
have different access levels in the app. One of the user roles is "HR".
When the user logs in with these credentials, they have a whole heap of
buttons/links/pages related to HR stuff that normal team members don't.
One of the sensitive information that needs to be displayed and more
importantly, stored in the database is the employee performance reviews
(HR access only).
I am wondering how I would go about storing/maintaining this
information as even the DBA's are not supposed to have access to this
information, but should still be able to administer it and/or retrieve
the encryption keys if the HR personnel forget their passwords etc.
The simplest way I can think of is to issue all the HR people with one
password, encrypt the data in the DB layer (in code) and then store it
in a table in the database. This way, anyone with access to the
Database won't necessarily have access to the data. The problem with
this one is that all the HR personnel share one password, which doesn't
seem like a nice (secure) solution.
The other option was to store the encryption key in the database and
encrypt the key itself with the password provided by the individual
user (HR person), which will be stored against that user's record. This
way, whenever a data request is made, the database sends the encrypted
performance review data and the password encrypted key to the user. The
user will then decrypt the key using their password, and then decrypt
the data using the key. This way, if the user ever forgets their
password, all the DBA (and/or App with owner acess) has to do is
reencrypt the key with a new user password and store it against the
user's record. The problem with this is storing the key in the database
and yet restricting access to the DBA. They should not have raw access
to the key (which essentially means they can see the data in the
database), but still should have enough access that if a user forgets
their password, the DBA can reset the password without losing all the
existing data.
I was thinking of storing the key on a key server somewhere with
different access rights etc. but that means the system starts to get
complex.
How would you guys go about building such a system? Does MS Sql Server
2005 provide any mechanisms for such functionality? Plus, as a design
issue, is it better to let the database handle the encryption, key
management, roles/privileges or is it better to do it in the
application itself? One of the advantages I can think of for doing it
in the application is that it gives a bit of database independence, i
can change the underlying database easily without having to rewrite a
whole heap of functionality. The other reason is that this way, i won't
have unencrypted data or keys on wire.
Looking forward to some expert opinions and discussions regarding this
problem.
Thanks in advance.
Hello? Anyone? Surely this is not such a hard problem, or particularly
unusual. I would guess there are many times when you would want to
encrypt the data in a database, so as to make it inaccessible to the
DBA's, but still give them some administrative privileges. One of the
constraints is that the user with access to the data doesn't
necessarily "own" the data, so that if they forget the password (i.e
lose the encryption key), all the data is not lost.
On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> Hi,
> I am writing an .NET 2.0 app that has different users logging in who
> have different access levels in the app. One of the user roles is "HR".
> When the user logs in with these credentials, they have a whole heap of
> buttons/links/pages related to HR stuff that normal team members don't.
> One of the sensitive information that needs to be displayed and more
> importantly, stored in the database is the employee performance reviews
> (HR access only).
> I am wondering how I would go about storing/maintaining this
> information as even the DBA's are not supposed to have access to this
> information, but should still be able to administer it and/or retrieve
> the encryption keys if the HR personnel forget their passwords etc.
> The simplest way I can think of is to issue all the HR people with one
> password, encrypt the data in the DB layer (in code) and then store it
> in a table in the database. This way, anyone with access to the
> Database won't necessarily have access to the data. The problem with
> this one is that all the HR personnel share one password, which doesn't
> seem like a nice (secure) solution.
> The other option was to store the encryption key in the database and
> encrypt the key itself with the password provided by the individual
> user (HR person), which will be stored against that user's record. This
> way, whenever a data request is made, the database sends the encrypted
> performance review data and the password encrypted key to the user. The
> user will then decrypt the key using their password, and then decrypt
> the data using the key. This way, if the user ever forgets their
> password, all the DBA (and/or App with owner acess) has to do is
> reencrypt the key with a new user password and store it against the
> user's record. The problem with this is storing the key in the database
> and yet restricting access to the DBA. They should not have raw access
> to the key (which essentially means they can see the data in the
> database), but still should have enough access that if a user forgets
> their password, the DBA can reset the password without losing all the
> existing data.
> I was thinking of storing the key on a key server somewhere with
> different access rights etc. but that means the system starts to get
> complex.
> How would you guys go about building such a system? Does MS Sql Server
> 2005 provide any mechanisms for such functionality? Plus, as a design
> issue, is it better to let the database handle the encryption, key
> management, roles/privileges or is it better to do it in the
> application itself? One of the advantages I can think of for doing it
> in the application is that it gives a bit of database independence, i
> can change the underlying database easily without having to rewrite a
> whole heap of functionality. The other reason is that this way, i won't
> have unencrypted data or keys on wire.
> Looking forward to some expert opinions and discussions regarding this
> problem.
> Thanks in advance.
|||The key (ahem) here is this:
[vbcol=seagreen]
Plain and simple. If the DBAs can read the decryption key, they can
read the data. So the statement "but should still be able to administer
it and/or retrieve the encryption keys if the HR personnel forget their
passwords etc." is what I call a "non-starter". By definition, the DBAs
have access to the data.
You're right on one point though. If all people share access to the
same password or decryption key, you have an insecure solution. Think
of it this way -- the bigger the "secret" is, and the more people who
have access to that secret, the less secure the system.
Encrypting the key doesn't work either because you have to encrypt the
key with another key. Where are you going to put that key? Are the
DBAs going to have access to it too?
Here's my take on it:
Carefully analyze what is and what is not a secret in the system. Only
encrypt the real secrets. Limit the access to the database to the
smallest set of people possible. If you don't want the DBAs to have
access to the encryption key, that by definition means you have to store
the key outside the database. I recommend that you use .NET's
encryption routines to encrypt your secrets *outside* of the database,
and simply store the data in the system once it's encrypted.
So it boils down to this. SOMEBODY needs access to the keys. They need
to exist somewhere (even if it's not on your SQL Server). The
administrator of that machine will probably be able to read the key.
Deny that person read access to the database. That person needs to be
trustworthy, too. If he/she is not, they can share the key with an
untrustworthy DBA and get your data by writing a small program.
HTH
-Dave
nyathancha@.hotmail.com wrote:
> Hello? Anyone? Surely this is not such a hard problem, or particularly
> unusual. I would guess there are many times when you would want to
> encrypt the data in a database, so as to make it inaccessible to the
> DBA's, but still give them some administrative privileges. One of the
> constraints is that the user with access to the data doesn't
> necessarily "own" the data, so that if they forget the password (i.e
> lose the encryption key), all the data is not lost.
>
>
>
> On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
>
-Dave Markle
http://www.markleconsulting.com/blog
|||Hi Dave, Thanks for the reply.
I was thinking along the same lines as well. I couldn't think of anyway
of storing the key in the database and still restricting access to the
DBA. Which is why I mentioned in my original post the "key server
somewhere with
different access rights" . Unless sql server has some magic
features/functionality that I don't know about.
So how hard would it be to build/deploy a key server? Maybe this is
more of an ecryption/security question rather than a database question.
My idea of encrypting the key with another key was that the second
"key" would be the user's password. This way the DBA doesn't have
access to the original data encryption key even if its stored in the
database (because it can only be decrypted using the user's password),
but if the user does forget their password, the data is not lost
because it wasn't encrypted with their password(/key). The DBA can
"reset" their password by reencrypting the orginal data key with a new
user password key and storing it against the column. Which is where I
came to the key server. The fact that the DBA should be able to get the
data key encrypted with a user password, but not the raw data key
itself. I can't think of anyway of doing this without an independent
application.
Does anyone know any commercial encryption key storage and distribution
solutions/products?
I am sure at some stage the data encryption key will itself have to be
encrypted before being put on the wire. Other wise you would have the
encrypted data coming from the database, the plain encryption key
coming from the key server. Doesn't seem particularly secure.
What is the general industry solution to a situation like this, where
you want to store sensitive data in a database, but the data is
accessible (i.e belongs to) to more than one person (so that if that
one person forgets their password/key, the data itself is not lost)
On Jan 25, 1:28 pm, Dave Markle <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com>
wrote:
> The key (ahem) here is this:
>
> Plain and simple. If the DBAs can read the decryption key, they can
> read the data. So the statement "but should still be able to administer
> it and/or retrieve the encryption keys if the HR personnel forget their
> passwords etc." is what I call a "non-starter". By definition, the DBAs
> have access to the data.
> You're right on one point though. If all people share access to the
> same password or decryption key, you have an insecure solution. Think
> of it this way -- the bigger the "secret" is, and the more people who
> have access to that secret, the less secure the system.
> Encrypting the key doesn't work either because you have to encrypt the
> key with another key. Where are you going to put that key? Are the
> DBAs going to have access to it too?
> Here's my take on it:
> Carefully analyze what is and what is not a secret in the system. Only
> encrypt the real secrets. Limit the access to the database to the
> smallest set of people possible. If you don't want the DBAs to have
> access to the encryption key, that by definition means you have to store
> the key outside the database. I recommend that you use .NET's
> encryption routines to encrypt your secrets *outside* of the database,
> and simply store the data in the system once it's encrypted.
> So it boils down to this. SOMEBODY needs access to the keys. They need
> to exist somewhere (even if it's not on your SQL Server). The
> administrator of that machine will probably be able to read the key.
> Deny that person read access to the database. That person needs to be
> trustworthy, too. If he/she is not, they can share the key with an
> untrustworthy DBA and get your data by writing a small program.
> HTH
> -Dave
>
> nyathan...@.hotmail.com wrote:
>
>
>
>
> -Dave Markle
> http://www.markleconsulting.com/blog
I am writing an .NET 2.0 app that has different users logging in who
have different access levels in the app. One of the user roles is "HR".
When the user logs in with these credentials, they have a whole heap of
buttons/links/pages related to HR stuff that normal team members don't.
One of the sensitive information that needs to be displayed and more
importantly, stored in the database is the employee performance reviews
(HR access only).
I am wondering how I would go about storing/maintaining this
information as even the DBA's are not supposed to have access to this
information, but should still be able to administer it and/or retrieve
the encryption keys if the HR personnel forget their passwords etc.
The simplest way I can think of is to issue all the HR people with one
password, encrypt the data in the DB layer (in code) and then store it
in a table in the database. This way, anyone with access to the
Database won't necessarily have access to the data. The problem with
this one is that all the HR personnel share one password, which doesn't
seem like a nice (secure) solution.
The other option was to store the encryption key in the database and
encrypt the key itself with the password provided by the individual
user (HR person), which will be stored against that user's record. This
way, whenever a data request is made, the database sends the encrypted
performance review data and the password encrypted key to the user. The
user will then decrypt the key using their password, and then decrypt
the data using the key. This way, if the user ever forgets their
password, all the DBA (and/or App with owner acess) has to do is
reencrypt the key with a new user password and store it against the
user's record. The problem with this is storing the key in the database
and yet restricting access to the DBA. They should not have raw access
to the key (which essentially means they can see the data in the
database), but still should have enough access that if a user forgets
their password, the DBA can reset the password without losing all the
existing data.
I was thinking of storing the key on a key server somewhere with
different access rights etc. but that means the system starts to get
complex.
How would you guys go about building such a system? Does MS Sql Server
2005 provide any mechanisms for such functionality? Plus, as a design
issue, is it better to let the database handle the encryption, key
management, roles/privileges or is it better to do it in the
application itself? One of the advantages I can think of for doing it
in the application is that it gives a bit of database independence, i
can change the underlying database easily without having to rewrite a
whole heap of functionality. The other reason is that this way, i won't
have unencrypted data or keys on wire.
Looking forward to some expert opinions and discussions regarding this
problem.
Thanks in advance.
Hello? Anyone? Surely this is not such a hard problem, or particularly
unusual. I would guess there are many times when you would want to
encrypt the data in a database, so as to make it inaccessible to the
DBA's, but still give them some administrative privileges. One of the
constraints is that the user with access to the data doesn't
necessarily "own" the data, so that if they forget the password (i.e
lose the encryption key), all the data is not lost.
On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> Hi,
> I am writing an .NET 2.0 app that has different users logging in who
> have different access levels in the app. One of the user roles is "HR".
> When the user logs in with these credentials, they have a whole heap of
> buttons/links/pages related to HR stuff that normal team members don't.
> One of the sensitive information that needs to be displayed and more
> importantly, stored in the database is the employee performance reviews
> (HR access only).
> I am wondering how I would go about storing/maintaining this
> information as even the DBA's are not supposed to have access to this
> information, but should still be able to administer it and/or retrieve
> the encryption keys if the HR personnel forget their passwords etc.
> The simplest way I can think of is to issue all the HR people with one
> password, encrypt the data in the DB layer (in code) and then store it
> in a table in the database. This way, anyone with access to the
> Database won't necessarily have access to the data. The problem with
> this one is that all the HR personnel share one password, which doesn't
> seem like a nice (secure) solution.
> The other option was to store the encryption key in the database and
> encrypt the key itself with the password provided by the individual
> user (HR person), which will be stored against that user's record. This
> way, whenever a data request is made, the database sends the encrypted
> performance review data and the password encrypted key to the user. The
> user will then decrypt the key using their password, and then decrypt
> the data using the key. This way, if the user ever forgets their
> password, all the DBA (and/or App with owner acess) has to do is
> reencrypt the key with a new user password and store it against the
> user's record. The problem with this is storing the key in the database
> and yet restricting access to the DBA. They should not have raw access
> to the key (which essentially means they can see the data in the
> database), but still should have enough access that if a user forgets
> their password, the DBA can reset the password without losing all the
> existing data.
> I was thinking of storing the key on a key server somewhere with
> different access rights etc. but that means the system starts to get
> complex.
> How would you guys go about building such a system? Does MS Sql Server
> 2005 provide any mechanisms for such functionality? Plus, as a design
> issue, is it better to let the database handle the encryption, key
> management, roles/privileges or is it better to do it in the
> application itself? One of the advantages I can think of for doing it
> in the application is that it gives a bit of database independence, i
> can change the underlying database easily without having to rewrite a
> whole heap of functionality. The other reason is that this way, i won't
> have unencrypted data or keys on wire.
> Looking forward to some expert opinions and discussions regarding this
> problem.
> Thanks in advance.
|||The key (ahem) here is this:
[vbcol=seagreen]
Plain and simple. If the DBAs can read the decryption key, they can
read the data. So the statement "but should still be able to administer
it and/or retrieve the encryption keys if the HR personnel forget their
passwords etc." is what I call a "non-starter". By definition, the DBAs
have access to the data.
You're right on one point though. If all people share access to the
same password or decryption key, you have an insecure solution. Think
of it this way -- the bigger the "secret" is, and the more people who
have access to that secret, the less secure the system.
Encrypting the key doesn't work either because you have to encrypt the
key with another key. Where are you going to put that key? Are the
DBAs going to have access to it too?
Here's my take on it:
Carefully analyze what is and what is not a secret in the system. Only
encrypt the real secrets. Limit the access to the database to the
smallest set of people possible. If you don't want the DBAs to have
access to the encryption key, that by definition means you have to store
the key outside the database. I recommend that you use .NET's
encryption routines to encrypt your secrets *outside* of the database,
and simply store the data in the system once it's encrypted.
So it boils down to this. SOMEBODY needs access to the keys. They need
to exist somewhere (even if it's not on your SQL Server). The
administrator of that machine will probably be able to read the key.
Deny that person read access to the database. That person needs to be
trustworthy, too. If he/she is not, they can share the key with an
untrustworthy DBA and get your data by writing a small program.
HTH
-Dave
nyathancha@.hotmail.com wrote:
> Hello? Anyone? Surely this is not such a hard problem, or particularly
> unusual. I would guess there are many times when you would want to
> encrypt the data in a database, so as to make it inaccessible to the
> DBA's, but still give them some administrative privileges. One of the
> constraints is that the user with access to the data doesn't
> necessarily "own" the data, so that if they forget the password (i.e
> lose the encryption key), all the data is not lost.
>
>
>
> On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
>
-Dave Markle
http://www.markleconsulting.com/blog
|||Hi Dave, Thanks for the reply.
I was thinking along the same lines as well. I couldn't think of anyway
of storing the key in the database and still restricting access to the
DBA. Which is why I mentioned in my original post the "key server
somewhere with
different access rights" . Unless sql server has some magic
features/functionality that I don't know about.
So how hard would it be to build/deploy a key server? Maybe this is
more of an ecryption/security question rather than a database question.
My idea of encrypting the key with another key was that the second
"key" would be the user's password. This way the DBA doesn't have
access to the original data encryption key even if its stored in the
database (because it can only be decrypted using the user's password),
but if the user does forget their password, the data is not lost
because it wasn't encrypted with their password(/key). The DBA can
"reset" their password by reencrypting the orginal data key with a new
user password key and storing it against the column. Which is where I
came to the key server. The fact that the DBA should be able to get the
data key encrypted with a user password, but not the raw data key
itself. I can't think of anyway of doing this without an independent
application.
Does anyone know any commercial encryption key storage and distribution
solutions/products?
I am sure at some stage the data encryption key will itself have to be
encrypted before being put on the wire. Other wise you would have the
encrypted data coming from the database, the plain encryption key
coming from the key server. Doesn't seem particularly secure.
What is the general industry solution to a situation like this, where
you want to store sensitive data in a database, but the data is
accessible (i.e belongs to) to more than one person (so that if that
one person forgets their password/key, the data itself is not lost)
On Jan 25, 1:28 pm, Dave Markle <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com>
wrote:
> The key (ahem) here is this:
>
> Plain and simple. If the DBAs can read the decryption key, they can
> read the data. So the statement "but should still be able to administer
> it and/or retrieve the encryption keys if the HR personnel forget their
> passwords etc." is what I call a "non-starter". By definition, the DBAs
> have access to the data.
> You're right on one point though. If all people share access to the
> same password or decryption key, you have an insecure solution. Think
> of it this way -- the bigger the "secret" is, and the more people who
> have access to that secret, the less secure the system.
> Encrypting the key doesn't work either because you have to encrypt the
> key with another key. Where are you going to put that key? Are the
> DBAs going to have access to it too?
> Here's my take on it:
> Carefully analyze what is and what is not a secret in the system. Only
> encrypt the real secrets. Limit the access to the database to the
> smallest set of people possible. If you don't want the DBAs to have
> access to the encryption key, that by definition means you have to store
> the key outside the database. I recommend that you use .NET's
> encryption routines to encrypt your secrets *outside* of the database,
> and simply store the data in the system once it's encrypted.
> So it boils down to this. SOMEBODY needs access to the keys. They need
> to exist somewhere (even if it's not on your SQL Server). The
> administrator of that machine will probably be able to read the key.
> Deny that person read access to the database. That person needs to be
> trustworthy, too. If he/she is not, they can share the key with an
> untrustworthy DBA and get your data by writing a small program.
> HTH
> -Dave
>
> nyathan...@.hotmail.com wrote:
>
>
>
>
> -Dave Markle
> http://www.markleconsulting.com/blog
Database Encryption - Employee performance review
Hi,
I am writing an .NET 2.0 app that has different users logging in who
have different access levels in the app. One of the user roles is "HR".
When the user logs in with these credentials, they have a whole heap of
buttons/links/pages related to HR stuff that normal team members don't.
One of the sensitive information that needs to be displayed and more
importantly, stored in the database is the employee performance reviews
(HR access only).
I am wondering how I would go about storing/maintaining this
information as even the DBA's are not supposed to have access to this
information, but should still be able to administer it and/or retrieve
the encryption keys if the HR personnel forget their passwords etc.
The simplest way I can think of is to issue all the HR people with one
password, encrypt the data in the DB layer (in code) and then store it
in a table in the database. This way, anyone with access to the
Database won't necessarily have access to the data. The problem with
this one is that all the HR personnel share one password, which doesn't
seem like a nice (secure) solution.
The other option was to store the encryption key in the database and
encrypt the key itself with the password provided by the individual
user (HR person), which will be stored against that user's record. This
way, whenever a data request is made, the database sends the encrypted
performance review data and the password encrypted key to the user. The
user will then decrypt the key using their password, and then decrypt
the data using the key. This way, if the user ever forgets their
password, all the DBA (and/or App with owner acess) has to do is
reencrypt the key with a new user password and store it against the
user's record. The problem with this is storing the key in the database
and yet restricting access to the DBA. They should not have raw access
to the key (which essentially means they can see the data in the
database), but still should have enough access that if a user forgets
their password, the DBA can reset the password without losing all the
existing data.
I was thinking of storing the key on a key server somewhere with
different access rights etc. but that means the system starts to get
complex.
How would you guys go about building such a system? Does MS Sql Server
2005 provide any mechanisms for such functionality? Plus, as a design
issue, is it better to let the database handle the encryption, key
management, roles/privileges or is it better to do it in the
application itself? One of the advantages I can think of for doing it
in the application is that it gives a bit of database independence, i
can change the underlying database easily without having to rewrite a
whole heap of functionality. The other reason is that this way, i won't
have unencrypted data or keys on wire.
Looking forward to some expert opinions and discussions regarding this
problem.
Thanks in advance.Hello? Anyone? Surely this is not such a hard problem, or particularly
unusual. I would guess there are many times when you would want to
encrypt the data in a database, so as to make it inaccessible to the
DBA's, but still give them some administrative privileges. One of the
constraints is that the user with access to the data doesn't
necessarily "own" the data, so that if they forget the password (i.e
lose the encryption key), all the data is not lost.
On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> Hi,
> I am writing an .NET 2.0 app that has different users logging in who
> have different access levels in the app. One of the user roles is "HR".
> When the user logs in with these credentials, they have a whole heap of
> buttons/links/pages related to HR stuff that normal team members don't.
> One of the sensitive information that needs to be displayed and more
> importantly, stored in the database is the employee performance reviews
> (HR access only).
> I am wondering how I would go about storing/maintaining this
> information as even the DBA's are not supposed to have access to this
> information, but should still be able to administer it and/or retrieve
> the encryption keys if the HR personnel forget their passwords etc.
> The simplest way I can think of is to issue all the HR people with one
> password, encrypt the data in the DB layer (in code) and then store it
> in a table in the database. This way, anyone with access to the
> Database won't necessarily have access to the data. The problem with
> this one is that all the HR personnel share one password, which doesn't
> seem like a nice (secure) solution.
> The other option was to store the encryption key in the database and
> encrypt the key itself with the password provided by the individual
> user (HR person), which will be stored against that user's record. This
> way, whenever a data request is made, the database sends the encrypted
> performance review data and the password encrypted key to the user. The
> user will then decrypt the key using their password, and then decrypt
> the data using the key. This way, if the user ever forgets their
> password, all the DBA (and/or App with owner acess) has to do is
> reencrypt the key with a new user password and store it against the
> user's record. The problem with this is storing the key in the database
> and yet restricting access to the DBA. They should not have raw access
> to the key (which essentially means they can see the data in the
> database), but still should have enough access that if a user forgets
> their password, the DBA can reset the password without losing all the
> existing data.
> I was thinking of storing the key on a key server somewhere with
> different access rights etc. but that means the system starts to get
> complex.
> How would you guys go about building such a system? Does MS Sql Server
> 2005 provide any mechanisms for such functionality? Plus, as a design
> issue, is it better to let the database handle the encryption, key
> management, roles/privileges or is it better to do it in the
> application itself? One of the advantages I can think of for doing it
> in the application is that it gives a bit of database independence, i
> can change the underlying database easily without having to rewrite a
> whole heap of functionality. The other reason is that this way, i won't
> have unencrypted data or keys on wire.
> Looking forward to some expert opinions and discussions regarding this
> problem.
> Thanks in advance.|||The key (ahem) here is this:
Plain and simple. If the DBAs can read the decryption key, they can
read the data. So the statement "but should still be able to administer
it and/or retrieve the encryption keys if the HR personnel forget their
passwords etc." is what I call a "non-starter". By definition, the DBAs
have access to the data.
You're right on one point though. If all people share access to the
same password or decryption key, you have an insecure solution. Think
of it this way -- the bigger the "secret" is, and the more people who
have access to that secret, the less secure the system.
Encrypting the key doesn't work either because you have to encrypt the
key with another key. Where are you going to put that key? Are the
DBAs going to have access to it too?
Here's my take on it:
Carefully analyze what is and what is not a secret in the system. Only
encrypt the real secrets. Limit the access to the database to the
smallest set of people possible. If you don't want the DBAs to have
access to the encryption key, that by definition means you have to store
the key outside the database. I recommend that you use .NET's
encryption routines to encrypt your secrets *outside* of the database,
and simply store the data in the system once it's encrypted.
So it boils down to this. SOMEBODY needs access to the keys. They need
to exist somewhere (even if it's not on your SQL Server). The
administrator of that machine will probably be able to read the key.
Deny that person read access to the database. That person needs to be
trustworthy, too. If he/she is not, they can share the key with an
untrustworthy DBA and get your data by writing a small program.
HTH
-Dave
nyathancha@.hotmail.com wrote:[vbcol=seagreen]
> Hello? Anyone? Surely this is not such a hard problem, or particularly
> unusual. I would guess there are many times when you would want to
> encrypt the data in a database, so as to make it inaccessible to the
> DBA's, but still give them some administrative privileges. One of the
> constraints is that the user with access to the data doesn't
> necessarily "own" the data, so that if they forget the password (i.e
> lose the encryption key), all the data is not lost.
>
>
>
> On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
>
-Dave Markle
http://www.markleconsulting.com/blog|||Hi Dave, Thanks for the reply.
I was thinking along the same lines as well. I couldn't think of anyway
of storing the key in the database and still restricting access to the
DBA. Which is why I mentioned in my original post the "key server
somewhere with
different access rights" . Unless sql server has some magic
features/functionality that I don't know about.
So how hard would it be to build/deploy a key server? Maybe this is
more of an ecryption/security question rather than a database question.
My idea of encrypting the key with another key was that the second
"key" would be the user's password. This way the DBA doesn't have
access to the original data encryption key even if its stored in the
database (because it can only be decrypted using the user's password),
but if the user does forget their password, the data is not lost
because it wasn't encrypted with their password(/key). The DBA can
"reset" their password by reencrypting the orginal data key with a new
user password key and storing it against the column. Which is where I
came to the key server. The fact that the DBA should be able to get the
data key encrypted with a user password, but not the raw data key
itself. I can't think of anyway of doing this without an independent
application.
Does anyone know any commercial encryption key storage and distribution
solutions/products?
I am sure at some stage the data encryption key will itself have to be
encrypted before being put on the wire. Other wise you would have the
encrypted data coming from the database, the plain encryption key
coming from the key server. Doesn't seem particularly secure.
What is the general industry solution to a situation like this, where
you want to store sensitive data in a database, but the data is
accessible (i.e belongs to) to more than one person (so that if that
one person forgets their password/key, the data itself is not lost)
On Jan 25, 1:28 pm, Dave Markle <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com>
wrote:
> The key (ahem) here is this:
>
> Plain and simple. If the DBAs can read the decryption key, they can
> read the data. So the statement "but should still be able to administer
> it and/or retrieve the encryption keys if the HR personnel forget their
> passwords etc." is what I call a "non-starter". By definition, the DBAs
> have access to the data.
> You're right on one point though. If all people share access to the
> same password or decryption key, you have an insecure solution. Think
> of it this way -- the bigger the "secret" is, and the more people who
> have access to that secret, the less secure the system.
> Encrypting the key doesn't work either because you have to encrypt the
> key with another key. Where are you going to put that key? Are the
> DBAs going to have access to it too?
> Here's my take on it:
> Carefully analyze what is and what is not a secret in the system. Only
> encrypt the real secrets. Limit the access to the database to the
> smallest set of people possible. If you don't want the DBAs to have
> access to the encryption key, that by definition means you have to store
> the key outside the database. I recommend that you use .NET's
> encryption routines to encrypt your secrets *outside* of the database,
> and simply store the data in the system once it's encrypted.
> So it boils down to this. SOMEBODY needs access to the keys. They need
> to exist somewhere (even if it's not on your SQL Server). The
> administrator of that machine will probably be able to read the key.
> Deny that person read access to the database. That person needs to be
> trustworthy, too. If he/she is not, they can share the key with an
> untrustworthy DBA and get your data by writing a small program.
> HTH
> -Dave
>
> nyathan...@.hotmail.com wrote:
>
>
>
>
>
>
>
>
> -Dave Markle
> http://www.markleconsulting.com/blog
I am writing an .NET 2.0 app that has different users logging in who
have different access levels in the app. One of the user roles is "HR".
When the user logs in with these credentials, they have a whole heap of
buttons/links/pages related to HR stuff that normal team members don't.
One of the sensitive information that needs to be displayed and more
importantly, stored in the database is the employee performance reviews
(HR access only).
I am wondering how I would go about storing/maintaining this
information as even the DBA's are not supposed to have access to this
information, but should still be able to administer it and/or retrieve
the encryption keys if the HR personnel forget their passwords etc.
The simplest way I can think of is to issue all the HR people with one
password, encrypt the data in the DB layer (in code) and then store it
in a table in the database. This way, anyone with access to the
Database won't necessarily have access to the data. The problem with
this one is that all the HR personnel share one password, which doesn't
seem like a nice (secure) solution.
The other option was to store the encryption key in the database and
encrypt the key itself with the password provided by the individual
user (HR person), which will be stored against that user's record. This
way, whenever a data request is made, the database sends the encrypted
performance review data and the password encrypted key to the user. The
user will then decrypt the key using their password, and then decrypt
the data using the key. This way, if the user ever forgets their
password, all the DBA (and/or App with owner acess) has to do is
reencrypt the key with a new user password and store it against the
user's record. The problem with this is storing the key in the database
and yet restricting access to the DBA. They should not have raw access
to the key (which essentially means they can see the data in the
database), but still should have enough access that if a user forgets
their password, the DBA can reset the password without losing all the
existing data.
I was thinking of storing the key on a key server somewhere with
different access rights etc. but that means the system starts to get
complex.
How would you guys go about building such a system? Does MS Sql Server
2005 provide any mechanisms for such functionality? Plus, as a design
issue, is it better to let the database handle the encryption, key
management, roles/privileges or is it better to do it in the
application itself? One of the advantages I can think of for doing it
in the application is that it gives a bit of database independence, i
can change the underlying database easily without having to rewrite a
whole heap of functionality. The other reason is that this way, i won't
have unencrypted data or keys on wire.
Looking forward to some expert opinions and discussions regarding this
problem.
Thanks in advance.Hello? Anyone? Surely this is not such a hard problem, or particularly
unusual. I would guess there are many times when you would want to
encrypt the data in a database, so as to make it inaccessible to the
DBA's, but still give them some administrative privileges. One of the
constraints is that the user with access to the data doesn't
necessarily "own" the data, so that if they forget the password (i.e
lose the encryption key), all the data is not lost.
On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> Hi,
> I am writing an .NET 2.0 app that has different users logging in who
> have different access levels in the app. One of the user roles is "HR".
> When the user logs in with these credentials, they have a whole heap of
> buttons/links/pages related to HR stuff that normal team members don't.
> One of the sensitive information that needs to be displayed and more
> importantly, stored in the database is the employee performance reviews
> (HR access only).
> I am wondering how I would go about storing/maintaining this
> information as even the DBA's are not supposed to have access to this
> information, but should still be able to administer it and/or retrieve
> the encryption keys if the HR personnel forget their passwords etc.
> The simplest way I can think of is to issue all the HR people with one
> password, encrypt the data in the DB layer (in code) and then store it
> in a table in the database. This way, anyone with access to the
> Database won't necessarily have access to the data. The problem with
> this one is that all the HR personnel share one password, which doesn't
> seem like a nice (secure) solution.
> The other option was to store the encryption key in the database and
> encrypt the key itself with the password provided by the individual
> user (HR person), which will be stored against that user's record. This
> way, whenever a data request is made, the database sends the encrypted
> performance review data and the password encrypted key to the user. The
> user will then decrypt the key using their password, and then decrypt
> the data using the key. This way, if the user ever forgets their
> password, all the DBA (and/or App with owner acess) has to do is
> reencrypt the key with a new user password and store it against the
> user's record. The problem with this is storing the key in the database
> and yet restricting access to the DBA. They should not have raw access
> to the key (which essentially means they can see the data in the
> database), but still should have enough access that if a user forgets
> their password, the DBA can reset the password without losing all the
> existing data.
> I was thinking of storing the key on a key server somewhere with
> different access rights etc. but that means the system starts to get
> complex.
> How would you guys go about building such a system? Does MS Sql Server
> 2005 provide any mechanisms for such functionality? Plus, as a design
> issue, is it better to let the database handle the encryption, key
> management, roles/privileges or is it better to do it in the
> application itself? One of the advantages I can think of for doing it
> in the application is that it gives a bit of database independence, i
> can change the underlying database easily without having to rewrite a
> whole heap of functionality. The other reason is that this way, i won't
> have unencrypted data or keys on wire.
> Looking forward to some expert opinions and discussions regarding this
> problem.
> Thanks in advance.|||The key (ahem) here is this:
Plain and simple. If the DBAs can read the decryption key, they can
read the data. So the statement "but should still be able to administer
it and/or retrieve the encryption keys if the HR personnel forget their
passwords etc." is what I call a "non-starter". By definition, the DBAs
have access to the data.
You're right on one point though. If all people share access to the
same password or decryption key, you have an insecure solution. Think
of it this way -- the bigger the "secret" is, and the more people who
have access to that secret, the less secure the system.
Encrypting the key doesn't work either because you have to encrypt the
key with another key. Where are you going to put that key? Are the
DBAs going to have access to it too?
Here's my take on it:
Carefully analyze what is and what is not a secret in the system. Only
encrypt the real secrets. Limit the access to the database to the
smallest set of people possible. If you don't want the DBAs to have
access to the encryption key, that by definition means you have to store
the key outside the database. I recommend that you use .NET's
encryption routines to encrypt your secrets *outside* of the database,
and simply store the data in the system once it's encrypted.
So it boils down to this. SOMEBODY needs access to the keys. They need
to exist somewhere (even if it's not on your SQL Server). The
administrator of that machine will probably be able to read the key.
Deny that person read access to the database. That person needs to be
trustworthy, too. If he/she is not, they can share the key with an
untrustworthy DBA and get your data by writing a small program.
HTH
-Dave
nyathancha@.hotmail.com wrote:[vbcol=seagreen]
> Hello? Anyone? Surely this is not such a hard problem, or particularly
> unusual. I would guess there are many times when you would want to
> encrypt the data in a database, so as to make it inaccessible to the
> DBA's, but still give them some administrative privileges. One of the
> constraints is that the user with access to the data doesn't
> necessarily "own" the data, so that if they forget the password (i.e
> lose the encryption key), all the data is not lost.
>
>
>
> On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
>
-Dave Markle
http://www.markleconsulting.com/blog|||Hi Dave, Thanks for the reply.
I was thinking along the same lines as well. I couldn't think of anyway
of storing the key in the database and still restricting access to the
DBA. Which is why I mentioned in my original post the "key server
somewhere with
different access rights" . Unless sql server has some magic
features/functionality that I don't know about.
So how hard would it be to build/deploy a key server? Maybe this is
more of an ecryption/security question rather than a database question.
My idea of encrypting the key with another key was that the second
"key" would be the user's password. This way the DBA doesn't have
access to the original data encryption key even if its stored in the
database (because it can only be decrypted using the user's password),
but if the user does forget their password, the data is not lost
because it wasn't encrypted with their password(/key). The DBA can
"reset" their password by reencrypting the orginal data key with a new
user password key and storing it against the column. Which is where I
came to the key server. The fact that the DBA should be able to get the
data key encrypted with a user password, but not the raw data key
itself. I can't think of anyway of doing this without an independent
application.
Does anyone know any commercial encryption key storage and distribution
solutions/products?
I am sure at some stage the data encryption key will itself have to be
encrypted before being put on the wire. Other wise you would have the
encrypted data coming from the database, the plain encryption key
coming from the key server. Doesn't seem particularly secure.
What is the general industry solution to a situation like this, where
you want to store sensitive data in a database, but the data is
accessible (i.e belongs to) to more than one person (so that if that
one person forgets their password/key, the data itself is not lost)
On Jan 25, 1:28 pm, Dave Markle <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com>
wrote:
> The key (ahem) here is this:
>
> Plain and simple. If the DBAs can read the decryption key, they can
> read the data. So the statement "but should still be able to administer
> it and/or retrieve the encryption keys if the HR personnel forget their
> passwords etc." is what I call a "non-starter". By definition, the DBAs
> have access to the data.
> You're right on one point though. If all people share access to the
> same password or decryption key, you have an insecure solution. Think
> of it this way -- the bigger the "secret" is, and the more people who
> have access to that secret, the less secure the system.
> Encrypting the key doesn't work either because you have to encrypt the
> key with another key. Where are you going to put that key? Are the
> DBAs going to have access to it too?
> Here's my take on it:
> Carefully analyze what is and what is not a secret in the system. Only
> encrypt the real secrets. Limit the access to the database to the
> smallest set of people possible. If you don't want the DBAs to have
> access to the encryption key, that by definition means you have to store
> the key outside the database. I recommend that you use .NET's
> encryption routines to encrypt your secrets *outside* of the database,
> and simply store the data in the system once it's encrypted.
> So it boils down to this. SOMEBODY needs access to the keys. They need
> to exist somewhere (even if it's not on your SQL Server). The
> administrator of that machine will probably be able to read the key.
> Deny that person read access to the database. That person needs to be
> trustworthy, too. If he/she is not, they can share the key with an
> untrustworthy DBA and get your data by writing a small program.
> HTH
> -Dave
>
> nyathan...@.hotmail.com wrote:
>
>
>
>
>
>
>
>
> -Dave Markle
> http://www.markleconsulting.com/blog
Thursday, March 8, 2012
Database Diagrams
I have a SQL 2000 database that I imported into SQL Server 2005 by restoring
the database from a backup. I carefully re-did the Users to make sure there
were corresponding Logins.
I want to create a Database Diagram using the Microsoft SQL Server
Management Studio. When I right click on the Database Diagrams node within
the correct database I get the following error.
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTE AUTHORIZATION statement to set the
database owner to a valid login, then add the database diagram support
objects.
Now I did both of these suggestions and then looked at the owner of the
database, who in fact is a valid Login with sysadmin privileges.
I'm not sure where to go from here since everything seems to be done
correctly.
Any suggestions?
Michael Hockstein
This is covered in section 4.8 in the Readme file that comes with the
product .
To install database diagram support in SQL Server Management Studio,
databases must be in SQL Server 2005 database compatibility level. Database
compatibility level can be reset after diagram support is installed. To
create database diagrams, change the database compatibility level to 2005,
install database diagram support, and then return the database to the
desired database compatibility level. For more information, search for
"sp_dbcmptlevel" in SQL Server Books Online.
So, run the following statement and try the diagram again.
EXEC sp_dbcmptlevel database_name, 90
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"michael" <howlinghound@.nospam.nospam> wrote in message
news:EB3D155A-EC71-4854-BAC6-7B14BE038586@.microsoft.com...
>I have a SQL 2000 database that I imported into SQL Server 2005 by
>restoring
> the database from a backup. I carefully re-did the Users to make sure
> there
> were corresponding Logins.
> I want to create a Database Diagram using the Microsoft SQL Server
> Management Studio. When I right click on the Database Diagrams node
> within
> the correct database I get the following error.
> Database diagram support objects cannot be installed because this database
> does not have a valid owner. To continue, first use the Files page of the
> Database Properties dialog box or the ALTE AUTHORIZATION statement to set
> the
> database owner to a valid login, then add the database diagram support
> objects.
> Now I did both of these suggestions and then looked at the owner of the
> database, who in fact is a valid Login with sysadmin privileges.
> I'm not sure where to go from here since everything seems to be done
> correctly.
> Any suggestions?
> --
> Michael Hockstein
|||Fantastic. Worked like a charm. Could you also give me a clue as to where to
find the Readme file? I looked in the SQL Server directory and subs but
couldn't locate it. This would be a good one to keep on file.
Michael Hockstein
"Gail Erickson [MS]" wrote:
> This is covered in section 4.8 in the Readme file that comes with the
> product .
> To install database diagram support in SQL Server Management Studio,
> databases must be in SQL Server 2005 database compatibility level. Database
> compatibility level can be reset after diagram support is installed. To
> create database diagrams, change the database compatibility level to 2005,
> install database diagram support, and then return the database to the
> desired database compatibility level. For more information, search for
> "sp_dbcmptlevel" in SQL Server Books Online.
> So, run the following statement and try the diagram again.
> EXEC sp_dbcmptlevel database_name, 90
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:EB3D155A-EC71-4854-BAC6-7B14BE038586@.microsoft.com...
>
>
|||michael (howlinghound@.nospam.nospam) writes:
> Fantastic. Worked like a charm. Could you also give me a clue as to
> where to find the Readme file? I looked in the SQL Server directory and
> subs but couldn't locate it. This would be a good one to keep on file.
I happened to have the SQL 2005 server DVD in my player, and I found a
Readme in G:\SQL Server x86\Servers\ReadmeSQL2005.htm. There seem to be
one in every top-level setup directory.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||In addition to Erland's response, you can also find the readme here:
http://support.microsoft.com/default...b;en-us;910228
Also, the April update to Books Online will contain this information in the
various topics on database diagrams.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"michael" <howlinghound@.nospam.nospam> wrote in message
news:4885DE6E-B80F-4367-9B2E-D772F090CD7B@.microsoft.com...[vbcol=seagreen]
> Fantastic. Worked like a charm. Could you also give me a clue as to where
> to
> find the Readme file? I looked in the SQL Server directory and subs but
> couldn't locate it. This would be a good one to keep on file.
>
> --
> Michael Hockstein
>
> "Gail Erickson [MS]" wrote:
|||OK! Thanks for the tip.
Michael Hockstein
"Erland Sommarskog" wrote:
> michael (howlinghound@.nospam.nospam) writes:
> I happened to have the SQL 2005 server DVD in my player, and I found a
> Readme in G:\SQL Server x86\Servers\ReadmeSQL2005.htm. There seem to be
> one in every top-level setup directory.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
|||Thanks Gail!
Michael Hockstein
"Gail Erickson [MS]" wrote:
> In addition to Erland's response, you can also find the readme here:
> http://support.microsoft.com/default...b;en-us;910228
> Also, the April update to Books Online will contain this information in the
> various topics on database diagrams.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:4885DE6E-B80F-4367-9B2E-D772F090CD7B@.microsoft.com...
>
>
the database from a backup. I carefully re-did the Users to make sure there
were corresponding Logins.
I want to create a Database Diagram using the Microsoft SQL Server
Management Studio. When I right click on the Database Diagrams node within
the correct database I get the following error.
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTE AUTHORIZATION statement to set the
database owner to a valid login, then add the database diagram support
objects.
Now I did both of these suggestions and then looked at the owner of the
database, who in fact is a valid Login with sysadmin privileges.
I'm not sure where to go from here since everything seems to be done
correctly.
Any suggestions?
Michael Hockstein
This is covered in section 4.8 in the Readme file that comes with the
product .
To install database diagram support in SQL Server Management Studio,
databases must be in SQL Server 2005 database compatibility level. Database
compatibility level can be reset after diagram support is installed. To
create database diagrams, change the database compatibility level to 2005,
install database diagram support, and then return the database to the
desired database compatibility level. For more information, search for
"sp_dbcmptlevel" in SQL Server Books Online.
So, run the following statement and try the diagram again.
EXEC sp_dbcmptlevel database_name, 90
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"michael" <howlinghound@.nospam.nospam> wrote in message
news:EB3D155A-EC71-4854-BAC6-7B14BE038586@.microsoft.com...
>I have a SQL 2000 database that I imported into SQL Server 2005 by
>restoring
> the database from a backup. I carefully re-did the Users to make sure
> there
> were corresponding Logins.
> I want to create a Database Diagram using the Microsoft SQL Server
> Management Studio. When I right click on the Database Diagrams node
> within
> the correct database I get the following error.
> Database diagram support objects cannot be installed because this database
> does not have a valid owner. To continue, first use the Files page of the
> Database Properties dialog box or the ALTE AUTHORIZATION statement to set
> the
> database owner to a valid login, then add the database diagram support
> objects.
> Now I did both of these suggestions and then looked at the owner of the
> database, who in fact is a valid Login with sysadmin privileges.
> I'm not sure where to go from here since everything seems to be done
> correctly.
> Any suggestions?
> --
> Michael Hockstein
|||Fantastic. Worked like a charm. Could you also give me a clue as to where to
find the Readme file? I looked in the SQL Server directory and subs but
couldn't locate it. This would be a good one to keep on file.
Michael Hockstein
"Gail Erickson [MS]" wrote:
> This is covered in section 4.8 in the Readme file that comes with the
> product .
> To install database diagram support in SQL Server Management Studio,
> databases must be in SQL Server 2005 database compatibility level. Database
> compatibility level can be reset after diagram support is installed. To
> create database diagrams, change the database compatibility level to 2005,
> install database diagram support, and then return the database to the
> desired database compatibility level. For more information, search for
> "sp_dbcmptlevel" in SQL Server Books Online.
> So, run the following statement and try the diagram again.
> EXEC sp_dbcmptlevel database_name, 90
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:EB3D155A-EC71-4854-BAC6-7B14BE038586@.microsoft.com...
>
>
|||michael (howlinghound@.nospam.nospam) writes:
> Fantastic. Worked like a charm. Could you also give me a clue as to
> where to find the Readme file? I looked in the SQL Server directory and
> subs but couldn't locate it. This would be a good one to keep on file.
I happened to have the SQL 2005 server DVD in my player, and I found a
Readme in G:\SQL Server x86\Servers\ReadmeSQL2005.htm. There seem to be
one in every top-level setup directory.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||In addition to Erland's response, you can also find the readme here:
http://support.microsoft.com/default...b;en-us;910228
Also, the April update to Books Online will contain this information in the
various topics on database diagrams.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"michael" <howlinghound@.nospam.nospam> wrote in message
news:4885DE6E-B80F-4367-9B2E-D772F090CD7B@.microsoft.com...[vbcol=seagreen]
> Fantastic. Worked like a charm. Could you also give me a clue as to where
> to
> find the Readme file? I looked in the SQL Server directory and subs but
> couldn't locate it. This would be a good one to keep on file.
>
> --
> Michael Hockstein
>
> "Gail Erickson [MS]" wrote:
|||OK! Thanks for the tip.
Michael Hockstein
"Erland Sommarskog" wrote:
> michael (howlinghound@.nospam.nospam) writes:
> I happened to have the SQL 2005 server DVD in my player, and I found a
> Readme in G:\SQL Server x86\Servers\ReadmeSQL2005.htm. There seem to be
> one in every top-level setup directory.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
|||Thanks Gail!
Michael Hockstein
"Gail Erickson [MS]" wrote:
> In addition to Erland's response, you can also find the readme here:
> http://support.microsoft.com/default...b;en-us;910228
> Also, the April update to Books Online will contain this information in the
> various topics on database diagrams.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:4885DE6E-B80F-4367-9B2E-D772F090CD7B@.microsoft.com...
>
>
Wednesday, March 7, 2012
Database Detach - orphan users
I've used the sp_'s from this article:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?
Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>
|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> database. Did you get any
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?
Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>
|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> database. Did you get any
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
Database Detach - orphan users
I've used the sp_'s from this article:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/defaul...scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?Possibly logins already existed with the same name on the destination databa
se. Did you get any
errors from step 5? That would explain it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...[vbcol
=seagreen]
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/defaul...scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>[/vbcol]|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>|||Then I have no ideas I'm afraid. It should not happen. I guess you could com
pare the file produced
by sp_help_revlogins with the logins on the originating system and with sysu
sers on both originating
server as well as the restored database. That should give you the whole pict
ure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...seagreen">
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> database. Did you get any
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/defaul...scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?Possibly logins already existed with the same name on the destination databa
se. Did you get any
errors from step 5? That would explain it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...[vbcol
=seagreen]
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/defaul...scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>[/vbcol]|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>|||Then I have no ideas I'm afraid. It should not happen. I guess you could com
pare the file produced
by sp_help_revlogins with the logins on the originating system and with sysu
sers on both originating
server as well as the restored database. That should give you the whole pict
ure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...seagreen">
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> database. Did you get any
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
Database Detach - orphan users
I've used the sp_'s from this article:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> > I've used the sp_'s from this article:
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> > And still got orphaned users.
> >
> > Here's what I did:
> >
> > 1. Script users on master server with sp_'s in KB article.
> > 2. Detach
> > 3. FTP
> > 4. Attach
> > 5. Load users from sp_'s output
> >
> > user databases still had no link to sql server logins
> > (not using windows auth by the way, only sql server logins)
> >
> > I had to use sp_change_users_login but I should not have had to.
> > What went wrong ?
> >
> >
> >
>|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
>> Possibly logins already existed with the same name on the destination
> database. Did you get any
>> errors from step 5? That would explain it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ben" <null@.void.com> wrote in message
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>> > I've used the sp_'s from this article:
>> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
>> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
>> > And still got orphaned users.
>> >
>> > Here's what I did:
>> >
>> > 1. Script users on master server with sp_'s in KB article.
>> > 2. Detach
>> > 3. FTP
>> > 4. Attach
>> > 5. Load users from sp_'s output
>> >
>> > user databases still had no link to sql server logins
>> > (not using windows auth by the way, only sql server logins)
>> >
>> > I had to use sp_change_users_login but I should not have had to.
>> > What went wrong ?
>> >
>> >
>> >
>
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> > I've used the sp_'s from this article:
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> > And still got orphaned users.
> >
> > Here's what I did:
> >
> > 1. Script users on master server with sp_'s in KB article.
> > 2. Detach
> > 3. FTP
> > 4. Attach
> > 5. Load users from sp_'s output
> >
> > user databases still had no link to sql server logins
> > (not using windows auth by the way, only sql server logins)
> >
> > I had to use sp_change_users_login but I should not have had to.
> > What went wrong ?
> >
> >
> >
>|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
>> Possibly logins already existed with the same name on the destination
> database. Did you get any
>> errors from step 5? That would explain it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ben" <null@.void.com> wrote in message
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>> > I've used the sp_'s from this article:
>> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
>> > http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
>> > And still got orphaned users.
>> >
>> > Here's what I did:
>> >
>> > 1. Script users on master server with sp_'s in KB article.
>> > 2. Detach
>> > 3. FTP
>> > 4. Attach
>> > 5. Load users from sp_'s output
>> >
>> > user databases still had no link to sql server logins
>> > (not using windows auth by the way, only sql server logins)
>> >
>> > I had to use sp_change_users_login but I should not have had to.
>> > What went wrong ?
>> >
>> >
>> >
>
Database Design setup
I have an application that lets users upload pictures. These pictures are
stored in different folders depending on the user ID (of the user that it
belongs to). The path can be figured out dynamically by knowing the user
ID. However, the programmer that's coding my application has decided to
save the full path into the database.
Upside that I see is, if there's a huge load of traffic I can see how it may
be good for performance, since you don't need to dynamically figure out the
path.
The downside though, is that this leads to database bloat, and I'm not sure
if that will cause the database performance to decrease. Also, whenever the
system path is updated, a script needs to go through the database and update
everything to reflect the new path.
So, if you can give me your input on this, I'd greatly appreciate it. Is
this a good, or bad way of designing a database?Sounds like you only need to store the full path once per user. It seems
sensible to put the path in a Users table in your database. Storing the full
path against every picture would be inefficient and also may be inconvenient
if you ever need to change the file location.
--
David Portas
SQL Server MVP
--|||I agree with David, Save the directory in the User row... If you figure our
the path on the fly based on the userid, your are locked into the file
location. If you simply store it in the database, you can change it very
easily.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> I have an application that lets users upload pictures. These pictures are
> stored in different folders depending on the user ID (of the user that it
> belongs to). The path can be figured out dynamically by knowing the user
> ID. However, the programmer that's coding my application has decided to
> save the full path into the database.
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
> The downside though, is that this leads to database bloat, and I'm not
sure
> if that will cause the database performance to decrease. Also, whenever
the
> system path is updated, a script needs to go through the database and
update
> everything to reflect the new path.
> So, if you can give me your input on this, I'd greatly appreciate it. Is
> this a good, or bad way of designing a database?
>
>|||"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
is the root of all evil.
Make it work right, then make it work fast (or small). It's surprising how
infrequently the second step is required.|||> Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
> is the root of all evil.
> Make it work right, then make it work fast (or small). It's surprising how
> infrequently the second step is required.
I've heard different schools of thought though. One says that hardcoding
the path makes it quicker because #1, there's no need for an additional
query to get the path variables, and #2, there's no additional processing
needed to put it all together. And if an update were ever needed all I need
is one query to update them all.
However, it does add to the database bloat. Does that affect performance?|||> However, it does add to the database bloat. Does that affect performance?
Yes, which is why your argument doesn't make much sense.
--
David Portas
SQL Server MVP
--|||> Yes, which is why your argument doesn't make much sense.
Can you show me an article that talks about this downside?
stored in different folders depending on the user ID (of the user that it
belongs to). The path can be figured out dynamically by knowing the user
ID. However, the programmer that's coding my application has decided to
save the full path into the database.
Upside that I see is, if there's a huge load of traffic I can see how it may
be good for performance, since you don't need to dynamically figure out the
path.
The downside though, is that this leads to database bloat, and I'm not sure
if that will cause the database performance to decrease. Also, whenever the
system path is updated, a script needs to go through the database and update
everything to reflect the new path.
So, if you can give me your input on this, I'd greatly appreciate it. Is
this a good, or bad way of designing a database?Sounds like you only need to store the full path once per user. It seems
sensible to put the path in a Users table in your database. Storing the full
path against every picture would be inefficient and also may be inconvenient
if you ever need to change the file location.
--
David Portas
SQL Server MVP
--|||I agree with David, Save the directory in the User row... If you figure our
the path on the fly based on the userid, your are locked into the file
location. If you simply store it in the database, you can change it very
easily.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> I have an application that lets users upload pictures. These pictures are
> stored in different folders depending on the user ID (of the user that it
> belongs to). The path can be figured out dynamically by knowing the user
> ID. However, the programmer that's coding my application has decided to
> save the full path into the database.
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
> The downside though, is that this leads to database bloat, and I'm not
sure
> if that will cause the database performance to decrease. Also, whenever
the
> system path is updated, a script needs to go through the database and
update
> everything to reflect the new path.
> So, if you can give me your input on this, I'd greatly appreciate it. Is
> this a good, or bad way of designing a database?
>
>|||"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
is the root of all evil.
Make it work right, then make it work fast (or small). It's surprising how
infrequently the second step is required.|||> Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
> is the root of all evil.
> Make it work right, then make it work fast (or small). It's surprising how
> infrequently the second step is required.
I've heard different schools of thought though. One says that hardcoding
the path makes it quicker because #1, there's no need for an additional
query to get the path variables, and #2, there's no additional processing
needed to put it all together. And if an update were ever needed all I need
is one query to update them all.
However, it does add to the database bloat. Does that affect performance?|||> However, it does add to the database bloat. Does that affect performance?
Yes, which is why your argument doesn't make much sense.
--
David Portas
SQL Server MVP
--|||> Yes, which is why your argument doesn't make much sense.
Can you show me an article that talks about this downside?
Database Design setup
I have an application that lets users upload pictures. These pictures are
stored in different folders depending on the user ID (of the user that it
belongs to). The path can be figured out dynamically by knowing the user
ID. However, the programmer that's coding my application has decided to
save the full path into the database.
Upside that I see is, if there's a huge load of traffic I can see how it may
be good for performance, since you don't need to dynamically figure out the
path.
The downside though, is that this leads to database bloat, and I'm not sure
if that will cause the database performance to decrease. Also, whenever the
system path is updated, a script needs to go through the database and update
everything to reflect the new path.
So, if you can give me your input on this, I'd greatly appreciate it. Is
this a good, or bad way of designing a database?
Sounds like you only need to store the full path once per user. It seems
sensible to put the path in a Users table in your database. Storing the full
path against every picture would be inefficient and also may be inconvenient
if you ever need to change the file location.
David Portas
SQL Server MVP
|||I agree with David, Save the directory in the User row... If you figure our
the path on the fly based on the userid, your are locked into the file
location. If you simply store it in the database, you can change it very
easily.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> I have an application that lets users upload pictures. These pictures are
> stored in different folders depending on the user ID (of the user that it
> belongs to). The path can be figured out dynamically by knowing the user
> ID. However, the programmer that's coding my application has decided to
> save the full path into the database.
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
> The downside though, is that this leads to database bloat, and I'm not
sure
> if that will cause the database performance to decrease. Also, whenever
the
> system path is updated, a script needs to go through the database and
update
> everything to reflect the new path.
> So, if you can give me your input on this, I'd greatly appreciate it. Is
> this a good, or bad way of designing a database?
>
>
|||"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
is the root of all evil.
Make it work right, then make it work fast (or small). It's surprising how
infrequently the second step is required.
|||> Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
> is the root of all evil.
> Make it work right, then make it work fast (or small). It's surprising how
> infrequently the second step is required.
I've heard different schools of thought though. One says that hardcoding
the path makes it quicker because #1, there's no need for an additional
query to get the path variables, and #2, there's no additional processing
needed to put it all together. And if an update were ever needed all I need
is one query to update them all.
However, it does add to the database bloat. Does that affect performance?
|||> However, it does add to the database bloat. Does that affect performance?
Yes, which is why your argument doesn't make much sense.
David Portas
SQL Server MVP
|||> Yes, which is why your argument doesn't make much sense.
Can you show me an article that talks about this downside?
stored in different folders depending on the user ID (of the user that it
belongs to). The path can be figured out dynamically by knowing the user
ID. However, the programmer that's coding my application has decided to
save the full path into the database.
Upside that I see is, if there's a huge load of traffic I can see how it may
be good for performance, since you don't need to dynamically figure out the
path.
The downside though, is that this leads to database bloat, and I'm not sure
if that will cause the database performance to decrease. Also, whenever the
system path is updated, a script needs to go through the database and update
everything to reflect the new path.
So, if you can give me your input on this, I'd greatly appreciate it. Is
this a good, or bad way of designing a database?
Sounds like you only need to store the full path once per user. It seems
sensible to put the path in a Users table in your database. Storing the full
path against every picture would be inefficient and also may be inconvenient
if you ever need to change the file location.
David Portas
SQL Server MVP
|||I agree with David, Save the directory in the User row... If you figure our
the path on the fly based on the userid, your are locked into the file
location. If you simply store it in the database, you can change it very
easily.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> I have an application that lets users upload pictures. These pictures are
> stored in different folders depending on the user ID (of the user that it
> belongs to). The path can be figured out dynamically by knowing the user
> ID. However, the programmer that's coding my application has decided to
> save the full path into the database.
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
> The downside though, is that this leads to database bloat, and I'm not
sure
> if that will cause the database performance to decrease. Also, whenever
the
> system path is updated, a script needs to go through the database and
update
> everything to reflect the new path.
> So, if you can give me your input on this, I'd greatly appreciate it. Is
> this a good, or bad way of designing a database?
>
>
|||"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
is the root of all evil.
Make it work right, then make it work fast (or small). It's surprising how
infrequently the second step is required.
|||> Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
> is the root of all evil.
> Make it work right, then make it work fast (or small). It's surprising how
> infrequently the second step is required.
I've heard different schools of thought though. One says that hardcoding
the path makes it quicker because #1, there's no need for an additional
query to get the path variables, and #2, there's no additional processing
needed to put it all together. And if an update were ever needed all I need
is one query to update them all.
However, it does add to the database bloat. Does that affect performance?
|||> However, it does add to the database bloat. Does that affect performance?
Yes, which is why your argument doesn't make much sense.
David Portas
SQL Server MVP
|||> Yes, which is why your argument doesn't make much sense.
Can you show me an article that talks about this downside?
Database Design Questions
I'm trying to design a database that allows the users to give each individual client/company unlimited addresses and salutations. I can build the design that accommodates this, but I cannot figure out how to handle them knowing which salutation to use with a mailing they might do to the clients.
I have put the Company Name and Position (title) in the address table so that when doing a mailing the company name and title are associated with the company address being mailed too. But again, I'm not sure how they would choose a salutation if they have many choices.
Looking for any of your thoughts or suggestions.
Thank you,I'm not following you 100%, but I'll take a stab at something that might accomodate your needs:
COMPANY
id_company
company_name
COMPANY_ADDRESS
id_company_address
id_company
address_company_name
address1
address2
city
state
zip
COMPANY_SALUTATION
id_company_salutation
id_company
salutation
COMPANY_MAILING
id_company_mailing
id_company
id_company_address
id_company_salutation
When they are about to do a mailing, the COMPANY_MAILING table should be populated as desired for each company involved in the mailing.
Terri
Saturday, February 25, 2012
Database design question
I working on a project that will let users essentially create design and requirement documents online and keep the information stored in a database.
I was thinking that the model would be similar to that of a book online.
I was thinking the basic schema would be something like
Table - Project
Table - Author.
Table - Topic
Table - Sub Topic
A project can have many topics, A project can have many authors etc.
A topic can have many sub topics etc.
So I was wondering if anyone knew of some sample schemas that may support that functionality.
The examples in the link you provided are good but I was looking for something more specific. Such as if I was going to build an online
store, I would want to see a sample database from another online store to see how/what data is stored.
Thanks,
|||
I was thinking that the model would be similar to that of a book online.
I was thinking the basic schema would be something like
Table - Project
Table - Author.
Table - Topic
Table - Sub Topic
A project can have many topics, A project can have many authors etc.
A topic can have many sub topics etc.
So I was wondering if anyone knew of some sample schemas that may support that functionality.
Try this link and download the PPT slide to get started. You may not need four tables because it is files and association. Hope this helps.
http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html
The examples in the link you provided are good but I was looking for something more specific. Such as if I was going to build an online
store, I would want to see a sample database from another online store to see how/what data is stored.
Thanks,
|||
SQL Server 2000 beginner's Guide by Dusan Petkovic has a project database and it is one of the better SQL Server books for Developers. But I would look at Pubs database to get started it is only nine tables so it will be easy to modify the create table statement. Hope this helps.
database design question
Hello all
I have been asked to create a database that has a users table, news table and a groups table. I'm new to this so I'm having a bit of difficulty mapping out the database design, and was hoping someone here could offer some input.
The idea is that the users will add their own news stories, and assign the stories to certain groups that HAVE been set (default groups) for them already like friends/work/public. But the users can ALSO create extra groups if they need to. So my problem is figuring out the best structure for the group's table.
What I have come up with is this...
Users Table
===========
UserID UserName
1 John
2 Mike
3 Sarah
Groups Table
============
GroupID Group Type UserID
1 Public 1
2 Work 1
3 Friends 1
4 Public 2
5 Work 2
6 Friends 2
7 Public 3
8 Work 3
9 Friends 3
10 FAMILY 2
(you'll notice that mike created his new group called family. The other groups will be automatically created when a new user is added to the system. Their new USERID will be assigned to a public, work and family groupID)
News Table
==========
NewsID UserID GroupID NewsStory
1 3 8 Blah, blah news...
2 3 3 Blah, blah news...
3 2 2 Blah, blah news...
4 1 2 Blah, blah news...
(Each user will be able to assign his/her news story to a certain group)
So my concern is the group's table. There's a lot of data repeating itself, but I can't think of a good way to store newly added groups by users. Is there a better way of doing this?
Thanks for your help!
Honestly, That is the best way to design it. That is the structure of how it should be done 'by the book.' I am pretty sure there is no other way.
It seems like allot of dat is repeated but you are doing it better than most becuase you are storing ID's rather than actually storing the persons name. It saves allot of space. Keep it up and you shall be a pretty good DBA anytime soon.
|||Hi
The users table and the News table looks fine -- but the Groups table structure, as you rightly observe, does not look very robust. In database design lingo it is not normalized. You would face insert, update and delete anomalies.
It is best to have just one row for each group in the groups table. Hence, you may want to have just one row each for Public, Work and Friends.
Now, you say that users can create extra groups. In this instance, Mike, for example has created the group FAMILY. Is the FAMILY group available only to Mike? Can Sarah, for example, create a FAMILY group as well? I would recommend that the latter not be the case. There should be only one group FAMILY (or better, Family) and the 'Created User' could be Mike. The row created by Mike will be available for use by all other users (think about this -- whether it matches your requirements) Thus your table design would be
GroupID, GroupDescription, CreatedUser
1 Public
2 Work
3 Friends
4 Family 2
NOTE -- The CreatedUser column could be left null (by convention) for seeded groups (default groups).
Hope this helps and do let me know your views on the suggestion. It may not be the best possible solution for your circumstances.
Good Luck!
Fouwaaz
|||Hi,
Your structure is good. Don't change anything. You can create T-SQL script for creation default groups, so you don't need to do it manually.
Hello all.
Thank you all for your input! I'm going to leave it as is and see how it handles.
Thanks again!
|||
...Sorry sql is not one of my strong points either
If I wanted to select all storys by a user I would user...
SELECT
N.[NewsStory]
FROM
[NewsTable] AS N
INNER JOIN [UsersTable] AS U ON U.[User_ID] = N.[UserID]
WHERE
N.[UserID] = 'number'
but how would I go about selecting only storys for a certain group as well?
What I mean is, how would I select a users newsstorys for say friends only?
thanks again
|||
just do another inner join like you have it. Are you using Visual Studio for this? they have a query builder built in to the program that would allow you to build awesome queries.
Sunday, February 19, 2012
Database Design - 3000+ users
We are in the process to develop a new VB6/SQL 2000 application (do
not ask why not .net, our company policy is to use VB6), which will
have over 3000 users to access the database (50GB data) and perform
heavy add/update on one table. Without the privilege to have a
better/faster server, we have to design database tables in a way to
increase the performance (on updates).
My boss wants to partition the data into different databases based on
defferent users groups (for examples, use users' department). Since
the data now on different databases, the updates for different user
groups would be faster. My concern is that these different databases
will hard to maintain. For example, a same query/stored procedure will
either reside on different
databases or coded with logic to go to different databases before
executing a update. It may better to have one database with multiple
(same structure) tables to store data for different user groups, then
use a (partitioned) view to link all tables (with check constraint
based on user's department number). When update, it would go to
different tables (hence reduce the traffic to tables and increase the
performance).
Thank you in advance for your suggestions.
Yang ZhongWhat makes you think since you have several db's the updates will be faster?
All the databases share the same resources and it doesn't sound like they
will be updating the same row anyway.
--
Andrew J. Kelly SQL MVP
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.com...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong|||(a) Do these users only access the data that they Insert/Update/Delete? Or,
can they access the data created by other user groups ~ different database?
(b) "perform heavy add/update on one table. Without the privilege to have a".
What kind (vertical / industry) of application is it ? (Different user
groups all updating a single table)
(c) Since all your databases (based on the different user groups) will
reside in one server - there will be no performance benefit when you use
partitioned views.
BUT, is it posisble to have each user group's database sitting on a
different physical disk with its own controller? If YES, then you will get
some of the performance benfits of a partitioned view.
Cheers!|||Since SQL can do row level locking, I suspect there will not be a lot of
locking issues for updates, (assuming single row updates), on a properly
indexed table where the Primary Key is used in the where clause.
Therefore, there may be no reason to go to all of the trouble you are
talking about.
It is certainly worth doing some testing to see!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.com...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong
not ask why not .net, our company policy is to use VB6), which will
have over 3000 users to access the database (50GB data) and perform
heavy add/update on one table. Without the privilege to have a
better/faster server, we have to design database tables in a way to
increase the performance (on updates).
My boss wants to partition the data into different databases based on
defferent users groups (for examples, use users' department). Since
the data now on different databases, the updates for different user
groups would be faster. My concern is that these different databases
will hard to maintain. For example, a same query/stored procedure will
either reside on different
databases or coded with logic to go to different databases before
executing a update. It may better to have one database with multiple
(same structure) tables to store data for different user groups, then
use a (partitioned) view to link all tables (with check constraint
based on user's department number). When update, it would go to
different tables (hence reduce the traffic to tables and increase the
performance).
Thank you in advance for your suggestions.
Yang ZhongWhat makes you think since you have several db's the updates will be faster?
All the databases share the same resources and it doesn't sound like they
will be updating the same row anyway.
--
Andrew J. Kelly SQL MVP
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.com...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong|||(a) Do these users only access the data that they Insert/Update/Delete? Or,
can they access the data created by other user groups ~ different database?
(b) "perform heavy add/update on one table. Without the privilege to have a".
What kind (vertical / industry) of application is it ? (Different user
groups all updating a single table)
(c) Since all your databases (based on the different user groups) will
reside in one server - there will be no performance benefit when you use
partitioned views.
BUT, is it posisble to have each user group's database sitting on a
different physical disk with its own controller? If YES, then you will get
some of the performance benfits of a partitioned view.
Cheers!|||Since SQL can do row level locking, I suspect there will not be a lot of
locking issues for updates, (assuming single row updates), on a properly
indexed table where the Primary Key is used in the where clause.
Therefore, there may be no reason to go to all of the trouble you are
talking about.
It is certainly worth doing some testing to see!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.com...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong
Database Design - 3000+ users
We are in the process to develop a new VB6/SQL 2000 application (do
not ask why not .net, our company policy is to use VB6), which will
have over 3000 users to access the database (50GB data) and perform
heavy add/update on one table. Without the privilege to have a
better/faster server, we have to design database tables in a way to
increase the performance (on updates).
My boss wants to partition the data into different databases based on
defferent users groups (for examples, use users' department). Since
the data now on different databases, the updates for different user
groups would be faster. My concern is that these different databases
will hard to maintain. For example, a same query/stored procedure will
either reside on different
databases or coded with logic to go to different databases before
executing a update. It may better to have one database with multiple
(same structure) tables to store data for different user groups, then
use a (partitioned) view to link all tables (with check constraint
based on user's department number). When update, it would go to
different tables (hence reduce the traffic to tables and increase the
performance).
Thank you in advance for your suggestions.
Yang Zhong
What makes you think since you have several db's the updates will be faster?
All the databases share the same resources and it doesn't sound like they
will be updating the same row anyway.
Andrew J. Kelly SQL MVP
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.c om...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong
|||Since SQL can do row level locking, I suspect there will not be a lot of
locking issues for updates, (assuming single row updates), on a properly
indexed table where the Primary Key is used in the where clause.
Therefore, there may be no reason to go to all of the trouble you are
talking about.
It is certainly worth doing some testing to see!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.c om...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong
not ask why not .net, our company policy is to use VB6), which will
have over 3000 users to access the database (50GB data) and perform
heavy add/update on one table. Without the privilege to have a
better/faster server, we have to design database tables in a way to
increase the performance (on updates).
My boss wants to partition the data into different databases based on
defferent users groups (for examples, use users' department). Since
the data now on different databases, the updates for different user
groups would be faster. My concern is that these different databases
will hard to maintain. For example, a same query/stored procedure will
either reside on different
databases or coded with logic to go to different databases before
executing a update. It may better to have one database with multiple
(same structure) tables to store data for different user groups, then
use a (partitioned) view to link all tables (with check constraint
based on user's department number). When update, it would go to
different tables (hence reduce the traffic to tables and increase the
performance).
Thank you in advance for your suggestions.
Yang Zhong
What makes you think since you have several db's the updates will be faster?
All the databases share the same resources and it doesn't sound like they
will be updating the same row anyway.
Andrew J. Kelly SQL MVP
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.c om...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong
|||Since SQL can do row level locking, I suspect there will not be a lot of
locking issues for updates, (assuming single row updates), on a properly
indexed table where the Primary Key is used in the where clause.
Therefore, there may be no reason to go to all of the trouble you are
talking about.
It is certainly worth doing some testing to see!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"yang zhong" <Yang_Zhong@.bcbstx.com> wrote in message
news:44d12b2d.0408171318.74348665@.posting.google.c om...
> We are in the process to develop a new VB6/SQL 2000 application (do
> not ask why not .net, our company policy is to use VB6), which will
> have over 3000 users to access the database (50GB data) and perform
> heavy add/update on one table. Without the privilege to have a
> better/faster server, we have to design database tables in a way to
> increase the performance (on updates).
> My boss wants to partition the data into different databases based on
> defferent users groups (for examples, use users' department). Since
> the data now on different databases, the updates for different user
> groups would be faster. My concern is that these different databases
> will hard to maintain. For example, a same query/stored procedure will
> either reside on different
> databases or coded with logic to go to different databases before
> executing a update. It may better to have one database with multiple
> (same structure) tables to store data for different user groups, then
> use a (partitioned) view to link all tables (with check constraint
> based on user's department number). When update, it would go to
> different tables (hence reduce the traffic to tables and increase the
> performance).
> Thank you in advance for your suggestions.
> Yang Zhong
Subscribe to:
Posts (Atom)