Databse role to Create Stored Procedures

Hi,
With SQL2005, I'd like to create a database role whose members could
Create/Drop stored procedures (even new ones), Select/Insert/Delete/Update
from any table of the Database, but couldn't modifiy the tables' structure.
Could someone suggest me a script?
Thanks for your help.
JN.Hi,
Assign below roles to user to read and write on tables
DB_DATAREADER
DB_DATAWRITER
This will allow the user to create procedure
GRANT CREATE PROCEDURE TO <UserName>
The createor can drop the procedure
Thanks
Hari
SQL Server MVP
"Jean-Nicolas BERGER" <JeanNicolasBERGER@.discussions.microsoft.com> wrote in
message news:203A9A00-5976-4B45-82FE-B9E31B32AEDB@.microsoft.com...
> Hi,
> With SQL2005, I'd like to create a database role whose members could
> Create/Drop stored procedures (even new ones), Select/Insert/Delete/Update
> from any table of the Database, but couldn't modifiy the tables'
> structure.
> Could someone suggest me a script?
> Thanks for your help.
> JN.

databse restore

Hello,
I have situation where I will need to restore from.
Monday -- Full backup + Transaction Log ever hour
Tuesday -- Deferential Backup +Transaction Log ever hour
Wednesday --Deferential Backup Transaction Log ever hour at 7 PM database
suspect and I will need to restore from my backup
Do I go and restore Full backup + deferential backup of Tuesday +
deferential Backup of Wednesday + Transaction log that was taken after that
deferential backup on Wednesday ?
Thanks,Hello,
I would recommend to find out first why database became suspect and fix that
problem. To restore use Monday full backup, the last differential backup
(Wednesday) and all the transaction log backups since the last differential
backup.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ron A" wrote:
> Hello,
>
> I have situation where I will need to restore from.
>
> Monday -- Full backup + Transaction Log ever hour
>
> Tuesday -- Deferential Backup +Transaction Log ever hour
>
> Wednesday --Deferential Backup Transaction Log ever hour at 7 PM database
> suspect and I will need to restore from my backup
>
> Do I go and restore Full backup + deferential backup of Tuesday +
> deferential Backup of Wednesday + Transaction log that was taken after that
> deferential backup on Wednesday ?
>
> Thanks,
>
>|||Ron
How about restoring FULL backup + LOG backup at 7PM ( I assume you took
backup log file just after a database became corrupted)
"Ron A" <omranu@.Gmail.com> wrote in message
news:er$LhgoHIHA.2100@.TK2MSFTNGP03.phx.gbl...
> Hello,
>
> I have situation where I will need to restore from.
>
> Monday -- Full backup + Transaction Log ever hour
>
> Tuesday -- Deferential Backup +Transaction Log ever hour
>
> Wednesday --Deferential Backup Transaction Log ever hour at 7 PM
> database suspect and I will need to restore from my backup
>
> Do I go and restore Full backup + deferential backup of Tuesday +
> deferential Backup of Wednesday + Transaction log that was taken after
> that deferential backup on Wednesday ?
>
> Thanks,
>
>

Databse replication

Dear All

I've made transactional replication between two SQL 2005 servers.
Everything looks fine, synchronization working fine, no errors, however size of replicated database file = 97 Mb,
on Publications server the database file size = 184 Mb.

What is wrong :S ?

Best Regards
PiotrMB?

You should be using Access|||Sounds like the equivalent of shrinking to me...|||Sounds like the equivalent of shrinking to me...

It's funny you talk about shrinkage, and your name is George|||The data on the replicated server has been defragmented. behind the scenes, the snapshot bcp's out the data from the publisher, and bcp's it in to the subscriber. This removes any whitespace created by deletes and page splits.|||That's a much more elegant way of putting it...
I had the whole "Ya know when you defrag your PC..." conversation knocking about in my head.

databse properties causes mmc to close

Please help,
I am running SQL Server 2000 on a Windows 2000 server.
Whenever I open the Enterprise Manager and I right-click on any database and
select properties, the entire console just closes. No error or alert is cre
ated.
What should I doHave a look at Event log and see if there's anything there. Also, be sure to
update your sqlserver with latest service pack (sp3a).
-oj
http://www.rac4sql.net
"john" <anonymous@.discussions.microsoft.com> wrote in message
news:4BEAD47A-C47B-47ED-83EA-6AC9E00BE098@.microsoft.com...
> Please help,
> I am running SQL Server 2000 on a Windows 2000 server.
> Whenever I open the Enterprise Manager and I right-click on any database
and select properties, the entire console just closes. No error or alert is
created.
> What should I do|||I have SP3 loaded and I've checked the event logs but there is nothing regar
ding SQL.

