Disk space usage

SQL 7.0 SP4/NT 4.0
DB File is set to grow by 5%.
It's given that SQL preallocates its hard disk space so
that as records are added to the table, there is no
change to the hard drive space at the NT level.
Is there a way to see, at the SQL level, how much of the
space currently allocated, is available?
Thanks,
Donsp_spaceused will show you how much space is currently
allocated in a database.
Linchi
>--Original Message--
>SQL 7.0 SP4/NT 4.0
>DB File is set to grow by 5%.
>It's given that SQL preallocates its hard disk space so
>that as records are added to the table, there is no
>change to the hard drive space at the NT level.
>Is there a way to see, at the SQL level, how much of the
>space currently allocated, is available?
>Thanks,
>Don
>
>.
>

Disk exchange on two-node MS SQL 2000 active/active cluster

Hi!
I have two-node cluster on Windows 2000 Adv Server sp4. There are two
instanses of MS SQL 2000 Server sp3, one on each node. I have one
physical disk owened by the first node, that is not used. I need to
transfer it to the second node, name it with the letter this node
already has (removing the disk with this letter from the node by
renaming it or just by deleting) and place on it SQL Server logs that
were located on the disk with this letter.
This is my plan:
1. Transfer disk Z: from node SQL1 to node SQL2
2. Stop sql server and agent services on node SQL2 (or take them
offline with cluster administrator) WHich is better?
3. Copy log files from disk V: to disk Z: on node SQL2
4. Delete disk V: (or rename it as lets say P: ) - I plan to remove
the mirror with the former V: disk from storage
5. Rename disk Z: to disk V: on node SQL2
6. Start sql server and agent services on node SQL2 (or take them
online with cluster administrator)
Is the plan correct? Are there any dangers like (suppose) node signs
its drives and will not accept another disk because of it or because
the disks are a bit different in size? Anything else?
Thanks.
I will be doing something similar this Sunday morning, but with a 2003
cluster. Comments inline.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0405210601.2d69da11@.posting.google.c om...
> Hi!
> I have two-node cluster on Windows 2000 Adv Server sp4. There are two
> instanses of MS SQL 2000 Server sp3, one on each node. I have one
> physical disk owened by the first node, that is not used. I need to
> transfer it to the second node, name it with the letter this node
> already has (removing the disk with this letter from the node by
> renaming it or just by deleting) and place on it SQL Server logs that
> were located on the disk with this letter.
> This is my plan:
> 1. Transfer disk Z: from node SQL1 to node SQL2
> 2. Stop sql server and agent services on node SQL2 (or take them
> offline with cluster administrator) WHich is better?
Use Cluster administrator to take the SQL service offline but leave the disk
resources online.
> 3. Copy log files from disk V: to disk Z: on node SQL2
>
Good.
> 4. Delete disk V: (or rename it as lets say P: ) - I plan to remove
> the mirror with the former V: disk from storage
>
Reletter it. Take it out of the SQL resource group.
> 5. Rename disk Z: to disk V: on node SQL2
>
Add this disk to the SQL resource group. Make SQL Server dependant on this
disk resource.
> 6. Start sql server and agent services on node SQL2 (or take them
> online with cluster administrator)
> Is the plan correct? Are there any dangers like (suppose) node signs
> its drives and will not accept another disk because of it or because
> the disks are a bit different in size? Anything else?
>
I have tested this once with a complete disk replacement using a test build.
Everything worked as expected. Make sure SQL stays offline while you are
moving things around. Make double sure you have fixed the dependancies.
Backup the universe before trying anything.
> Thanks.
|||Trying to transfer disk Z: from node SQL1 to node SQL2 (right mouse
button, change group, choose SQL2) get a window:
Are you sure the 'SQL1 Backup Z:' resource should be moved from the
''SQL1' group to the ''SQL2' group?
Choose "Yes"
Get another message:
Move Resources
The group will be changed for the following resources:
SQL1 Backup Z: (Only this resource, that means that I removed SQL
Server and dependancies from it)
Choose "Yes" again:
Get an error:
An error occured attempting to change the group of the 'Node2_BACKUP
Z:' resource to 'Node1'
The cluster node is not the owner of the group.
Error ID: 5016 (00001398).
Could not find how to change this.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<urKIL3zPEHA.3012@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> I will be doing something similar this Sunday morning, but with a 2003
> cluster. Comments inline.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Roust_m" <roustam@.hotbox.ru> wrote in message
> news:a388fd78.0405210601.2d69da11@.posting.google.c om...
> Use Cluster administrator to take the SQL service offline but leave the disk
> resources online.
> Good.
> Reletter it. Take it out of the SQL resource group.
> Add this disk to the SQL resource group. Make SQL Server dependant on this
> disk resource.
> I have tested this once with a complete disk replacement using a test build.
> Everything worked as expected. Make sure SQL stays offline while you are
> moving things around. Make double sure you have fixed the dependancies.
> Backup the universe before trying anything.
|||Bring both resource groups to the node you are logged into. Do the group
changes from that machine.
FYI, my changeover went smoothly. No issues with a replacement partition as
long as the group membership and dependancies are correct.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0405220639.1bc712da@.posting.google.c om...
> Trying to transfer disk Z: from node SQL1 to node SQL2 (right mouse
> button, change group, choose SQL2) get a window:
> Are you sure the 'SQL1 Backup Z:' resource should be moved from the
> ''SQL1' group to the ''SQL2' group?
> Choose "Yes"
> Get another message:
> Move Resources
> The group will be changed for the following resources:
> SQL1 Backup Z: (Only this resource, that means that I removed SQL
> Server and dependancies from it)
> Choose "Yes" again:
> Get an error:
> An error occured attempting to change the group of the 'Node2_BACKUP
> Z:' resource to 'Node1'
> The cluster node is not the owner of the group.
> Error ID: 5016 (00001398).
> Could not find how to change this.
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:<urKIL3zPEHA.3012@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
disk[vbcol=seagreen]
this[vbcol=seagreen]
build.[vbcol=seagreen]
are[vbcol=seagreen]
|||Hi!
I tried to bring everything to the node I was loged on and the node I
was trying to give ownership of the disk (the same node) - the same
error. Just deleted this resource and recreated it on the correct node.
After this all went fine.
Thanks.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

