would still like to know why this hapened. Here is what I did.
To fix a database (refered as orgDB below) with a role permissions problem
(won't save), do the following
a.. Create a new database (refered as tmpDB below)
b.. Import all tables from orgDB into tmpDB
c.. in tmpDB, run scrip to add users and set permissions
d.. Detach both database (right-click on db name, All Tasks, Detach
database). The DBs will disapear from the list.
e.. In Windows explorer, rename the original data (mdf) and log (ldf)
files: orgDB.mfd >> xx_orgDB.mdf and orgDB.lfd >> xx_orgDB.ldf
f.. In Windows explorer, rename the temporary data (mdf) and log (ldf)
filesto the original name: tmpDB.mfd >> orgDB.mdf and tmpDB.lfd >>
orgDB.ldf
g.. In Enterprise Manager, right-click on Databases / All Tasks / Attach
Database and follow the wizzard and select the orgDB.mdf file.
If there is a simplier (shorter) way to do this, I would like to know it.
C.R.
"C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
news:eYGmqGBwDHA.1744@.TK2MSFTNGP12.phx.gbl...
quote:
> We are experiencing a problems setting permissions on roles on SQL Server
> 2000. Our SQL server is holding several databases (60+) and on some of
them
quote:
> (not all), when we change permissions on tables for a role (Public for
> example), they do not seam to be saved.We tried through Enterprise Manager
> and SQL Query Analyser with the same result. We change the permission,
> click on OK, go out of EM and when we go back in, the permissions are back
> the way they were before we changed them What is puzzling us is that it
> works fine on some databases and not on other. We do not know of any
> database options that would prevent changing the permissions, is there
one?
quote:I've never heard of something like this before - it'd be great if you could
> Anyone as any taught about this?
> MS SQL Server 2000 (sp3a)
> Windows 2000 (sp4)
> --
> C.R.
>
call product support and help them reproduce this so we can see if there's a
problem with SQL Server or something really really strange about the
database...
-Richard
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
news:e3vpYRDwDHA.1872@.TK2MSFTNGP09.phx.gbl...
quote:
> I foud a way to fix this, but it's a long process (this DB is huged), and
I
quote:|||I have noted this too.
> would still like to know why this hapened. Here is what I did.
> To fix a database (refered as orgDB below) with a role permissions problem
> (won't save), do the following
> a.. Create a new database (refered as tmpDB below)
> b.. Import all tables from orgDB into tmpDB
> c.. in tmpDB, run scrip to add users and set permissions
> d.. Detach both database (right-click on db name, All Tasks, Detach
> database). The DBs will disapear from the list.
> e.. In Windows explorer, rename the original data (mdf) and log (ldf)
> files: orgDB.mfd >> xx_orgDB.mdf and orgDB.lfd >> xx_orgDB.ldf
> f.. In Windows explorer, rename the temporary data (mdf) and log (ldf)
> filesto the original name: tmpDB.mfd >> orgDB.mdf and tmpDB.lfd >>
> orgDB.ldf
> g.. In Enterprise Manager, right-click on Databases / All Tasks / Attach
> Database and follow the wizzard and select the orgDB.mdf file.
> If there is a simplier (shorter) way to do this, I would like to know it.
>
> C.R.
>
> "C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
> news:eYGmqGBwDHA.1744@.TK2MSFTNGP12.phx.gbl...
Server[QUOTE]
> them
Manager[QUOTE]
back[QUOTE]
> one?
>
We have a SQL 2000 SP3 that is having databases of its own.
We recently upgraded (other) databases from a SQL 7.0 server to this s2k
server.
The problem of not saving the permisssions is happening only on the upgraded
dbs not the ones created in SQL 7.0.
WE have managed to replicate this problem on at least 3 different ugpraded
servers.
Although it seems that databases backed up in sQL 7.0 and restore in SQL
2000 does not have this problem but databases upgraded using the copy db
wizards - all have the problem.
Interestingly, checkint the tables (syspermissions) indicate that the role
does actually have the permissions, but they are not shown on EM or scripted
out of the objects if u script them.
Hope that helps in replication of the problem.
MHJ
"Richard Waymire [MSFT]" <rwaymi@.online.microsoft.com> wrote in message
news:eFRavfEwDHA.2492@.TK2MSFTNGP12.phx.gbl...
quote:
> I've never heard of something like this before - it'd be great if you
could
quote:
> call product support and help them reproduce this so we can see if there's
a
quote:
> problem with SQL Server or something really really strange about the
> database...
> -Richard
> --
> Richard Waymire, MCSE, MCDBA
> This posting is provided "AS IS" with no warranties, and confers no
rights.
quote:
> "C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
> news:e3vpYRDwDHA.1872@.TK2MSFTNGP09.phx.gbl...
and[QUOTE]
> I
problem[QUOTE]
Attach[QUOTE]
it.[QUOTE]
> Server
of[QUOTE]
> Manager
permission,[QUOTE]
> back
it[QUOTE]
>
No comments:
Post a Comment