Databse Offline

hi everyone
Could someone please help me in following:
One of my database in SQL 2000 going Offline automatically. When i
bring it back Online its Ok for 20/30 minutes and then again appear as
Offline. I had similar problem when one of the database keep going to
'Single user' automatically.
Any idea what happening.
Thank you

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Khalid

It sounds like you have the auto-close option eneabled. You need to
disable it.
Right click on your database in EM, choose properties and then check
the options tab to see if the option is enabled.

Regards

John

Databse Link will not connect

I have a couple new servers - Windows 2003 R2 - with SQL Server 2000
SP4 installed. They are all Active/Active clustered instances (my
first ones).

I am trying to create database links to other SQL Server instances, but
there are 3 that I cannot connect to.

The dblink works if I connect to the instance as "sa", but not as my
Windows Authenticated account. We use Active Directory and I am in the
Administrator group on all of the boxes.

Of the 3 I can't get to, 1 is a cluster (Active/Passive) and the other
2 are just regular Enterprise instances. They are all SQL 2000 SP4.

I have several other instances, all SP4 on 2003 boxes and I can connect
to all of them.

Also, on the instances where the dblink does not work, I can actually
connect to these databases w/in Enterprise Manager successfully.

I am using a different port for the instances, but I have set them up
in the Client Network Utility as well and still can't connect.

I can connect via a dblink TO these new instances from the older boxes
fine.

I'm really stumped - I've had the Network people verify we don't have a
firewall issue or something.

Must be some sort of permissions problem, but I don't know what else to
check - - -

Please help!!

THANK YOU!!Not completely sure what you mean by a dblink, but what SQL permissions
do the Administrators have on each server? Are you sure the
BUILTIN/Administrators Windows group have the appropriate permissions
on the databases you want to select from?

Stu

traceable1 wrote:

Quote:

Originally Posted by

I have a couple new servers - Windows 2003 R2 - with SQL Server 2000
SP4 installed. They are all Active/Active clustered instances (my
first ones).
>
I am trying to create database links to other SQL Server instances, but
there are 3 that I cannot connect to.
>
The dblink works if I connect to the instance as "sa", but not as my
Windows Authenticated account. We use Active Directory and I am in the
Administrator group on all of the boxes.
>
Of the 3 I can't get to, 1 is a cluster (Active/Passive) and the other
2 are just regular Enterprise instances. They are all SQL 2000 SP4.
>
I have several other instances, all SP4 on 2003 boxes and I can connect
to all of them.
>
Also, on the instances where the dblink does not work, I can actually
connect to these databases w/in Enterprise Manager successfully.
>
I am using a different port for the instances, but I have set them up
in the Client Network Utility as well and still can't connect.
>
I can connect via a dblink TO these new instances from the older boxes
fine.
>
I'm really stumped - I've had the Network people verify we don't have a
firewall issue or something.
>
Must be some sort of permissions problem, but I don't know what else to
check - - -
>
Please help!!
>
THANK YOU!!

|||Thank you!

By dblink I am referring to a linked server. (I apologize - I come
from an Oracle background).

The BUILTIN/Administrators group has all of the System Roles and it
still did not work.
Other boxes have links into the server/instance which work fine, and
the new server can link to other servers.
It's strange because it's just this one combination that does not work.

Group A: oldest servers; non-clustered; Win 2003 SP1

Group B: medium servers; 1 a/p cluster; 2 non-clustered; Win 2003 SP1

Group C: newest servers; 3 a/a clusters; Win 2003 R2

All SQL Server 2000 SP4

Group A can link to Group B
Group A can link to Group C
Group B can link to Group A
Group B can link to Group C
Group C can link to Group A
Group C CANNOT link to Group A if logged in using Windows
Authentication

All of the links are set up using "sa".

Stu wrote:

Quote:

Originally Posted by

Not completely sure what you mean by a dblink, but what SQL permissions
do the Administrators have on each server? Are you sure the
BUILTIN/Administrators Windows group have the appropriate permissions
on the databases you want to select from?
>
Stu
>
>
traceable1 wrote:

Quote:

Originally Posted by

