ALTER MASTER KEY REGENERATE Command

Hi,
If I forgot what was the password used to create the Database Master key, I
could just use this command to regenerate a new one:
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'newpassword'
Then what is the use of the following command which requires me to provide a
password?
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101
)
CREATE MASTER KEY
ENCRYPTION BY PASSWORD =
'23987hxJKL969#ghf0%94467GRkjg5k3fd117r$
$#1946kcj$n44nhdlj'
Does this means that the Sys Admin (SA) will have divine rights to
regenerate the Database Master key without the need to know the original
password used to create the key?
Is there any proper guidelines as to how secure a database using the SQL
Server 2005 encryption capability?The database master key (DbMK) has an encryption by password and by default
it also has an encryption by the service master key (SMK). The latter
encryption allows a sysadmin easy access to any data encrypted by the SMK,
whether he knows the password or not. However, if you drop the SMK
encryption (ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY), then
the DbMK can only be used by people that know the DbMK password. The ALTER
statement that you mentioned below can be used by a sysadmin only if the
DbMK has a SMK encryption; otherwise, the sysadmin would need to know the
DbMK password to open the DbMK first, before he could execute the statement.
Note that using a password encryption for the DbMK doesn't really mean that
you lock out the sysadmin from accessing the data - it's just that he
doesn't have direct SQL Server access to the DbMK anymore. For example, in
most cases, a sysadmin is also a local administrator, so he could simply
debug the server process to get the DbMK password at the moment you execute
the OPEN MASTER KEY statement.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"et_ck" <etck@.discussions.microsoft.com> wrote in message
news:7BD34F69-AF85-4057-9341-6E117B4FD532@.microsoft.com...
> Hi,
> If I forgot what was the password used to create the Database Master key,
> I
> could just use this command to regenerate a new one:
> ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'newpassword'
> Then what is the use of the following command which requires me to provide
> a
> password?
> IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id =
> 101)
> CREATE MASTER KEY
> ENCRYPTION BY PASSWORD =
> '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$
$#1946kcj$n44nhdlj'
> Does this means that the Sys Admin (SA) will have divine rights to
> regenerate the Database Master key without the need to know the original
> password used to create the key?
> Is there any proper guidelines as to how secure a database using the SQL
> Server 2005 encryption capability?
>

Alter Index with REBUILD on master database?

SQL Server 2005:
We plan to use the alter index with rebuild syntax to rebuild our indexes
weekly in a job. Should master and msdb tables be included? I have no
interest in doing it manually a couple times per year if it needs it.
Thanks,
MarkI remember asking the same thing in a SQL 2000 forum a long time ago and the
consensus was you never need to include any of the system databases in
reindexing / update stats. I would gather the same is applicable to SQL
2005.
HTH,
Rubens
"Mark" <mark@.idonotlikespam.com> wrote in message
news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2005:
> We plan to use the alter index with rebuild syntax to rebuild our indexes
> weekly in a job. Should master and msdb tables be included? I have no
> interest in doing it manually a couple times per year if it needs it.
> Thanks,
> Mark
>|||Why not?
"Rubens" <rubensrose@.hotmail.com> wrote in message
news:uUmDCKBpIHA.4912@.TK2MSFTNGP03.phx.gbl...
>I remember asking the same thing in a SQL 2000 forum a long time ago and
>the consensus was you never need to include any of the system databases in
>reindexing / update stats. I would gather the same is applicable to SQL
>2005.
> HTH,
> Rubens
> "Mark" <mark@.idonotlikespam.com> wrote in message
> news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2005:
>> We plan to use the alter index with rebuild syntax to rebuild our indexes
>> weekly in a job. Should master and msdb tables be included? I have no
>> interest in doing it manually a couple times per year if it needs it.
>> Thanks,
>> Mark|||Because in SQL2005 there shouldn't be any tables (of consequence) in the
master database that you can actually run UPDATE STATISTICS or rebuild
indexes.
Linchi
"Mark" wrote:
> Why not?
> "Rubens" <rubensrose@.hotmail.com> wrote in message
> news:uUmDCKBpIHA.4912@.TK2MSFTNGP03.phx.gbl...
> >I remember asking the same thing in a SQL 2000 forum a long time ago and
> >the consensus was you never need to include any of the system databases in
> >reindexing / update stats. I would gather the same is applicable to SQL
> >2005.
> >
> > HTH,
> > Rubens
> >
> > "Mark" <mark@.idonotlikespam.com> wrote in message
> > news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
> >> SQL Server 2005:
> >>
> >> We plan to use the alter index with rebuild syntax to rebuild our indexes
> >> weekly in a job. Should master and msdb tables be included? I have no
> >> interest in doing it manually a couple times per year if it needs it.
> >>
> >> Thanks,
> >> Mark
> >>
>
>