disconnection of web pages from SQL Server

We have SQL 2000 SP4 (Standard Edition) on Windows 2000 SP4. We have a cold
fusion application that is running against this database. We have 5 front end
(cold fusion) servers load balanced. We have a monitoring service that pings
our web application main pages (we have 2 main pages) and reports the
downtime. Once in a while the alert site reports that the site is down (tries
thru all the 5 cold fusion servers). On the DB side, I do not see any
messages for that time frame SQL server logs and or error logs. I was looking
at the stats... CPU usuage is around 55%, 8 minutes before the problem
oocured. It gradually went up and reached 65% 3 minutes before. Came
gradually came down to 25% at 1 minute after the problem. 1.6 GB memory was
available through out and number of cpu processes were constant at 45.
Are there any other ways to find out what happened (if any thing was really
wrong with SQL server) ?
Are there any other logs I could check (of course it is after the effect now)
Do you have scheduled virus scans that might be running at this time?
"RK73" wrote:

> We have SQL 2000 SP4 (Standard Edition) on Windows 2000 SP4. We have a cold
> fusion application that is running against this database. We have 5 front end
> (cold fusion) servers load balanced. We have a monitoring service that pings
> our web application main pages (we have 2 main pages) and reports the
> downtime. Once in a while the alert site reports that the site is down (tries
> thru all the 5 cold fusion servers). On the DB side, I do not see any
> messages for that time frame SQL server logs and or error logs. I was looking
> at the stats... CPU usuage is around 55%, 8 minutes before the problem
> oocured. It gradually went up and reached 65% 3 minutes before. Came
> gradually came down to 25% at 1 minute after the problem. 1.6 GB memory was
> available through out and number of cpu processes were constant at 45.
> Are there any other ways to find out what happened (if any thing was really
> wrong with SQL server) ?
> Are there any other logs I could check (of course it is after the effect now)
sql

disconnection of web pages from SQL Server