I have a couple new servers - Windows 2003 R2 - with SQL Server 2000
SP4 installed. They are all Active/Active clustered instances (my
first ones).

I am trying to create database links to other SQL Server instances, but
there are 3 that I cannot connect to.

The dblink works if I connect to the instance as "sa", but not as my
Windows Authenticated account. We use Active Directory and I am in the
Administrator group on all of the boxes.

Of the 3 I can't get to, 1 is a cluster (Active/Passive) and the other
2 are just regular Enterprise instances. They are all SQL 2000 SP4.

I have several other instances, all SP4 on 2003 boxes and I can connect
to all of them.

Also, on the instances where the dblink does not work, I can actually
connect to these databases w/in Enterprise Manager successfully.

I am using a different port for the instances, but I have set them up
in the Client Network Utility as well and still can't connect.

I can connect via a dblink TO these new instances from the older boxes
fine.

I'm really stumped - I've had the Network people verify we don't have a
firewall issue or something.

Must be some sort of permissions problem, but I don't know what else to
check - - -

Please help!!

THANK YOU!!

|||You may want to google "double-hop" and SQL Server; I'm not sure that I
understand your scenario, but a few articles might ring true.

HTH,
Stu

traceable1 wrote:

Quote:

Originally Posted by

Thank you!
>
By dblink I am referring to a linked server. (I apologize - I come
from an Oracle background).
>
The BUILTIN/Administrators group has all of the System Roles and it
still did not work.
Other boxes have links into the server/instance which work fine, and
the new server can link to other servers.
It's strange because it's just this one combination that does not work.
>
>
Group A: oldest servers; non-clustered; Win 2003 SP1
>
Group B: medium servers; 1 a/p cluster; 2 non-clustered; Win 2003 SP1
>
Group C: newest servers; 3 a/a clusters; Win 2003 R2
>
All SQL Server 2000 SP4
>
Group A can link to Group B
Group A can link to Group C
Group B can link to Group A
Group B can link to Group C
Group C can link to Group A
Group C CANNOT link to Group A if logged in using Windows
Authentication
>
All of the links are set up using "sa".
>
>
>
>
>
>
Stu wrote:

Quote:

Originally Posted by

Not completely sure what you mean by a dblink, but what SQL permissions
do the Administrators have on each server? Are you sure the
BUILTIN/Administrators Windows group have the appropriate permissions
on the databases you want to select from?

Stu

traceable1 wrote:

Quote:

Originally Posted by

I have a couple new servers - Windows 2003 R2 - with SQL Server 2000
SP4 installed. They are all Active/Active clustered instances (my
first ones).
>
I am trying to create database links to other SQL Server instances, but
there are 3 that I cannot connect to.
>
The dblink works if I connect to the instance as "sa", but not as my
Windows Authenticated account. We use Active Directory and I am in the
Administrator group on all of the boxes.
>
Of the 3 I can't get to, 1 is a cluster (Active/Passive) and the other
2 are just regular Enterprise instances. They are all SQL 2000 SP4.
>
I have several other instances, all SP4 on 2003 boxes and I can connect
to all of them.
>
Also, on the instances where the dblink does not work, I can actually
connect to these databases w/in Enterprise Manager successfully.
>
I am using a different port for the instances, but I have set them up
in the Client Network Utility as well and still can't connect.
>
I can connect via a dblink TO these new instances from the older boxes
fine.
>
I'm really stumped - I've had the Network people verify we don't have a
firewall issue or something.
>
Must be some sort of permissions problem, but I don't know what else to
check - - -
>
Please help!!
>
THANK YOU!!

|||Thanks -

we're not double-hopping.

If you go to Enterprise Manager, under Security there is "Linked
Servers".

If I am on one of the boxes in Group C, I am unable to open the link to
any of the instances in Group B if I am logged into the instance using
Windows Authentication. (Sorry I had a typo in the last message).

I've recently discovered that if I change the port back to 1433, the
database link will work. However, I am uncomfortable going back to the
default port.
All of my other links work fine.

Also, the servers in Group C are using 64-bit Windows (but 32-bit SQL).

thanks,
tc

Stu wrote:

Quote:

Originally Posted by

You may want to google "double-hop" and SQL Server; I'm not sure that I
understand your scenario, but a few articles might ring true.
>
HTH,
Stu
>
>
traceable1 wrote:

Quote:

Originally Posted by

Thank you!

