Problems using SQL: table

Showing posts with label table. Show all posts

Friday, March 30, 2012

Problems with TRIGGER

I still have problems with my TRIGGER (T-SQL) to update a table. I will read some information from INSERTD table and create a String to execute EXEC(@.sSQL), I’m creating a dynamic SQL because the name of then table I’ll update is in the INSERTED table, the problem I have is that : I read a date from the INSERTED table (31/08/2007 12:15:30) and write this date into then update table, but the date change to this : 31/08/2007 12:16:00 above is part of the trigger that do this :

SELECT @.TipoConta = Tipo, @.Inscricao = Inscricao, @.MesAno = Mes_Ref, @.DataPagam = Data_Quitacao, @.ValorPago = Valor_Pago, @.LiberMult = Libera_Multa FROM INSERTED

SET @.YearTable = Right(@.MesAno, 4)

SET @.MyTable = 'LEITURAS_VALORES_' + @.YearTable

set @.sSQL =

'UPDATE ' +

@.MyTable +

' SET ' +

'VALOR_PAGO = ' + str(@.ValorPago, 12, 2) +

', DATA_SITUACAO = ''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + '''' +

', SITUACAO = '''+ @.Situacao + '''' +

', LIB_MULTA = ' + str(@.LiberMult, 2) +

' WHERE ' +

'INSCRICAO = ' + str(@.Inscricao, 8) +

'AND MES_ANO = ''' + @.AuxVar + ''''

exec (@.sSQL)

@.DataPagam is the date I’m reading from INSERTED

The problem is in the code : ', DATA_SITUACAO = ''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + '''' +

Date format (dd/mm/yyyy hh:mm Tongue Tied s) we use here in Brazil

Any one can help me,

Thanks, very much

Try something like this:

', DATA_SITUACAO = CAST(''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + ''' AS DATETIME) '
|||

It could be that your installation is set to us_english which has a dateformat of MDY.

In your trigger, try setting SET DATEFORMAT DMY at the start.

HTH!

|||

I put ''' AS DATETIME) '

Error Source :

.NetSqlCliente Data Provider

Error Message :

Incorrect syntax near then keyword 'AS'

|||

I have already done it : SET DATEFORMAT DMY in my TRIGGER

Thanks

Problems with TRIGGER

SELECT @.TipoConta = Tipo, @.Inscricao = Inscricao, @.MesAno = Mes_Ref, @.DataPagam = Data_Quitacao, @.ValorPago = Valor_Pago, @.LiberMult = Libera_Multa FROM INSERTED

SET @.YearTable = Right(@.MesAno, 4)

SET @.MyTable = 'LEITURAS_VALORES_' + @.YearTable

set @.sSQL =

'UPDATE ' +

@.MyTable +

' SET ' +

'VALOR_PAGO = ' + str(@.ValorPago, 12, 2) +

', DATA_SITUACAO = ''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + '''' +

', SITUACAO = '''+ @.Situacao + '''' +

', LIB_MULTA = ' + str(@.LiberMult, 2) +

' WHERE ' +

'INSCRICAO = ' + str(@.Inscricao, 8) +

'AND MES_ANO = ''' + @.AuxVar + ''''

exec (@.sSQL)

@.DataPagam is the date I’m reading from INSERTED

The problem is in the code : ', DATA_SITUACAO = ''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + '''' +

Date format (dd/mm/yyyy hh:mm Tongue Tied s) we use here in Brazil

Any one can help me,

Thanks, very much

Try something like this:

', DATA_SITUACAO = CAST(''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + ''' AS DATETIME) '
|||

It could be that your installation is set to us_english which has a dateformat of MDY.

In your trigger, try setting SET DATEFORMAT DMY at the start.

HTH!

|||

I put ''' AS DATETIME) '

Error Source :

.NetSqlCliente Data Provider

Error Message :

Incorrect syntax near then keyword 'AS'

|||

I have already done it : SET DATEFORMAT DMY in my TRIGGER

Thanks

problems with the subqueries

I am a beginner in learning sql server 2000 and i wish to know the query for choosing

for (eg ) From "Order details" table in Northwind database

I wish the select the orderid with maximum number products

(i.e.,) say orderid 10250 contains maximum of 25 products with product ids 14, 15, 16... and so on

and the output should be of the form

Orderid Max_No_prod

10250 25

can anyone help me with this....?

From what I understand you want the order that has the most products. Here is the query:

Code Snippet

Select Top 1 OrderID, Count(Distinct ProductID) As Max_No_Prod From [Order Details]

Group By OrderID

Order By Max_No_Prod Desc

I hope this answers your question.

Best regards,

Sami Samir

|||

Hi,

Not sure I completely understand your question, this will return the largest volume of any one product in an order.

Code Snippet

SELECT OrderID, max(Quantity) As Max_No_prod

FROM [Order Details]

WHERE OrderID = 10250

GROUP BY OrderID

Jamie

sql

Problems with Table Adapters

1) I have added new fields to the database, now seeems I need to remove method from table adapter and add it again. Is there any simple way to have table adapter refreshed after adding a field to the stored procedure?

2) Also I have noticed that when I try to edit existing method (configure option), it tells me that method name can not be the same for fill and get. any idea? It is the same now...

3) Also How do I remove the default method? Delete option is greyed out...

Thanks a lot

hi,

We cannot delete the default method.this method is user to fill and select(Default select command)

Right Click the default method and click on Configure..Now If u are using a stored Procedure then u can see the stored procedure name on the select dropdown.

Click the previous button and select the radio button new stored procedure or select the sql statement (if u are selecting the sql statment write an ordinary sql statement) now go till Finish..now the default select method has changed..

now to use the stored procedure that u have changed do the same procedure and select an existing stored procedure option when we click the prevoius button and continue clicking the wizard until finish.

now u will be using the new stored procedure that has a new field that u have added..

i hope this helps

|||

The problem is that when I click next button on the "Choose methods to generate". I get this error message:

"The names specified for Fill Data table and Get data table can not be the same".

You see, there are the same right now, so I am not sure what I should choose... This is existing starter kit application...

|||

hi,

give different names for the filldata method and getdata method.. these are two different methods..

'FilldataAddUsers" and

"GetDataAddAddUsers "

Problems with Switching partitions

Hi,

Im trying to switch a partition from partitioned table to a non partitioned table. I am getting the following error.

eCertified.dbo.Carrier_Trip_AvailabilitySwitch.PK_Carrier_Trip_AvailabilitySwitch_1' is in filegroup 'PRIMARY' and partition 24 of index 'eCertified.dbo.Carrier_Trip_Availability_Partition.PK_Carrier_Trip_Availability_Partition' is in filegroup 'SECONDARY24'. [SQLSTATE 42000] (Error 4939). The step failed.

The table one has 24 partitions and it has a partitioned clustered index. PK_Carrier_Trip_AvailabilitySwitch is the primary key in the non partitioned table and its is in the primary file group while the PK_Carrier_Trip_Availability_Partition is the primary key iin the partitioned table and its in the file group secondary 24.

Please let me know how to solve this issue.

When you switch a partition of a partitioned table to a non-partitioned table or a partition of another partitioned table, the source partition and the target table/partition must be in the same file group.

Problems With Sum

Dear All
We have a table with around 100000 records, this table
contains an Int value that can be >= 1 <= 999999.
Whenever we use the command
Select sum(MYINT) from tblTEST
We get the error 'Arithmetic overflow error converting
expression to data type int.'
I know that the result of the sum is bigger than an int
however I was wondering if there is a trick I can do so I
don't need to create my own loop to add them together.
Thanks for your time
JTry this
Select convert(decimal(12,2),sum(MYINT)) from tblTEST
Madhivanan|||SELECT SUM(CAST(myint AS BIGINT))
FROM tblTest
David Portas
SQL Server MVP
--|||You need to put CONVERT inside the SUM otherwise the SUM will still
overflow.
David Portas
SQL Server MVP
--|||Madhivanan,
It will still give you an Arithmetic Overflow error!
Try the code below
SELECT IDENTITY(INT) AS Number
INTO #TMP
FROM sysobjects s1
CROSS JOIN sysobjects s2
CROSS JOIN sysobjects s3
Select convert(decimal(12,2),sum(Number)) from #TMP
If you dont feel offended, I strongly recommend you to test
your solutions before you post them
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109256055.787979.288100@.z14g2000cwz.googlegroups.com...
> Try this
> Select convert(decimal(12,2),sum(MYINT)) from tblTEST
> Madhivanan
>|||Thanks Thomas
It should be
Select sum(convert(decimal(12,2),Number)) from tblTEST
Madhivanan

Wednesday, March 28, 2012

Problems with SQL users, groups and table access

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.
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 your
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 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
>|||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 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:
> 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
> >
>
>|||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 running
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:
> 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 your
> 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 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
> >|||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...
> 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:
>> 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,
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 install
a new SP fro ArcServe.
Will let you know.
"Uri Dimant" 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...
> > 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:
> >
> >> 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
> >> >
> >>
> >>
> >>
>
>|||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:
> 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 your
> 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 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
> >|||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/downloads/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:
> 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:
> > 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 your
> > 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 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
> > >|||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 admin
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:
> 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 your
> 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 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
> >|||Hi Ken
Having full time domain administration rights is not really good practice.
You will have access to the database via the BUILDIN\Administrators group.
You can look at creating a Database Role and/or a Active Directory Security
Group where you can add the domain users and then you would need to give
permissions only to the Group/Role and not to individuals, you will then only
need to add the domain user to once to the Group/Role without having to
configure permissions for each object.
Check out Books Online for more information about Roles and permissions.
If you look at the database properties in Enterprise manager you will
probably find that your domain account is the database owner. You may want to
change this using the sp_changedbowner stored procedure.
At this point in time I would change your personal permissions and add the
permissions as described above. Make sure that you do know how to log in as
the local administrator before you do this.
HTH
John
"Ken-T" 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 admin
> 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:
> > 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 your
> > 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 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
> > >|||John, really, thanks for the help with this. Is much appreciated.
I need to sorts out the accounts/groups and do a bit of testing.
My only worry is locking myself out of SQL or making it unable to run.
So, another question - if I backup all db's now and actually make phsical
file copies while SQL server is not running - then will the security/user
settings will be back as they were if I roll back'
If so - will know that I have an emergency way back, just in case.
Ken
"John Bell" wrote:
> Hi Ken
> Having full time domain administration rights is not really good practice.
> You will have access to the database via the BUILDIN\Administrators group.
> You can look at creating a Database Role and/or a Active Directory Security
> Group where you can add the domain users and then you would need to give
> permissions only to the Group/Role and not to individuals, you will then only
> need to add the domain user to once to the Group/Role without having to
> configure permissions for each object.
> Check out Books Online for more information about Roles and permissions.
> If you look at the database properties in Enterprise manager you will
> probably find that your domain account is the database owner. You may want to
> change this using the sp_changedbowner stored procedure.
> At this point in time I would change your personal permissions and add the
> permissions as described above. Make sure that you do know how to log in as
> the local administrator before you do this.
> HTH
> John
> "Ken-T" 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 admin
> > 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:
> >
> > > 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 your
> > > 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 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
> > > >|||Hi
If you know the local administrators account and don't delete or change the
BUILTIN\Administrators group from the server then you should be able to get
back in.
Backing up the database will retain the permissions on the objects when they
were backed up. If possibly I would take this off the live server and test it
on a different machine see http://support.microsoft.com/kb/314546, and watch
out for orphaned users as detailed in the article. Failing that restore a
backup of the live database
as a different database and then you will not affect other users.
John
"Ken-T" wrote:
> John, really, thanks for the help with this. Is much appreciated.
> I need to sorts out the accounts/groups and do a bit of testing.
> My only worry is locking myself out of SQL or making it unable to run.
> So, another question - if I backup all db's now and actually make phsical
> file copies while SQL server is not running - then will the security/user
> settings will be back as they were if I roll back'
> If so - will know that I have an emergency way back, just in case.
> Ken
>
>
> "John Bell" wrote:
> > Hi Ken
> >
> > Having full time domain administration rights is not really good practice.
> > You will have access to the database via the BUILDIN\Administrators group.
> > You can look at creating a Database Role and/or a Active Directory Security
> > Group where you can add the domain users and then you would need to give
> > permissions only to the Group/Role and not to individuals, you will then only
> > need to add the domain user to once to the Group/Role without having to
> > configure permissions for each object.
> > Check out Books Online for more information about Roles and permissions.
> >
> > If you look at the database properties in Enterprise manager you will
> > probably find that your domain account is the database owner. You may want to
> > change this using the sp_changedbowner stored procedure.
> >
> > At this point in time I would change your personal permissions and add the
> > permissions as described above. Make sure that you do know how to log in as
> > the local administrator before you do this.
> >
> > HTH
> >
> > John
> > "Ken-T" 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 admin
> > > 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:
> > >
> > > > 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 your
> > > > 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 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
> > > > >|||Thanks indeed John,
just heard from CA that our restore problem on MSDB is probably user/rights
related too.
Will start the cleanup/tests and see if I can set up a mini server for
testing.
Hope that next post is a simpler issue.
Ken
"John Bell" wrote:
> Hi
> If you know the local administrators account and don't delete or change the
> BUILTIN\Administrators group from the server then you should be able to get
> back in.
> Backing up the database will retain the permissions on the objects when they
> were backed up. If possibly I would take this off the live server and test it
> on a different machine see http://support.microsoft.com/kb/314546, and watch
> out for orphaned users as detailed in the article. Failing that restore a
> backup of the live database
> as a different database and then you will not affect other users.
> John
> "Ken-T" wrote:
> > John, really, thanks for the help with this. Is much appreciated.
> >
> > I need to sorts out the accounts/groups and do a bit of testing.
> > My only worry is locking myself out of SQL or making it unable to run.
> >
> > So, another question - if I backup all db's now and actually make phsical
> > file copies while SQL server is not running - then will the security/user
> > settings will be back as they were if I roll back'
> > If so - will know that I have an emergency way back, just in case.
> >
> > Ken
> >
> >
> >
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Ken
> > >
> > > Having full time domain administration rights is not really good practice.
> > > You will have access to the database via the BUILDIN\Administrators group.
> > > You can look at creating a Database Role and/or a Active Directory Security
> > > Group where you can add the domain users and then you would need to give
> > > permissions only to the Group/Role and not to individuals, you will then only
> > > need to add the domain user to once to the Group/Role without having to
> > > configure permissions for each object.
> > > Check out Books Online for more information about Roles and permissions.
> > >
> > > If you look at the database properties in Enterprise manager you will
> > > probably find that your domain account is the database owner. You may want to
> > > change this using the sp_changedbowner stored procedure.
> > >
> > > At this point in time I would change your personal permissions and add the
> > > permissions as described above. Make sure that you do know how to log in as
> > > the local administrator before you do this.
> > >
> > > HTH
> > >
> > > John
> > > "Ken-T" 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 admin
> > > > 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:
> > > >
> > > > > 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 your
> > > > > 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 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
> > > > > >|||Hi
Reinstalling the backup software would hopefully correct any problems that
has with permissions, but you may want to see if you can use a system that
backs up to disc and then use the normal backup.
John
"Ken-T" wrote:
> Thanks indeed John,
> just heard from CA that our restore problem on MSDB is probably user/rights
> related too.
> Will start the cleanup/tests and see if I can set up a mini server for
> testing.
> Hope that next post is a simpler issue.
> Ken
> "John Bell" wrote:
> > Hi
> >
> > If you know the local administrators account and don't delete or change the
> > BUILTIN\Administrators group from the server then you should be able to get
> > back in.
> >
> > Backing up the database will retain the permissions on the objects when they
> > were backed up. If possibly I would take this off the live server and test it
> > on a different machine see http://support.microsoft.com/kb/314546, and watch
> > out for orphaned users as detailed in the article. Failing that restore a
> > backup of the live database
> > as a different database and then you will not affect other users.
> >
> > John
> >
> > "Ken-T" wrote:
> >
> > > John, really, thanks for the help with this. Is much appreciated.
> > >
> > > I need to sorts out the accounts/groups and do a bit of testing.
> > > My only worry is locking myself out of SQL or making it unable to run.
> > >
> > > So, another question - if I backup all db's now and actually make phsical
> > > file copies while SQL server is not running - then will the security/user
> > > settings will be back as they were if I roll back'
> > > If so - will know that I have an emergency way back, just in case.
> > >
> > > Ken
> > >
> > >
> > >
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Ken
> > > >
> > > > Having full time domain administration rights is not really good practice.
> > > > You will have access to the database via the BUILDIN\Administrators group.
> > > > You can look at creating a Database Role and/or a Active Directory Security
> > > > Group where you can add the domain users and then you would need to give
> > > > permissions only to the Group/Role and not to individuals, you will then only
> > > > need to add the domain user to once to the Group/Role without having to
> > > > configure permissions for each object.
> > > > Check out Books Online for more information about Roles and permissions.
> > > >
> > > > If you look at the database properties in Enterprise manager you will
> > > > probably find that your domain account is the database owner. You may want to
> > > > change this using the sp_changedbowner stored procedure.
> > > >
> > > > At this point in time I would change your personal permissions and add the
> > > > permissions as described above. Make sure that you do know how to log in as
> > > > the local administrator before you do this.
> > > >
> > > > HTH
> > > >
> > > > John
> > > > "Ken-T" 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 admin
> > > > > 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:
> > > > >
> > > > > > 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 your
> > > > > > 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 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
> > > > > > >

Problems with SQL users, groups and table access

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:
>

Friday, March 23, 2012

Problems with quick record count (no table info in sysindexes)

Hi everybody,
In my program, I use the following statement to get a quick count of the
records in a given table:
SELECT [rows] FROM sysindexes WHERE [indid] = 0 AND [name] = 'table_name'
I don't know if it's just a bug with SQL, but it sometimes happens that when
there are changes to a particular table, there is no entry for that table in
sysindexes. I know this is pretty weird because BOL states that every table
should have an entry in sysindexes. If I drop the DB and then recreate it,
everything's OK but this is, of course, no realistic solution every time I
alter a table.
Does anyone else have this problem? Is there anything I can run to
repopulate sysindexes? Was this solved with some SP?
Any help would be much appreciated.
TIA,
sivrik.> In my program, I use the following statement to get a quick count of the
> records in a given table:
Why not just to SELECT COUNT(*) FROM TableName
or look at sp_spaceused system stored procedure in the BOL
Note: It's not recomended to query system tables instead use
INFORMATION_SCHEMA views. (See examples in the BOL)
"sivrik" <sivrik@.discussions.microsoft.com> wrote in message
news:7BB560F0-ADB7-43CB-B0FA-8BD687F65699@.microsoft.com...
> Hi everybody,
> In my program, I use the following statement to get a quick count of the
> records in a given table:
> SELECT [rows] FROM sysindexes WHERE [indid] = 0 AND [name] = 'table_name'
> I don't know if it's just a bug with SQL, but it sometimes happens that
when
> there are changes to a particular table, there is no entry for that table
in
> sysindexes. I know this is pretty weird because BOL states that every
table
> should have an entry in sysindexes. If I drop the DB and then recreate
it,
> everything's OK but this is, of course, no realistic solution every time I
> alter a table.
> Does anyone else have this problem? Is there anything I can run to
> repopulate sysindexes? Was this solved with some SP?
> Any help would be much appreciated.
> TIA,
> sivrik.|||Uri,
Thanks for the reply. I don't use SELECT COUNT(*) because there can be a
lot of data in a particular table and it takes forever. Thanks for the tip
on sp_spaceused. Is there a way to get it to return only the rowcount? I
need to set the value to a variable. I know this won't work but something
like:
select [rows] from (exec sp_spaceused 'table_name')
Or maybe there's another way to get this value quickly. I took a quick look
at INFORMATION_SCHEMA but couldn't find anything helpful.
Thanks again,
sivrik.
"Uri Dimant" wrote:

> Why not just to SELECT COUNT(*) FROM TableName
> or look at sp_spaceused system stored procedure in the BOL
>
> Note: It's not recomended to query system tables instead use
> INFORMATION_SCHEMA views. (See examples in the BOL)
>
> "sivrik" <sivrik@.discussions.microsoft.com> wrote in message
> news:7BB560F0-ADB7-43CB-B0FA-8BD687F65699@.microsoft.com...
> when
> in
> table
> it,
>
>|||I think your problem is that you are assuming that indid is always the table
.
If the table has a clustered index, then the 0 is changed to 1. 0 for indid
indicates the table has no clustered index and is a heap. When a clustered
index is added the 0 is changed.
select count(*) is the best way to get a quick count of rows. I uses index
information if possible.
"sivrik" wrote:

> Hi everybody,
> In my program, I use the following statement to get a quick count of the
> records in a given table:
> SELECT [rows] FROM sysindexes WHERE [indid] = 0 AND [name] = 'table_name'
> I don't know if it's just a bug with SQL, but it sometimes happens that wh
en
> there are changes to a particular table, there is no entry for that table
in
> sysindexes. I know this is pretty weird because BOL states that every tab
le
> should have an entry in sysindexes. If I drop the DB and then recreate it
,
> everything's OK but this is, of course, no realistic solution every time I
> alter a table.
> Does anyone else have this problem? Is there anything I can run to
> repopulate sysindexes? Was this solved with some SP?
> Any help would be much appreciated.
> TIA,
> sivrik.|||Mike,
Thanks for the reply. I did make that assumption but the problem is that
ALL information for the particular table "disappears" from sysindexes. So
SELECT * FROM sysindexes WHERE [name] = 'Table_Name'
returns absolutely nothing. SELECT COUNT(*) from a table with a lot of data
in it takes forever so that's why I was looking for a quicker way.
sivrik.
"Mike Hanson" wrote:
> I think your problem is that you are assuming that indid is always the tab
le.
> If the table has a clustered index, then the 0 is changed to 1. 0 for ind
id
> indicates the table has no clustered index and is a heap. When a clustere
d
> index is added the 0 is changed.
> select count(*) is the best way to get a quick count of rows. I uses inde
x
> information if possible.
> "sivrik" wrote:
>|||Hi
You will have to create a 'template' table with all columns that output from
sp_spaceused
INSERT INTO #Temp EXEC sp_spaceused 'table name'
SELECT COUNT(rows) FROM #Temp
Another way is to create an indexed view that will produce a more efficient
execution plan.
This example has written by Steve Kass.
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"sivrik" <sivrik@.discussions.microsoft.com> wrote in message
news:0F09E3E7-2B20-41BD-98E6-0354136D8D2C@.microsoft.com...
> Uri,
> Thanks for the reply. I don't use SELECT COUNT(*) because there can be a
> lot of data in a particular table and it takes forever. Thanks for the
tip
> on sp_spaceused. Is there a way to get it to return only the rowcount? I
> need to set the value to a variable. I know this won't work but something
> like:
> select [rows] from (exec sp_spaceused 'table_name')
> Or maybe there's another way to get this value quickly. I took a quick
look
> at INFORMATION_SCHEMA but couldn't find anything helpful.
> Thanks again,
> sivrik.
> "Uri Dimant" wrote:
>
the
the
'table_name'
that
table
recreate
time I|||Hi Sivrik
I have never heard of information dissappearing from sysindexes before, at
the risk of offending you, are you sure you are running the select within th
e
right database context. I do this all the time in Query Analyzer, forgettin
g
to change the context, perhaps your application connection string is not
setting the correct database context for the query.
I usally find count(*) to be quite fast if a clustered index exists on the
table and statistics are up to date. Usually slow performance of this
function means it is actually having to update the statistics (if automatic
statistics are enabled) or do a manual count, because statistics are out of
date or do not exist.
Mike Hanson
"sivrik" wrote:
> Mike,
> Thanks for the reply. I did make that assumption but the problem is that
> ALL information for the particular table "disappears" from sysindexes. So
> SELECT * FROM sysindexes WHERE [name] = 'Table_Name'
> returns absolutely nothing. SELECT COUNT(*) from a table with a lot of da
ta
> in it takes forever so that's why I was looking for a quicker way.
> sivrik.
>
> "Mike Hanson" wrote:
>|||sivrik try
create table #temp(name varchar(100), rows int, reserved varchar(100), data
varchar(100), index_size varchar(100), unused varchar(100))
insert #temp(name, rows, reserved, data, index_size, unused)
exec sp_spaceused 'your table'
select rows from #temp
Regards
Meir
"sivrik" <sivrik@.discussions.microsoft.com> wrote in message
news:0F09E3E7-2B20-41BD-98E6-0354136D8D2C@.microsoft.com...
> Uri,
> Thanks for the reply. I don't use SELECT COUNT(*) because there can be a
> lot of data in a particular table and it takes forever. Thanks for the
tip
> on sp_spaceused. Is there a way to get it to return only the rowcount? I
> need to set the value to a variable. I know this won't work but something
> like:
> select [rows] from (exec sp_spaceused 'table_name')
> Or maybe there's another way to get this value quickly. I took a quick
look
> at INFORMATION_SCHEMA but couldn't find anything helpful.
> Thanks again,
> sivrik.
> "Uri Dimant" wrote:
>
the
the
'table_name'
that
table
recreate
time I|||No offense taken, I'm sure we've all forgot to swith the DB in query analyze
r
before. =) No, it's not that I'm running it in the wrong context. It's
actually that there is no more information about the table in sysindexes. I
t
seems strange to me too so that's why I was asking.
Thanks,
sivrik.
"Mike Hanson" wrote:

> Hi Sivrik
> I have never heard of information dissappearing from sysindexes before, at
> the risk of offending you, are you sure you are running the select within
the
> right database context. I do this all the time in Query Analyzer, forgett
ing
> to change the context, perhaps your application connection string is not
> setting the correct database context for the query.
> I usally find count(*) to be quite fast if a clustered index exists on the
> table and statistics are up to date. Usually slow performance of this
> function means it is actually having to update the statistics (if automati
c
> statistics are enabled) or do a manual count, because statistics are out o
f
> date or do not exist.
> Mike Hanson
>|||Also you need to be aware that the information you get from spaceused (which
comes from sysindexes ) is not updated with each insert, update, or delete,
and will often be close , but not exact... ( It can be very far off if you
have done a truncate table.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"sivrik" <sivrik@.discussions.microsoft.com> wrote in message
news:B7F509C2-6C9E-4B0B-B091-0A8DFBB8F790@.microsoft.com...
> No offense taken, I'm sure we've all forgot to swith the DB in query
> analyzer
> before. =) No, it's not that I'm running it in the wrong context. It's
> actually that there is no more information about the table in sysindexes.
> It
> seems strange to me too so that's why I was asking.
> Thanks,
> sivrik.
> "Mike Hanson" wrote:
>
>

Friday, March 30, 2012

Problems with TRIGGER

Problems with TRIGGER

problems with the subqueries

Problems with Table Adapters

Problems with Switching partitions

Problems With Sum

Wednesday, March 28, 2012

Problems with SQL users, groups and table access

Problems with SQL users, groups and table access

Friday, March 23, 2012

Problems with quick record count (no table info in sysindexes)

Problems using SQL

Blog Archive

About Me