We have SQL 2000 SP4 (Standard Edition) on Windows 2000 SP4. We have a cold
fusion application that is running against this database. We have 5 front end
(cold fusion) servers load balanced. We have a monitoring service that pings
our web application main pages (we have 2 main pages) and reports the
downtime. Once in a while the alert site reports that the site is down (tries
thru all the 5 cold fusion servers). On the DB side, I do not see any
messages for that time frame SQL server logs and or error logs. I was looking
at the stats... CPU usuage is around 55%, 8 minutes before the problem
oocured. It gradually went up and reached 65% 3 minutes before. Came
gradually came down to 25% at 1 minute after the problem. 1.6 GB memory was
available through out and number of cpu processes were constant at 45.
Are there any other ways to find out what happened (if any thing was really
wrong with SQL server) ?
Are there any other logs I could check (of course it is after the effect now)Do you have scheduled virus scans that might be running at this time?
"RK73" wrote:
> We have SQL 2000 SP4 (Standard Edition) on Windows 2000 SP4. We have a cold
> fusion application that is running against this database. We have 5 front end
> (cold fusion) servers load balanced. We have a monitoring service that pings
> our web application main pages (we have 2 main pages) and reports the
> downtime. Once in a while the alert site reports that the site is down (tries
> thru all the 5 cold fusion servers). On the DB side, I do not see any
> messages for that time frame SQL server logs and or error logs. I was looking
> at the stats... CPU usuage is around 55%, 8 minutes before the problem
> oocured. It gradually went up and reached 65% 3 minutes before. Came
> gradually came down to 25% at 1 minute after the problem. 1.6 GB memory was
> available through out and number of cpu processes were constant at 45.
> Are there any other ways to find out what happened (if any thing was really
> wrong with SQL server) ?
> Are there any other logs I could check (of course it is after the effect now)

disconnection of web pages from SQL Server

We have SQL 2000 SP4 (Standard Edition) on Windows 2000 SP4. We have a cold
fusion application that is running against this database. We have 5 front en
d
(cold fusion) servers load balanced. We have a monitoring service that pings
our web application main pages (we have 2 main pages) and reports the
downtime. Once in a while the alert site reports that the site is down (trie
s
thru all the 5 cold fusion servers). On the DB side, I do not see any
messages for that time frame SQL server logs and or error logs. I was lookin
g
at the stats... CPU usuage is around 55%, 8 minutes before the problem
oocured. It gradually went up and reached 65% 3 minutes before. Came
gradually came down to 25% at 1 minute after the problem. 1.6 GB memory was
available through out and number of cpu processes were constant at 45.
Are there any other ways to find out what happened (if any thing was really
wrong with SQL server) ?
Are there any other logs I could check (of course it is after the effect now
)Do you have scheduled virus scans that might be running at this time?
"RK73" wrote:
[vbcol=seagreen]
> We have SQL 2000 SP4 (Standard Edition) on Windows 2000 SP4. We have a col
d
> fusion application that is running against this database. We have 5 front
end
> (cold fusion) servers load balanced. We have a monitoring service that pin
gs
> our web application main pages (we have 2 main pages) and reports the
> downtime. Once in a while the alert site reports that the site is down (tr
ies
> thru all the 5 cold fusion servers). On the DB side, I do not see any
> messages for that time frame SQL server logs and or error logs. I was look
ing
> at the stats... CPU usuage is around 55%, 8 minutes before the problem
> oocured. It gradually went up and reached 65% 3 minutes before. Came
> gradually came down to 25% at 1 minute after the problem. 1.6 GB memory w
as
> available through out and number of cpu processes were constant at 45.
> Are there any other ways to find out what happened (if any thing was reall
y
> wrong with SQL server) ?
> Are there any other logs I could check (of course it is after the effect now)[/vbc
ol]

Disaster..

Scenario:
w2k server sp4
sql server 2k sp3a
I had on USB external disk (160gb from maxtor) 3,34 GB MDF file and LDF
of some size.
Yesterday night I encountered some read errors on it.
W2k suggests me to run CHKDSK /F, that I obviously do.
When system restarts that 3,34 GB MDF file is now size 0kb!!!
Now...
I know that I should have a backup but these data aren't so
important...but If I can retrieve them is better than nothing !!!!!
I'm trying to restore some files with lots of recovery tools but I
notice that all of them find thousands of files...
How can I do, if possible, to obtain my MDF back "full" of data?
Any help MUCH MUCH appreciated...
Best Regards.
--
F.Fabri,
You really shouldn't put SQL Server data files on an exernal USB drive.
Anyway, there's not much you can do in this instance unless you send the
drive off to a specialist for repair (expensive). I have heard that
putting it in the freezer for a few hours can revive it. However, if
your CHKDSK has overwritten or "fixed" sectors on the disk, it is
unlikely you will get the data back without a corrupt MDF file.
I think your only choices here are to restore from backup, or start again.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Fabri wrote:
> Scenario:
> w2k server sp4
> sql server 2k sp3a
>
> I had on USB external disk (160gb from maxtor) 3,34 GB MDF file and LDF
> of some size.
> Yesterday night I encountered some read errors on it.
> W2k suggests me to run CHKDSK /F, that I obviously do.
> When system restarts that 3,34 GB MDF file is now size 0kb!!!
> Now...
> I know that I should have a backup but these data aren't so
> important...but If I can retrieve them is better than nothing !!!!!
> I'm trying to restore some files with lots of recovery tools but I
> notice that all of them find thousands of files...
> How can I do, if possible, to obtain my MDF back "full" of data?
> Any help MUCH MUCH appreciated...
> Best Regards.
>