By dblink I am referring to a linked server. (I apologize - I come
from an Oracle background).

The BUILTIN/Administrators group has all of the System Roles and it
still did not work.
Other boxes have links into the server/instance which work fine, and
the new server can link to other servers.
It's strange because it's just this one combination that does not work.

Group A: oldest servers; non-clustered; Win 2003 SP1

Group B: medium servers; 1 a/p cluster; 2 non-clustered; Win 2003 SP1

Group C: newest servers; 3 a/a clusters; Win 2003 R2

All SQL Server 2000 SP4

Group A can link to Group B
Group A can link to Group C
Group B can link to Group A
Group B can link to Group C
Group C can link to Group A
Group C CANNOT link to Group A if logged in using Windows
Authentication

All of the links are set up using "sa".

Stu wrote:

Quote:

Originally Posted by

Not completely sure what you mean by a dblink, but what SQL permissions
do the Administrators have on each server? Are you sure the
BUILTIN/Administrators Windows group have the appropriate permissions
on the databases you want to select from?
>
Stu
>
>
traceable1 wrote:
I have a couple new servers - Windows 2003 R2 - with SQL Server 2000
SP4 installed. They are all Active/Active clustered instances (my
first ones).

I am trying to create database links to other SQL Server instances, but
there are 3 that I cannot connect to.

The dblink works if I connect to the instance as "sa", but not as my
Windows Authenticated account. We use Active Directory and I am in the
Administrator group on all of the boxes.

Of the 3 I can't get to, 1 is a cluster (Active/Passive) and the other
2 are just regular Enterprise instances. They are all SQL 2000 SP4.

I have several other instances, all SP4 on 2003 boxes and I can connect
to all of them.

Also, on the instances where the dblink does not work, I can actually
connect to these databases w/in Enterprise Manager successfully.

I am using a different port for the instances, but I have set them up
in the Client Network Utility as well and still can't connect.

I can connect via a dblink TO these new instances from the older boxes
fine.

I'm really stumped - I've had the Network people verify we don't have a
firewall issue or something.

Must be some sort of permissions problem, but I don't know what else to
check - - -

Please help!!

THANK YOU!!

DATABSE IS SUSPECT BECAUSE OF MISSING FILES

My SQL 7 database is missing it's LDF file and is now
tagged as suspect. I have tried many things to solve this
problem but I always get error msg 945, level 16.
I am trying to restore the database but this takes a long
time. By the way the reason that I deleted the LFD file
because it had grown beyond the capacity of the harddrive.
Is there anything else I can do that does not include
major surgery? Any help that you can give me is
appreciated, thanks.
James Colbert
See if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Deleting a log file should never be an option.
Andrew J. Kelly SQL MVP
"James Colbert" <jcolbert30@.yahoo.com> wrote in message
news:248b801c45f83$a205f370$a501280a@.phx.gbl...
> My SQL 7 database is missing it's LDF file and is now
> tagged as suspect. I have tried many things to solve this
> problem but I always get error msg 945, level 16.
> I am trying to restore the database but this takes a long
> time. By the way the reason that I deleted the LFD file
> because it had grown beyond the capacity of the harddrive.
> Is there anything else I can do that does not include
> major surgery? Any help that you can give me is
> appreciated, thanks.
> James Colbert
>
|||Sorry but no...
The exact problem that I am having is that files are
missing and your reply does not address how to recover
from this problem. In other words how do I replace the
missing files that SQL needs in order to remove the DB
from the suspect mode?
Any further suggestions would be appreciated, thanks.
James
|||Hi,
Instead of deletion it is always recommended to shrink the files using DBCC
SHRINKFILE.
When you lost the LDF and you need to recover the database
if you have the FULL database backup and Transaction log backups it is
recommeded to apply the backups in sequence to recover the database.
This provide the data integrity.
Incase if you do not have the backups you can do below:-
1. Set the database to emergency mode
2. Create a new database and USE DTS to transfer data and objects.
-- Setting emergency mode
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Since the transaction log file was not in the startup the data
integrity/consistency may not be assured.
Thanks
Hari
MCDBA
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James
|||Uhhh, but did you actually read it? It details exactly what to do in this
situation including resetting the suspect status. There are no supported
methods that will work 100% of the time when you delete the log. Your best
bet is to restore from know good backups. If that's not an option you can
try sp_attach_single_file_db and this method. You can also call MS PSS and
let them walk you trough it.
http://support.microsoft.com/default...d=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James

