Our SBS Premium was set up by a predecessor and am not sure how much
"tweaking" was done. Have however found quite a few items that were changed
from normal installation defaults.
Have now started looking into SQL Server and found some issues.
(Some of these may be due to my being inexperienced with MS SQL Server,
apologies in advance if so).
Using SQL Enterprise from a Domain Admin account.
No problems - can Create/delete Db's, tables etc.
Using an ODBC connection to Northwind from Access with Domain Admin Account.
Seems to be no problem, can call tables and even write in the Categories
table.
However... and now the fun starts....
After creation of a new DB and a test table, all fine in SQL Enterprise -
but - table is read only via ODBC from Domain Admin account.
Have also noted that MSDB and Master had to be restored from age-old backups
way back when after a major crash. Am wondering if a rebuild of MSDB may hel
p
to get it back to default settings.
Would appreciate some expert input.
KenHi Ken
If you are connecting to the database and able to select data then it is
unlikely that the problems lies with the system databases. You may want to
check the logins to the server and users in the database. Did you view the
table permissions when in enterprise manager? To rule out any issue with you
r
access connection, you could see if you can update the data in Query
Analyser, it could be that you have specified a read-only cursor or similar.
John
"Ken-T" wrote:
> Hi,
> Our SBS Premium was set up by a predecessor and am not sure how much
> "tweaking" was done. Have however found quite a few items that were change
d
> from normal installation defaults.
> Have now started looking into SQL Server and found some issues.
> (Some of these may be due to my being inexperienced with MS SQL Server,
> apologies in advance if so).
> Using SQL Enterprise from a Domain Admin account.
> No problems - can Create/delete Db's, tables etc.
> Using an ODBC connection to Northwind from Access with Domain Admin Accoun
t.
> Seems to be no problem, can call tables and even write in the Categories
> table.
> However... and now the fun starts....
> After creation of a new DB and a test table, all fine in SQL Enterprise -
> but - table is read only via ODBC from Domain Admin account.
> Have also noted that MSDB and Master had to be restored from age-old backu
ps
> way back when after a major crash. Am wondering if a rebuild of MSDB may h
elp
> to get it back to default settings.
> Would appreciate some expert input.
> Ken
>|||Ken
You cannot rebuild MSDB database , however you will ne able to rebuild
MASTER database but I'd say it is not an easy task
Can you elaborate what is the problem? What if you install SQL Server and
the move all user databases in. I have done may times restore to MSDB
database but I have never done rebuild mater database
"Ken-T" <KenT@.discussions.microsoft.com> wrote in message
news:ECCB5214-60CA-4944-A2D3-F9D4F92CE062@.microsoft.com...
> Hi,
> Our SBS Premium was set up by a predecessor and am not sure how much
> "tweaking" was done. Have however found quite a few items that were
> changed
> from normal installation defaults.
> Have now started looking into SQL Server and found some issues.
> (Some of these may be due to my being inexperienced with MS SQL Server,
> apologies in advance if so).
> Using SQL Enterprise from a Domain Admin account.
> No problems - can Create/delete Db's, tables etc.
> Using an ODBC connection to Northwind from Access with Domain Admin
> Account.
> Seems to be no problem, can call tables and even write in the Categories
> table.
> However... and now the fun starts....
> After creation of a new DB and a test table, all fine in SQL Enterprise -
> but - table is read only via ODBC from Domain Admin account.
> Have also noted that MSDB and Master had to be restored from age-old
> backups
> way back when after a major crash. Am wondering if a rebuild of MSDB may
> help
> to get it back to default settings.
> Would appreciate some expert input.
> Ken
>|||Uri,
our server is SBS Premium and unfortunately it's not quite so easy to
re-install SQL.
There are Sbsmonitoring and Sharepoint instances. Would have to setup a new
server to "test" and again, due to SBS limitations there can only be one DC
so would have to build a new test domain or keep test server separate.
Plus of course the time consuming SP installation and re-boots.
I thought that MSBD could be re-created using the INSTMSDB.SQL script?
Am following this in separate thread now - but would really appreciate all
input you have regarding master and msdb re-builds since am now finding a ne
w
issue.
MSDB cannot be restored via ArcServe and I suspect this is only since SP4
(definately had no trouble last year).
Am hearing from CA that SP4 may not be fully supported - ouch.
Ken
"Uri Dimant" wrote:
> Ken
> You cannot rebuild MSDB database , however you will ne able to rebuild
> MASTER database but I'd say it is not an easy task
> Can you elaborate what is the problem? What if you install SQL Server and
> the move all user databases in. I have done may times restore to MSDB
> database but I have never done rebuild mater database
>
>
> "Ken-T" <KenT@.discussions.microsoft.com> wrote in message
> news:ECCB5214-60CA-4944-A2D3-F9D4F92CE062@.microsoft.com...
>
>|||Hi John,
darn... why didn't I think of that?
May take a while to be able to check this but I suspect that data entry via
Query will not be a problem.
As you may see in separate post in this thread, have now found restore probs
with ArcvServe (currently in work, msbd cannot be restored, probably since
SP4).
For further clarification, all is running, it's not that we have an SQL
crash or anything. We hope soon to begin using SQL more and are in the
checking and testing phase. So, at the moment, there are no tasks etc that
need to be saved and the idea of getting back to defaults with a re-build of
master and msdb seemed easiest. ( agree with you though, since all is runnin
g
the problem is me goofing somewhere).
The first issue seems to be users/groups for the database or SQL itself.
Would it help if I listed the shown users/groups etc so that we could ensure
that right admin groups have the right basic access?
Ken
"John Bell" wrote:
[vbcol=seagreen]
> Hi Ken
> If you are connecting to the database and able to select data then it is
> unlikely that the problems lies with the system databases. You may want to
> check the logins to the server and users in the database. Did you view the
> table permissions when in enterprise manager? To rule out any issue with y
our
> access connection, you could see if you can update the data in Query
> Analyser, it could be that you have specified a read-only cursor or simila
r.
> John
> "Ken-T" wrote:
>|||Ken
> I thought that MSBD could be re-created using the INSTMSDB.SQL script?
Correct, but that means you are going to lose all jobs if you had it as well
as dts packages.
> MSDB cannot be restored via ArcServe and I suspect this is only since SP4
> (definately had no trouble last year).
> Am hearing from CA that SP4 may not be fully supported - ouch.
Is ArcServe third party software? I'm not familiar with it.
SP4 is a service pack so how it could be not supported. There are some
hotfix you can download from MS web site for SP4 as far as I know
"Ken-T" <KenT@.discussions.microsoft.com> wrote in message
news:C22670CA-EAF6-4B2C-8FC2-F1D99D1B62CD@.microsoft.com...[vbcol=seagreen]
> Uri,
> our server is SBS Premium and unfortunately it's not quite so easy to
> re-install SQL.
> There are Sbsmonitoring and Sharepoint instances. Would have to setup a
> new
> server to "test" and again, due to SBS limitations there can only be one
> DC
> so would have to build a new test domain or keep test server separate.
> Plus of course the time consuming SP installation and re-boots.
> I thought that MSBD could be re-created using the INSTMSDB.SQL script?
> Am following this in separate thread now - but would really appreciate all
> input you have regarding master and msdb re-builds since am now finding a
> new
> issue.
> MSDB cannot be restored via ArcServe and I suspect this is only since SP4
> (definately had no trouble last year).
> Am hearing from CA that SP4 may not be fully supported - ouch.
> Ken
>
> "Uri Dimant" wrote:
>|||Uri,
yup - ArcServe is backup software.
The restore problem seems to be happening after SP4 for SQL installation.
ArcServe are checking into how their product works with SQL after the SQL
SP4 installation. Have had similar probs in the past, usuálly had to instal
l
a new SP fro ArcServe.
Will let you know.
"Uri Dimant" wrote:
> Ken
> Correct, but that means you are going to lose all jobs if you had it as we
ll
> as dts packages.
>
>
> Is ArcServe third party software? I'm not familiar with it.
> SP4 is a service pack so how it could be not supported. There are some
> hotfix you can download from MS web site for SP4 as far as I know
>
> "Ken-T" <KenT@.discussions.microsoft.com> wrote in message
> news:C22670CA-EAF6-4B2C-8FC2-F1D99D1B62CD@.microsoft.com...
>
>|||John,
the problem seems to be me....and setting the access to the DB and tables.
Using a Dom Admin accnt, Created a test db, created a table.
Left everything as is.
SQL Enterprise can read/write/delete, do anything.
SQL Query can read and write to the test db test table.
ODBC access via MSAccess can not.
Ermmm..... '
"John Bell" wrote:
[vbcol=seagreen]
> Hi Ken
> If you are connecting to the database and able to select data then it is
> unlikely that the problems lies with the system databases. You may want to
> check the logins to the server and users in the database. Did you view the
> table permissions when in enterprise manager? To rule out any issue with y
our
> access connection, you could see if you can update the data in Query
> Analyser, it could be that you have specified a read-only cursor or simila
r.
> John
> "Ken-T" wrote:
>|||Hi Ken
You may have seen my other posts... I am not a fan of the backup agents!
You may want to check how your ODBC connection is configured. Also look at
the MDAC component checker
http://msdn.microsoft.com/data/mdac...ds/default.aspx and possibly move
to a more recent version.
You may also want to try creating a new access database and re-link the
tables to see if you can update them.
John
"Ken-T" wrote:
[vbcol=seagreen]
> John,
> the problem seems to be me....and setting the access to the DB and tables
.
> Using a Dom Admin accnt, Created a test db, created a table.
> Left everything as is.
> SQL Enterprise can read/write/delete, do anything.
> SQL Query can read and write to the test db test table.
> ODBC access via MSAccess can not.
> Ermmm..... '
>
> "John Bell" wrote:
>|||John,
sorry for making this thread too complex.
Have checked and tested further. Thing are now beginning to make sense.
Created a test DB and a test table.
In Server-Users added a domain user and gave access to the database.
Set Database in a system DSN, Logged in to PC as the domain user.
All is fine. User can see data and change data via ODBC connection.
If however I try to create a user for my Domain account I get a warning that
user already has permissions in databases. Then comes an error 15181 that db
owner cannot be deleted etc.
Could it be that SQL server already has settings for my username based on
the groups I am a member of?
In SQL Enterprise for our SQL Server, under Security, User names - I see
dbadmin
sa
builtin\Administrators
domain\Administrator
For the groups the membership for my account is :
Am a member of
DomainAdmins
Admins
Dom Admins also contains members Administrators and vice versa.
Administrators conatins Domain Admins.
(Probably all way too complex, will get aroung to simplifying one day).
So, the real question - may this be the problem? That the group memberships
is not allowing naming me as a simple DB user without mixing up all the admi
n
rights?
If so, what users/groups should normally be showing for default under
Security/Usernames?
Can the Admin groups be removed without causing further problems?
Ken
"John Bell" wrote:
[vbcol=seagreen]
> Hi Ken
> If you are connecting to the database and able to select data then it is
> unlikely that the problems lies with the system databases. You may want to
> check the logins to the server and users in the database. Did you view the
> table permissions when in enterprise manager? To rule out any issue with y
our
> access connection, you could see if you can update the data in Query
> Analyser, it could be that you have specified a read-only cursor or simila
r.
> John
> "Ken-T" wrote:
>
No comments:
Post a Comment