Allocation Error in Master DB

Hi,
I wanted to find out if there is a way to fix Master DB. I ran DBCC and it
shows some allocation errors. I understand I can run Repaid_Allow_Data_Loss
with other databases but not with Master, Is this correct? Is there another
way to fix the DB rather than having to restore it?
Thank you.1. change sql server to single mode
2.stop sql server service
3.restore database
: RESTORE DATABASE master FROM DISK='C:\SQLDATA\...\...'
cheers|||Thank you but all my backups have the same errors as well so I need fix it
somehow.
"jongwoo" <jongwoo@.discussions.microsoft.com> wrote in message
news:DE879B08-EDAC-4B05-BDFA-0CBAFC9E5F28@.microsoft.com...
> 1. change sql server to single mode
> 2.stop sql server service
> 3.restore database
> : RESTORE DATABASE master FROM DISK='C:\SQLDATA\...\...'
> cheers|||If you give as much error message info as possible, we should be more able t
o
help out.

Allocation Error in Master DB

Hi,
I wanted to find out if there is a way to fix Master DB. I ran DBCC and it
shows some allocation errors. I understand I can run Repaid_Allow_Data_Loss
with other databases but not with Master, Is this correct? Is there another
way to fix the DB rather than having to restore it?
Thank you.1. change sql server to single mode
2.stop sql server service
3.restore database
: RESTORE DATABASE master FROM DISK='C:\SQLDATA\...\...'
cheers|||Thank you but all my backups have the same errors as well so I need fix it
somehow.
"jongwoo" <jongwoo@.discussions.microsoft.com> wrote in message
news:DE879B08-EDAC-4B05-BDFA-0CBAFC9E5F28@.microsoft.com...
> 1. change sql server to single mode
> 2.stop sql server service
> 3.restore database
> : RESTORE DATABASE master FROM DISK='C:\SQLDATA\...\...'
> cheers|||If you give as much error message info as possible, we should be more able to
help out.

All stored procedures in the master database disappear? Help!

This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a lot
system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
I would greatly appreciate any help.
Bing
I doubt they are really gone since. SEM uses many of those procedures to do
it's work and I doubt SEM would even work if all the procs were gone. Of
course, I've never tried... <g>
Hopefully it's something this simple. I don't suppose you have closed and
reopened SEM?
BTW... why are you modifying procedures in master in the first place. You
should not be touching system procedures and for the most part you shouldn't
be placing 'user' procedures there.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"bing" <bing@.discussions.microsoft.com> wrote in message
news:212BA431-BCAE-425A-BF02-277B235DE3DA@.microsoft.com...
> This morning when I went to modify a stored procedure in the master
database, I noticed all the stored procedures were gone! When I clicked
'Stored Procedures', nothing returned on the right window in SQL Server
Enterprise Manager. There should be a lot system and user defined stored
procedures there. Extended Stored Procedures show up fine though. Is this
a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing
|||From query analyzer run the following query
select * from master..sysobjects where xtype = 'S'
It will list your system sp's.
You can then be sure if they are deleted or not.
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing
|||Sorry S for system tables P for SP's you're problem with sp's then type
xtype = 'P'
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing
|||It's getting even weirder. I closed and reopened SEM one more time, now I can see all the stored procedures in the master database, but all the views become invisible this time. DBCC checkdb on master shows no error. Would restarting
SQL server and SQL agent help clean up some weirdness?
Bing
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing

All stored procedures in the master database disappear? Help!