Disappering users

Hello.

I have an SQL 2000 OLAP (SP4) project with lots of dimensions, mesures, cubes, roles and users. Everything was fine for a year till yesterday. I've added a user (did it time to time before) to a role. As I process the cube the user was removed from the role. Now I can not add anyone. Am planning to remove everyone and add again. But also scared of adding nobody forever.

Help. Thanks.

I wonder if perhaps one or more of the users in some of the roles are no longer valid in the domain? I would suggest making a backup of your database and then perhaps by removing the roles and re-adding the (valid) users you'll get back to a good state.

Hope that helps.

DISABLENETWORKPROTOCOLS and named instance

Is it true that I need to enable network protocols to communicate with a
named instance of MSDE SP4 locally?
Installing a named instance with DISABLENETWORKPROTOCOLS=0 seems to prevent
osql connecting to it. I don't want remote access - only local access (so
it would be nice not to load the network protocols). Or have I missed
something?
hi Graham,
Graham Morris wrote:
> Is it true that I need to enable network protocols to communicate
> with a named instance of MSDE SP4 locally?
> Installing a named instance with DISABLENETWORKPROTOCOLS=0 seems to
> prevent osql connecting to it. I don't want remote access - only
> local access (so it would be nice not to load the network protocols).
> Or have I missed something?
you do not need to enable network protocols to connect to local named
instances as all local instances are available to reach via shared memory..
what kind of problem are you experiencing? what is the exception you are
reported with?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I start with a Windows XP SP2 system in a workgroup, with nothing installed
except Tweakui (I use VirtualPC to start with the same system each time).
I log in with local administrator privileges.
I make sure all the latest updates are installed from Windows Update.
I navigate to the MSDE SP4 directory and enter 'Setup INSTANCENAME=MYINST
BLANKSAPWD=1'
I reboot so that service MSSQL$MYINST autostarts.
On reboot the service manager shows an empty circle (no 'play' nor 'stop'
symbol). The dropdown does not show my server and instance.
I open a command prompt and enter "osql -S .\MYINST -E" and I get:
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).
I now run C:\Program Files\Microsoft SQL Server\80\Tools\Binn\Svrnetcn.exe,
and add 'Named Pipes'.
The osql command now correctly displays the 1> prompt.
I restart the service manager and it know shows the state of the service.
I find that if I install a blank instance I don't need to and network
libraries. What could be happening?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3ovjn4F7r0d5U1@.individual.net...
> hi Graham,
> Graham Morris wrote:
> you do not need to enable network protocols to connect to local named
> instances as all local instances are available to reach via shared
> memory..
> what kind of problem are you experiencing? what is the exception you are
> reported with?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
Graham Morris wrote:
> I start with a Windows XP SP2 system in a workgroup, with nothing
> installed except Tweakui (I use VirtualPC to start with the same
> system each time).
> I log in with local administrator privileges.
> I make sure all the latest updates are installed from Windows Update.
> I navigate to the MSDE SP4 directory and enter 'Setup
> INSTANCENAME=MYINST BLANKSAPWD=1'
> I reboot so that service MSSQL$MYINST autostarts.
> On reboot the service manager shows an empty circle (no 'play' nor
> 'stop' symbol). The dropdown does not show my server and instance.
disabling network protocols can cause this as the SQL Server Service Manager
can fail to enlist instances of the like, as reported in
http://support.microsoft.com/default...b;EN-US;814132

> I open a command prompt and enter "osql -S .\MYINST -E" and I get:
> [DBNETLIB]SQL Server does not exist or access denied.
> [DBNETLIB]ConnectionOpen (Connect()).
> I now run C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\Svrnetcn.exe, and add 'Named Pipes'.
> The osql command now correctly displays the 1> prompt.
> I restart the service manager and it know shows the state of the
> service.
> I find that if I install a blank instance I don't need to and network
> libraries. What could be happening?
the reported exception, as indicated in
http://support.microsoft.com/default...6&Product=sql,
is a general MDAC related problem, probably caused by
http://support.microsoft.com/kb/328383/en-us
I'll try installing a virtual machine may self ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi,
Andrea Montanari wrote:
> hi,
> Graham Morris wrote:
>