DATABSE IS SUSPECT BECAUSE OF MISSING FILES

My SQL 7 database is missing it's LDF file and is now
tagged as suspect. I have tried many things to solve this
problem but I always get error msg 945, level 16.
I am trying to restore the database but this takes a long
time. By the way the reason that I deleted the LFD file
because it had grown beyond the capacity of the harddrive.
Is there anything else I can do that does not include
major surgery? Any help that you can give me is
appreciated, thanks.
James ColbertSee if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Deleting a log file should never be an option.
Andrew J. Kelly SQL MVP
"James Colbert" <jcolbert30@.yahoo.com> wrote in message
news:248b801c45f83$a205f370$a501280a@.phx
.gbl...
> My SQL 7 database is missing it's LDF file and is now
> tagged as suspect. I have tried many things to solve this
> problem but I always get error msg 945, level 16.
> I am trying to restore the database but this takes a long
> time. By the way the reason that I deleted the LFD file
> because it had grown beyond the capacity of the harddrive.
> Is there anything else I can do that does not include
> major surgery? Any help that you can give me is
> appreciated, thanks.
> James Colbert
>|||Sorry but no...
The exact problem that I am having is that files are
missing and your reply does not address how to recover
from this problem. In other words how do I replace the
missing files that SQL needs in order to remove the DB
from the suspect mode?
Any further suggestions would be appreciated, thanks.
James|||Hi,
Instead of deletion it is always recommended to shrink the files using DBCC
SHRINKFILE.
When you lost the LDF and you need to recover the database
---
if you have the FULL database backup and Transaction log backups it is
recommeded to apply the backups in sequence to recover the database.
This provide the data integrity.
Incase if you do not have the backups you can do below:-
1. Set the database to emergency mode
2. Create a new database and USE DTS to transfer data and objects.
-- Setting emergency mode
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Since the transaction log file was not in the startup the data
integrity/consistency may not be assured.
Thanks
Hari
MCDBA
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx
.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James|||Uhhh, but did you actually read it? It details exactly what to do in this
situation including resetting the suspect status. There are no supported
methods that will work 100% of the time when you delete the log. Your best
bet is to restore from know good backups. If that's not an option you can
try sp_attach_single_file_db and this method. You can also call MS PSS and
let them walk you trough it.
http://support.microsoft.com/defaul...id=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
Andrew J. Kelly SQL MVP
"James" <jcolbert30@.yahoo.com> wrote in message
news:2496001c45f89$77adbe90$a501280a@.phx
.gbl...
> Sorry but no...
> The exact problem that I am having is that files are
> missing and your reply does not address how to recover
> from this problem. In other words how do I replace the
> missing files that SQL needs in order to remove the DB
> from the suspect mode?
> Any further suggestions would be appreciated, thanks.
> James

Databse is lost

Hi,
I am using windows server STD 2003, with SP1, with SQL server 2000 with SP1.
The database stops working almost every 24 hours. I have to stop/kill
services and then start again to get it back up.
Kindly advise.
ThanksYou would want to start by checking the SQL Server error
logs and the Windows event logs. There should be something
logged to give you some clues as to what is going on with
your SQL Server.
-Sue
On Mon, 15 May 2006 22:35:01 -0700, Al Gates
<AlGates@.discussions.microsoft.com> wrote:

>Hi,
>I am using windows server STD 2003, with SP1, with SQL server 2000 with SP1
.
>The database stops working almost every 24 hours. I have to stop/kill
>services and then start again to get it back up.
>Kindly advise.
>Thanks

Databse in Use

I am attempting to schedule a Transaction log update to our reports server.
the transaction log completes fine as long as no one is in the DB. How can
I restart SQL or drop the users before I run the log restore so I do not get
the Database in use error?How about executing ALTER DATABASE and set it in single user with the
ROLLBACK IMMEDIATE option?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Will Westfall" <willwjr@.nospamyahoo.com> wrote in message
news:%23o6jcT8pDHA.1948@.TK2MSFTNGP12.phx.gbl...
> I am attempting to schedule a Transaction log update to our reports
server.
> the transaction log completes fine as long as no one is in the DB. How
can
> I restart SQL or drop the users before I run the log restore so I do not
get
> the Database in use error?
>|||Thanks for the quick response. I will give that a shot.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:exy65V8pDHA.372@.TK2MSFTNGP11.phx.gbl...
> How about executing ALTER DATABASE and set it in single user with the
> ROLLBACK IMMEDIATE option?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Will Westfall" <willwjr@.nospamyahoo.com> wrote in message
> news:%23o6jcT8pDHA.1948@.TK2MSFTNGP12.phx.gbl...
> > I am attempting to schedule a Transaction log update to our reports
> server.
> > the transaction log completes fine as long as no one is in the DB. How
> can
> > I restart SQL or drop the users before I run the log restore so I do not
> get
> > the Database in use error?
> >
> >
>

databse in DMZ

We provide datawarehousing reports.
Our datbases are currenlty in the DMZ.
Why? Wouldn't just the webserver be in the DMZ and the db's reside on the private network?RE:
Q1 Our datbases are currenlty in the DMZ. Why?
Q2 Wouldn't just the webserver be in the DMZ and the db's reside on the private network?

A1 That is a good question for your DB developers, IT / business management, etc.,.

A2 Configured well, that may be a somewhat more secure arrangement than having all (?) corporate DBs in a DMZ :( . Some corporations may have reasons to isolate (low risk DBs) in a DMZ. For example, if 'main' internal DBs are completly isolated from any intenet connectivity for security or policy reasons, there may be periodic small loads to the DMZ DBs of a working dataset, (while the core of the historical data is physically secured from any potential internet exposure). Or it may have just been for developer convenience.|||What is very curious is that datawarehouse data is usually highly confidential.

Normally, database servers are restricted from being accessed directly from the Internet due to security/confidential/hacking issues. Also, make sure that the ports that are open are ones absolutely necessary. The database server should only be accessible from the application/web server.

If the database server has to be available in the DMZ then replicate/copy the information from the master database, which is on the intranet behind firewall 2, to the DMZ database - this way if any damage does happen to the DMZ database server your master is still protected.|||When you say "master" db you mean the systme db, right? How do I use the master db to recover from a user db failure. I remember that when I was studying for my exams but I have never had to use that.

-K|||No - The master is the sql server instance running on the intranet (not the dmz).

Databse Design problem

Hello all,
I have a database design problem
I have a hierarchy that includes 5 levels and each level have a table
EX :
TABLE_L1
L1_ID INT AUTO
L1_CODE nvarchar(50)
L1_NAME nvarchar(255)
TABLE_L2
L2_ID INT AUTO
L1_ID INT
L2_CODE nvarchar(50)
L2_NAME nvarchar(255)
etc
The primary key is an id auto. (can be replaced by a GUID if it is
necessary)
The problem :
I have a user table and must affect rights on some members than can be a
different level of the hierarchy.
For example :
User 1 can access to the member A of level one and all the level A
children's but he can also access to member B4 of level 2
I try to implement integrity so when a member is deleted all rights are
deleted too.
My first design is to have one security definition table per level but i
think i am not the first person to have to give rights on different levels
of a hierarchy and they're must be a "best practice" to design it!
anoyone knows an "ideal" solution?
Thanks
cymryrPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
table .. <<
Then this is not a hierarchy. It should be in one table. You should
not use any kind of auto numbering in a relational database and a GUID
is the worst choice.
How much research did you do when you decided to use NVARCHAR(50) and
NVARCHAR(255). Thise "magic numbers" are a sign of no design work at
all.
children's but he can also access to member B4 of level 2 <<
I have implemented a security scheme with a nested sets model in which
privileges were inherited down the tree from a superior to a
subordinate.
You seem to have no rule for determining privileges, so you will have
to list all combinations.
I have an entire book on TREES & HIERARCHIES IN SQL which might help.|||> How much research did you do when you decided to use NVARCHAR(50) and
> NVARCHAR(255). Thise "magic numbers" are a sign of no design work at
> all.
This is not "Magic numbers" there are the result of an extraction. i am not
responsable of the other database and my design is subbordinate by the other
apps

> children's but he can also access to member B4 of level 2 <<
> I have implemented a security scheme with a nested sets model in which
> privileges were inherited down the tree from a superior to a
> subordinate.
> You seem to have no rule for determining privileges, so you will have
> to list all combinations.
> I have an entire book on TREES & HIERARCHIES IN SQL which might help.
Wich book?I am very interested|||>> Which book? I am very interested <<
TREES & HIERARCHIES IN SQL (Morgan-Kaufmann, 2004)
--CELKO--