This morning when I went to modify a stored procedure in the master database
, I noticed all the stored procedures were gone! When I clicked 'Stored Pro
cedures', nothing returned on the right window in SQL Server Enterprise Mana
ger. There should be a lot
system and user defined stored procedures there. Extended Stored Procedure
s show up fine though. Is this a disaster? What might have happened?
I would greatly appreciate any help.
BingI doubt they are really gone since. SEM uses many of those procedures to do
it's work and I doubt SEM would even work if all the procs were gone. Of
course, I've never tried... <g>
Hopefully it's something this simple. I don't suppose you have closed and
reopened SEM?
BTW... why are you modifying procedures in master in the first place. You
should not be touching system procedures and for the most part you shouldn't
be placing 'user' procedures there.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"bing" <bing@.discussions.microsoft.com> wrote in message
news:212BA431-BCAE-425A-BF02-277B235DE3DA@.microsoft.com...
> This morning when I went to modify a stored procedure in the master
database, I noticed all the stored procedures were gone! When I clicked
'Stored Procedures', nothing returned on the right window in SQL Server
Enterprise Manager. There should be a lot system and user defined stored
procedures there. Extended Stored Procedures show up fine though. Is this
a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing|||From query analyzer run the following query
select * from master..sysobjects where xtype = 'S'
It will list your system sp's.
You can then be sure if they are deleted or not.
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I no
ticed all the stored procedures were gone! When I clicked 'Stored Procedures', noth
ing returned on the right window in SQL Server Enterprise Manager. There should be
a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fin
e though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing|||Sorry S for system tables P for SP's you're problem with sp's then type
xtype = 'P'
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I no
ticed all the stored procedures were gone! When I clicked 'Stored Procedures', noth
ing returned on the right window in SQL Server Enterprise Manager. There should be
a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fin
e though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing|||It's getting even weirder. I closed and reopened SEM one more time, now I c
an see all the stored procedures in the master database, but all the views b
ecome invisible this time. DBCC checkdb on master shows no error. Would r
estarting
SQL server and SQL agent help clean up some weirdness?
Bing
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I no
ticed all the stored procedures were gone! When I clicked 'Stored Procedures', noth
ing returned on the right window in SQL Server Enterprise Manager. There should be
a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fin
e though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing

All from one table and all from another

I'm hoping someone could help me write the SQL code to solve this problem

I have two tables and a master one if I need it. All tables can be linked with the Master_ID. Table1 and Table2 can each have 0, one or many records for each Master_ID. The other column in the two tables is a number representing a volumn of two different fluids.

Table1
Master_id
Volume1_amount

Table2
Master_id
Volume2_amount

Master
Master_id
Master_name

How can I return all of the rows in Table1 and all of the rows in Table2 for each Master_id such that it looks like this if Table1 has 2 records and Table2 has 1 record for a given Master_id and then Table2 has 2 records and Table1 has 0 for a differnt Master_id

Master_id Volume1_amount Volume2_amount
100235 25.3 m 62.1 m
100235 22.0 m null
220000 null 85.66 m
220000 null 59.0 m

Any help would very much be appreciated.What are the primary keys of Table1 and Table2? What is it that links the 62.1m Table2 value to the 25.3m Table1 value rather than to the 22.0m value?|||The tables are actually temporary tables so there is/are no primary key(s) define but the Master_id is what links them all together. The master_id in Table1 will match the Master_id in Table2 which both match to Master_id in the master table|||Yes, but my other question was:

What is it that links the 62.1m Table2 value to the 25.3m Table1 value rather than to the 22.0m value?

You haven't answered that.|||Oh sorry - nothing except for which ever is first in the table. The two volumes don't relate to each other at all except that they both relate to the master_id. Make sense?|||OK, well the concept of "first in the table" is meaningless in a relational database without something to order by. What DBMS are you using? For Oracle I know a trick you can use. Otherwise, I would suggest you need to add an extra column to Table1 and Table2:

Table1
Master_id
Volume1_amount
Seq_no

Table2
Master_id
Volume2_amount
Seq_no

where Seq_no is 1 for the 1st record for each Master_id, 2 for the second etc.

Then your query becomes:

select coalesce(t1.master_id,t2.master_id), t1.volume1_amount, t2.volume2_amount
from t1
full outer join t2
on
(t1.master_id = t2.master_id
and t1.seq_no = t2.seq_no
);|||Excellent! Thank you very much