> I'll try installing a virtual machine may self ...
just tried... with the same scenario you reported... after installation +
reboot I logged in as local WinXP admin, the SQL Server Service Manager is
"empty", but I can log in via osql with no problem at all...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I think I've solved this: without network protocols the osql -S parameter
doesn't like the syntax ".\MYINST". Put in the full host name (e. g.
MYHOST\MYINST) and it works!
While you're listening though: this is part of a larger problem: I can't
connect to MSDE SP4 on some domain machines (same problem with osql) until I
remove then replace the network libraries with svrnetcn.exe. This sounds as
though it might be a group policy problem, but I've no idea what. Ring any
bells?
Anyway thanks for the help on the first problem.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3p0d08F7tp8aU1@.individual.net...
> hi,
> Andrea Montanari wrote:
>
> just tried... with the same scenario you reported... after installation +
> reboot I logged in as local WinXP admin, the SQL Server Service Manager is
> "empty", but I can log in via osql with no problem at all...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Graham
Graham Morris wrote:
> While you're listening though: this is part of a larger problem: I
> can't connect to MSDE SP4 on some domain machines (same problem with
> osql) until I remove then replace the network libraries with
> svrnetcn.exe. This sounds as though it might be a group policy
> problem, but I've no idea what. Ring any bells?
actually not... never heard of such a constraint.. you can have "problems"
connecting if you are not listed in the granted logins list, or if you have
network troubles, but as long as MDAC stack is quiet consistent on remote
clients I never heard of such a problem... I'll keep investigating
thought...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Disable publishing

We are using SQL 2K with sp4 and use push subcription. The publisher
database and the distribution database are on the same SQL server.
I need to disable publishing and distribution so I can recreate the
publication from scratch. I use SQL EM's Disable Publishing and Distribution
wizard and after I 'finish' the wizard, it proceed to do its thing and gives
me an error that 'distribution database' is in use. I click ok and the
wizard finished its stuff. I checked the publication and it is droped along
with subscrtiptions. Here are my questions.
1. To avoid the 'in use' error, am I supposed to do something like stop
some jobs or agents before I do that?
2, If the wizard didn't encounter any error, what is supposed to happen to
the distribution database?
Wingman
The distribution database will get removed when it works ok. When I have
seen this error, the publications are removed, and disabling publication for
the second time will remove the distribution database correctly.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the info.
I ran the second time and it removed the distribution database as you
described. It clean out a lot of stuff.
I check the replication folder in EM and find a subscription in the
Subscriptions folder. Is it ok to leave it there before I redo the
publishing from scratch? Why do they want to leave a subscription there?
Wingman
"Paul Ibison" wrote:

> The distribution database will get removed when it works ok. When I have
> seen this error, the publications are removed, and disabling publication for
> the second time will remove the distribution database correctly.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Wingman,
You can have a look at sp_MSenumsubscriptions to see what is happening when
you click on the subscriptions folder. The key tables in each subscribing
databases are sysmergesubscriptions and MSreplication_subscriptions. If this
database is no longer a subscriber, then run sp_removedbreplication, which
removes the redundant system tables. If you want these tables to remain,
then simply locate the redundant record in the above system table and delete
it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Disable database diagram creation

I have a MSSQL 2000 SP4 server.
I need to disable ability to create database diagrams for users.
How can I do this?To create a diagram, they would need to be the database
owner or a member of the db_owner role (or a sysadmin).
Don't add them to those roles.
You can figure out the specific object permissions in the
following article - different issue than yours but it lists
the permissions.
http://support.microsoft.com/?id=327145
-Sue
On 24 Oct 2006 08:38:06 -0700, slousch@.gmail.com wrote:

>I have a MSSQL 2000 SP4 server.
>I need to disable ability to create database diagrams for users.
>How can I do this?|||User is not db_owner and not in sysadmin,
there are no permissions to public database role,
but he is still able to create diagrams.
I want to get "You do not have sufficient privileges to create the new
diagram." message,
when user trying to creaate diagram.
Sue Hoegemeier wrote:
> To create a diagram, they would need to be the database
> owner or a member of the db_owner role (or a sysadmin).
> Don't add them to those roles.
> You can figure out the specific object permissions in the
> following article - different issue than yours but it lists
> the permissions.
> http://support.microsoft.com/?id=327145
> -Sue|||Check the permissions on the objects listed in the article.
-Sue
On 25 Oct 2006 04:09:54 -0700, slousch@.gmail.com wrote:
[vbcol=seagreen]
>User is not db_owner and not in sysadmin,
>there are no permissions to public database role,
>but he is still able to create diagrams.
>I want to get "You do not have sufficient privileges to create the new
>diagram." message,
>when user trying to creaate diagram.
>Sue Hoegemeier wrote: