Pervasive "Arithmetic overflow occurred." message

I'm encountering a problem with a SQL Server 2000 installation. The steps
to reproduce are simple: log in to the server via OSQL or "Query Analyzer".
Run "sp_help". I immediately receive several "Arithmetic overflow
occurred." messages followed by the standard results expected from
"sp_help". All other queries also report the same error message, though the
results seem to be correct. Doesn't matter what DB I'm running against.
Doesn't matter what ID I've logged in with. I've tried different client
machines. I don't see the problem when I connect to other SQL Server 2000
installations. I also tried it from the console of the SQL Server machine
itself w/"Query Analyzer". Same result.
What would cause this and how do I correct it?
Thanks,
--David
I'd suggest applying the latest service pack
You can get it here :-
http://www.microsoft.com/technet/pro...s/default.mspx
HTH. Ryan
"David Ehrlich" <djehrlich@.hotmail.com> wrote in message
news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
> I'm encountering a problem with a SQL Server 2000 installation. The steps
> to reproduce are simple: log in to the server via OSQL or "Query
> Analyzer". Run "sp_help". I immediately receive several "Arithmetic
> overflow occurred." messages followed by the standard results expected
> from "sp_help". All other queries also report the same error message,
> though the results seem to be correct. Doesn't matter what DB I'm running
> against. Doesn't matter what ID I've logged in with. I've tried different
> client machines. I don't see the problem when I connect to other SQL
> Server 2000 installations. I also tried it from the console of the SQL
> Server machine itself w/"Query Analyzer". Same result.
> What would cause this and how do I correct it?
> Thanks,
> --David
>
|||Are you able gauge which line on sp_help the error is occurring?
If so, you could open the stored procedure and see the source of the problem
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/remotedba.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David Ehrlich" <djehrlich@.hotmail.com> wrote in message
news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
> I'm encountering a problem with a SQL Server 2000 installation. The steps
> to reproduce are simple: log in to the server via OSQL or "Query
Analyzer".
> Run "sp_help". I immediately receive several "Arithmetic overflow
> occurred." messages followed by the standard results expected from
> "sp_help". All other queries also report the same error message, though
the
> results seem to be correct. Doesn't matter what DB I'm running against.
> Doesn't matter what ID I've logged in with. I've tried different client
> machines. I don't see the problem when I connect to other SQL Server 2000
> installations. I also tried it from the console of the SQL Server machine
> itself w/"Query Analyzer". Same result.
> What would cause this and how do I correct it?
> Thanks,
> --David
>
|||Looks to me like we're current (at least for SQL Server 2000):
SELECT @.@.VERSION
GO
Arithmetic overflow occurred.
<...snip...>
Arithmetic overflow occurred.
-----------
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23ZQVmF4GGHA.2212@.TK2MSFTNGP15.phx.gbl...
> I'd suggest applying the latest service pack
> You can get it here :-
> http://www.microsoft.com/technet/pro...s/default.mspx
> --
> HTH. Ryan
> "David Ehrlich" <djehrlich@.hotmail.com> wrote in message
> news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
>
|||This happens on _any_ query, stored procedure, etc. For instance (pieced
together from "Query Analyzer"):
SELECT @.@.VERSION
GO
Arithmetic overflow occurred.
<...snip...>
Arithmetic overflow occurred.
-----------
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
"Jack Vamvas" <info@.nospam.com> wrote in message
news:dqj6l6$2v3$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> Are you able gauge which line on sp_help the error is occurring?
> If so, you could open the stored procedure and see the source of the
> problem
>
> Jack Vamvas
> __________________________________________________ ________________
> Receive free SQL tips - register at www.ciquery.com/remotedba.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
>
> "David Ehrlich" <djehrlich@.hotmail.com> wrote in message
> news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
> Analyzer".
> the
>
|||I did two things and the problem disappeared:
1. I installed the latest _security_ patches (the latest service patches
were
already installed).
2. As required by the security patches, I rebooted the machine.
Seems odd that the problem would be cured by one of these two actions.
Good thing this wasn't a production SQL Server.
--David
"David Ehrlich" <djehrlich@.hotmail.com> wrote in message
news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
> I'm encountering a problem with a SQL Server 2000 installation. The steps
> to reproduce are simple: log in to the server via OSQL or "Query
> Analyzer". Run "sp_help". I immediately receive several "Arithmetic
> overflow occurred." messages followed by the standard results expected
> from "sp_help". All other queries also report the same error message,
> though the results seem to be correct. Doesn't matter what DB I'm running
> against. Doesn't matter what ID I've logged in with. I've tried different
> client machines. I don't see the problem when I connect to other SQL
> Server 2000 installations. I also tried it from the console of the SQL
> Server machine itself w/"Query Analyzer". Same result.
> What would cause this and how do I correct it?
> Thanks,
> --David
>
|||David,
Just seen this KB http://support.microsoft.com/kb/911851/
Chris
"David Ehrlich" <djehrlich@.hotmail.com> wrote in message
news:e55FzMIHGHA.1288@.TK2MSFTNGP09.phx.gbl...
>I did two things and the problem disappeared:
> 1. I installed the latest _security_ patches (the latest service patches
> were
> already installed).
> 2. As required by the security patches, I rebooted the machine.
> Seems odd that the problem would be cured by one of these two actions.
> Good thing this wasn't a production SQL Server.
> --David
> "David Ehrlich" <djehrlich@.hotmail.com> wrote in message
> news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
>
>

Pervasive "Arithmetic overflow occurred." message

I'm encountering a problem with a SQL Server 2000 installation. The steps
to reproduce are simple: log in to the server via OSQL or "Query Analyzer".
Run "sp_help". I immediately receive several "Arithmetic overflow
occurred." messages followed by the standard results expected from
"sp_help". All other queries also report the same error message, though the
results seem to be correct. Doesn't matter what DB I'm running against.
Doesn't matter what ID I've logged in with. I've tried different client
machines. I don't see the problem when I connect to other SQL Server 2000
installations. I also tried it from the console of the SQL Server machine
itself w/"Query Analyzer". Same result.
What would cause this and how do I correct it?
Thanks,
--DavidI'd suggest applying the latest service pack
You can get it here :-
http://www.microsoft.com/technet/prodtechnol/sql/2000/downloads/default.mspx
--
HTH. Ryan
"David Ehrlich" <djehrlich@.hotmail.com> wrote in message
news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
> I'm encountering a problem with a SQL Server 2000 installation. The steps
> to reproduce are simple: log in to the server via OSQL or "Query
> Analyzer". Run "sp_help". I immediately receive several "Arithmetic
> overflow occurred." messages followed by the standard results expected
> from "sp_help". All other queries also report the same error message,
> though the results seem to be correct. Doesn't matter what DB I'm running
> against. Doesn't matter what ID I've logged in with. I've tried different
> client machines. I don't see the problem when I connect to other SQL
> Server 2000 installations. I also tried it from the console of the SQL
> Server machine itself w/"Query Analyzer". Same result.
> What would cause this and how do I correct it?
> Thanks,
> --David
>|||Are you able gauge which line on sp_help the error is occurring?
If so, you could open the stored procedure and see the source of the problem
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/remotedba.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David Ehrlich" <djehrlich@.hotmail.com> wrote in message
news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
> I'm encountering a problem with a SQL Server 2000 installation. The steps
> to reproduce are simple: log in to the server via OSQL or "Query
Analyzer".
> Run "sp_help". I immediately receive several "Arithmetic overflow
> occurred." messages followed by the standard results expected from
> "sp_help". All other queries also report the same error message, though
the
> results seem to be correct. Doesn't matter what DB I'm running against.
> Doesn't matter what ID I've logged in with. I've tried different client
> machines. I don't see the problem when I connect to other SQL Server 2000
> installations. I also tried it from the console of the SQL Server machine
> itself w/"Query Analyzer". Same result.
> What would cause this and how do I correct it?
> Thanks,
> --David
>|||Looks to me like we're current (at least for SQL Server 2000):
SELECT @.@.VERSION
GO
Arithmetic overflow occurred.
<...snip...>
Arithmetic overflow occurred.
------------
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23ZQVmF4GGHA.2212@.TK2MSFTNGP15.phx.gbl...
> I'd suggest applying the latest service pack
> You can get it here :-
> http://www.microsoft.com/technet/prodtechnol/sql/2000/downloads/default.mspx
> --
> HTH. Ryan
> "David Ehrlich" <djehrlich@.hotmail.com> wrote in message
> news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
>> I'm encountering a problem with a SQL Server 2000 installation. The
>> steps to reproduce are simple: log in to the server via OSQL or "Query
>> Analyzer". Run "sp_help". I immediately receive several "Arithmetic
>> overflow occurred." messages followed by the standard results expected
>> from "sp_help". All other queries also report the same error message,
>> though the results seem to be correct. Doesn't matter what DB I'm
>> running against. Doesn't matter what ID I've logged in with. I've tried
>> different client machines. I don't see the problem when I connect to
>> other SQL Server 2000 installations. I also tried it from the console of
>> the SQL Server machine itself w/"Query Analyzer". Same result.
>> What would cause this and how do I correct it?
>> Thanks,
>> --David
>>
>|||This happens on _any_ query, stored procedure, etc. For instance (pieced
together from "Query Analyzer"):
SELECT @.@.VERSION
GO
Arithmetic overflow occurred.
<...snip...>
Arithmetic overflow occurred.
------------
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
"Jack Vamvas" <info@.nospam.com> wrote in message
news:dqj6l6$2v3$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> Are you able gauge which line on sp_help the error is occurring?
> If so, you could open the stored procedure and see the source of the
> problem
>
> Jack Vamvas
> __________________________________________________________________
> Receive free SQL tips - register at www.ciquery.com/remotedba.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
>
> "David Ehrlich" <djehrlich@.hotmail.com> wrote in message
> news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
>> I'm encountering a problem with a SQL Server 2000 installation. The
>> steps
>> to reproduce are simple: log in to the server via OSQL or "Query
> Analyzer".
>> Run "sp_help". I immediately receive several "Arithmetic overflow
>> occurred." messages followed by the standard results expected from
>> "sp_help". All other queries also report the same error message, though
> the
>> results seem to be correct. Doesn't matter what DB I'm running against.
>> Doesn't matter what ID I've logged in with. I've tried different client
>> machines. I don't see the problem when I connect to other SQL Server
>> 2000
>> installations. I also tried it from the console of the SQL Server
>> machine
>> itself w/"Query Analyzer". Same result.
>> What would cause this and how do I correct it?
>> Thanks,
>> --David
>>
>|||I did two things and the problem disappeared:
1. I installed the latest _security_ patches (the latest service patches
were
already installed).
2. As required by the security patches, I rebooted the machine.
Seems odd that the problem would be cured by one of these two actions.
Good thing this wasn't a production SQL Server.
--David
"David Ehrlich" <djehrlich@.hotmail.com> wrote in message
news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
> I'm encountering a problem with a SQL Server 2000 installation. The steps
> to reproduce are simple: log in to the server via OSQL or "Query
> Analyzer". Run "sp_help". I immediately receive several "Arithmetic
> overflow occurred." messages followed by the standard results expected
> from "sp_help". All other queries also report the same error message,
> though the results seem to be correct. Doesn't matter what DB I'm running
> against. Doesn't matter what ID I've logged in with. I've tried different
> client machines. I don't see the problem when I connect to other SQL
> Server 2000 installations. I also tried it from the console of the SQL
> Server machine itself w/"Query Analyzer". Same result.
> What would cause this and how do I correct it?
> Thanks,
> --David
>|||David,
Just seen this KB http://support.microsoft.com/kb/911851/
Chris
"David Ehrlich" <djehrlich@.hotmail.com> wrote in message
news:e55FzMIHGHA.1288@.TK2MSFTNGP09.phx.gbl...
>I did two things and the problem disappeared:
> 1. I installed the latest _security_ patches (the latest service patches
> were
> already installed).
> 2. As required by the security patches, I rebooted the machine.
> Seems odd that the problem would be cured by one of these two actions.
> Good thing this wasn't a production SQL Server.
> --David
> "David Ehrlich" <djehrlich@.hotmail.com> wrote in message
> news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
>> I'm encountering a problem with a SQL Server 2000 installation. The
>> steps
>> to reproduce are simple: log in to the server via OSQL or "Query
>> Analyzer". Run "sp_help". I immediately receive several "Arithmetic
>> overflow occurred." messages followed by the standard results expected
>> from "sp_help". All other queries also report the same error message,
>> though the results seem to be correct. Doesn't matter what DB I'm
>> running
>> against. Doesn't matter what ID I've logged in with. I've tried
>> different
>> client machines. I don't see the problem when I connect to other SQL
>> Server 2000 installations. I also tried it from the console of the SQL
>> Server machine itself w/"Query Analyzer". Same result.
>> What would cause this and how do I correct it?
>> Thanks,
>> --David
>>
>
>

Pervasive "Arithmetic overflow occurred." message

I'm encountering a problem with a SQL Server 2000 installation. The steps
to reproduce are simple: log in to the server via OSQL or "Query Analyzer".
Run "sp_help". I immediately receive several "Arithmetic overflow
occurred." messages followed by the standard results expected from
"sp_help". All other queries also report the same error message, though the
results seem to be correct. Doesn't matter what DB I'm running against.
Doesn't matter what ID I've logged in with. I've tried different client
machines. I don't see the problem when I connect to other SQL Server 2000
installations. I also tried it from the console of the SQL Server machine
itself w/"Query Analyzer". Same result.
What would cause this and how do I correct it?
Thanks,
--DavidI'd suggest applying the latest service pack
You can get it here :-
http://www.microsoft.com/technet/pr...ds/default.mspx
HTH. Ryan
"David Ehrlich" <djehrlich@.hotmail.com> wrote in message
news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
> I'm encountering a problem with a SQL Server 2000 installation. The steps
> to reproduce are simple: log in to the server via OSQL or "Query
> Analyzer". Run "sp_help". I immediately receive several "Arithmetic
> overflow occurred." messages followed by the standard results expected
> from "sp_help". All other queries also report the same error message,
> though the results seem to be correct. Doesn't matter what DB I'm running
> against. Doesn't matter what ID I've logged in with. I've tried different
> client machines. I don't see the problem when I connect to other SQL
> Server 2000 installations. I also tried it from the console of the SQL
> Server machine itself w/"Query Analyzer". Same result.
> What would cause this and how do I correct it?
> Thanks,
> --David
>|||Are you able gauge which line on sp_help the error is occurring?
If so, you could open the stored procedure and see the source of the problem
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/remotedba.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David Ehrlich" <djehrlich@.hotmail.com> wrote in message
news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
> I'm encountering a problem with a SQL Server 2000 installation. The steps
> to reproduce are simple: log in to the server via OSQL or "Query
Analyzer".
> Run "sp_help". I immediately receive several "Arithmetic overflow
> occurred." messages followed by the standard results expected from
> "sp_help". All other queries also report the same error message, though
the
> results seem to be correct. Doesn't matter what DB I'm running against.
> Doesn't matter what ID I've logged in with. I've tried different client
> machines. I don't see the problem when I connect to other SQL Server 2000
> installations. I also tried it from the console of the SQL Server machine
> itself w/"Query Analyzer". Same result.
> What would cause this and how do I correct it?
> Thanks,
> --David
>|||Looks to me like we're current (at least for SQL Server 2000):
SELECT @.@.VERSION
GO
Arithmetic overflow occurred.
<...snip...>
Arithmetic overflow occurred.
----
----
----
--
---
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23ZQVmF4GGHA.2212@.TK2MSFTNGP15.phx.gbl...
> I'd suggest applying the latest service pack
> You can get it here :-
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/downloads/default.mspx[/ur
l]
> --
> HTH. Ryan
> "David Ehrlich" <djehrlich@.hotmail.com> wrote in message
> news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
>|||This happens on _any_ query, stored procedure, etc. For instance (pieced
together from "Query Analyzer"):
SELECT @.@.VERSION
GO
Arithmetic overflow occurred.
<...snip...>
Arithmetic overflow occurred.
----
----
----
--
---
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
"Jack Vamvas" <info@.nospam.com> wrote in message
news:dqj6l6$2v3$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> Are you able gauge which line on sp_help the error is occurring?
> If so, you could open the stored procedure and see the source of the
> problem
>
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/remotedba.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
>
> "David Ehrlich" <djehrlich@.hotmail.com> wrote in message
> news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
> Analyzer".
> the
>|||I did two things and the problem disappeared:
1. I installed the latest _security_ patches (the latest service patches
were
already installed).
2. As required by the security patches, I rebooted the machine.
Seems odd that the problem would be cured by one of these two actions.
Good thing this wasn't a production SQL Server.
--David
"David Ehrlich" <djehrlich@.hotmail.com> wrote in message
news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
> I'm encountering a problem with a SQL Server 2000 installation. The steps
> to reproduce are simple: log in to the server via OSQL or "Query
> Analyzer". Run "sp_help". I immediately receive several "Arithmetic
> overflow occurred." messages followed by the standard results expected
> from "sp_help". All other queries also report the same error message,
> though the results seem to be correct. Doesn't matter what DB I'm running
> against. Doesn't matter what ID I've logged in with. I've tried different
> client machines. I don't see the problem when I connect to other SQL
> Server 2000 installations. I also tried it from the console of the SQL
> Server machine itself w/"Query Analyzer". Same result.
> What would cause this and how do I correct it?
> Thanks,
> --David
>|||David,
Just seen this KB http://support.microsoft.com/kb/911851/
Chris
"David Ehrlich" <djehrlich@.hotmail.com> wrote in message
news:e55FzMIHGHA.1288@.TK2MSFTNGP09.phx.gbl...
>I did two things and the problem disappeared:
> 1. I installed the latest _security_ patches (the latest service patches
> were
> already installed).
> 2. As required by the security patches, I rebooted the machine.
> Seems odd that the problem would be cured by one of these two actions.
> Good thing this wasn't a production SQL Server.
> --David
> "David Ehrlich" <djehrlich@.hotmail.com> wrote in message
> news:uIzZU$3GGHA.740@.TK2MSFTNGP12.phx.gbl...
>
>

Per-record processing?

Hi,

I am looking for a way to process each record from a SELECT query. Each record has to go through a stored procedure to be validated and different actions have to be taken depending on whether or not it passes the validation.

I'm a bit new to Transact SQL. My background is in programming, so I keep wanting to visualize a loop, which doesn't exist in TSQL, from what I understand. Can anyone help me understand how something of this type would be done?

Thank you!

SQL Server is designed for SET based operation. 'Per-Record' operations are often best left to a client application.

However, in some situations, it may be necessary to (a) gather relavent records into a temporary table, (b) set a counter variable with the @.@.Rowcount from the temporary table, (c) use a WHILE @.CurrentRecord < @.RecordCount type of loop.

However, this is guarenteed to be a non-optimal use of SQL Server. Perhaps if you were to post the select query, the stored procedure, and some sample data, folks here would help you properly design a SET based operation -if possible.

|||Thank you. I think this is just what I needed to move this into a .Net app.sql

Permissions WTF

I have a intigrated log using a local group
the login does not have explicit access to any db's and no server roles
when i log on with query analyzer i can access any data (even though not a
server admin or anything else)
when i remove login...can log (as it should be)
why does this login have full access to all db's ?When i add the login and run IS_MEMBER and USER it says i am dbo of a user
database that i did not give access to what is going on ?
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uNI5pih6FHA.3684@.TK2MSFTNGP12.phx.gbl...
>I have a intigrated log using a local group
> the login does not have explicit access to any db's and no server roles
> when i log on with query analyzer i can access any data (even though not a
> server admin or anything else)
> when i remove login...can log (as it should be)
> why does this login have full access to all db's ?
>|||You are getting in with sysadmin priviliges, possibly by membership of
another group. What does the following query return for your windows
account?
exec master..xp_logininfo 'DOMAIN\username'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uNI5pih6FHA.3684@.TK2MSFTNGP12.phx.gbl...
>I have a intigrated log using a local group
> the login does not have explicit access to any db's and no server roles
> when i log on with query analyzer i can access any data (even though not a
> server admin or anything else)
> when i remove login...can log (as it should be)
> why does this login have full access to all db's ?
>|||Thanks for reply, but it was my stupidity...i had been logged in as the
user when i had created the user databases...then forgot it was a member of
the group used to create the login....should have realized it when it said
i was member of db_owner and not srv role sysadmin...but i am getting slow
at my old age.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:upEiioj6FHA.476@.TK2MSFTNGP15.phx.gbl...
> You are getting in with sysadmin priviliges, possibly by membership of
> another group. What does the following query return for your windows
> account?
> exec master..xp_logininfo 'DOMAIN\username'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
> news:uNI5pih6FHA.3684@.TK2MSFTNGP12.phx.gbl...
>

permissions via roles query (SQL Server 2000)

Is anybody willing to share a query which shows all permissions granted
to a user, including permissions granted via roles? The complexity is
that a role can be granted to a role, and therefore this becomes a
bill-of-materials explosion / tree hierarchy / adjacency list problem.
Example:
create role r1
grant select on t1 to r1
grant select on t2 to r1
create role r2
grant select on t3 to r2
grant select on t4 to r2
create role r3
grant r1 to r3
grant r2 to r3
grant r3 to user1
The end result is that you want to be able to see that user1 has select
on t1, t2, t3, t4. An added bonus would be able to see the lineage.
I have found general discussion about solving these kinds of problems.
I'm curious if anybody has a working example for permissions and roles
in SQLServer 2000.sp_helprotect
<rc8740@.netscape.net> wrote in message
news:1149690495.671940.102730@.i39g2000cwa.googlegroups.com...
> Is anybody willing to share a query which shows all permissions granted
> to a user, including permissions granted via roles? The complexity is
> that a role can be granted to a role, and therefore this becomes a
> bill-of-materials explosion / tree hierarchy / adjacency list problem.
> Example:
> create role r1
> grant select on t1 to r1
> grant select on t2 to r1
> create role r2
> grant select on t3 to r2
> grant select on t4 to r2
> create role r3
> grant r1 to r3
> grant r2 to r3
> grant r3 to user1
> The end result is that you want to be able to see that user1 has select
> on t1, t2, t3, t4. An added bonus would be able to see the lineage.
> I have found general discussion about solving these kinds of problems.
> I'm curious if anybody has a working example for permissions and roles
> in SQLServer 2000.
>|||There are some good ones up on sqlservercentral.com
It sounds like you may be looking for one like this one:
http://www.sqlservercentral.com/scr...butions/268.asp
-Sue
On 7 Jun 2006 07:28:15 -0700, rc8740@.netscape.net wrote:

>Is anybody willing to share a query which shows all permissions granted
>to a user, including permissions granted via roles? The complexity is
>that a role can be granted to a role, and therefore this becomes a
>bill-of-materials explosion / tree hierarchy / adjacency list problem.
>Example:
>create role r1
>grant select on t1 to r1
>grant select on t2 to r1
>create role r2
>grant select on t3 to r2
>grant select on t4 to r2
>create role r3
>grant r1 to r3
>grant r2 to r3
>grant r3 to user1
>The end result is that you want to be able to see that user1 has select
>on t1, t2, t3, t4. An added bonus would be able to see the lineage.
>I have found general discussion about solving these kinds of problems.
>I'm curious if anybody has a working example for permissions and roles
>in SQLServer 2000.|||There are some good ones up on sqlservercentral.com
It sounds like you may be looking for one like this one:
http://www.sqlservercentral.com/scr...butions/268.asp
-Sue
On 7 Jun 2006 07:28:15 -0700, rc8740@.netscape.net wrote:

>Is anybody willing to share a query which shows all permissions granted
>to a user, including permissions granted via roles? The complexity is
>that a role can be granted to a role, and therefore this becomes a
>bill-of-materials explosion / tree hierarchy / adjacency list problem.
>Example:
>create role r1
>grant select on t1 to r1
>grant select on t2 to r1
>create role r2
>grant select on t3 to r2
>grant select on t4 to r2
>create role r3
>grant r1 to r3
>grant r2 to r3
>grant r3 to user1
>The end result is that you want to be able to see that user1 has select
>on t1, t2, t3, t4. An added bonus would be able to see the lineage.
>I have found general discussion about solving these kinds of problems.
>I'm curious if anybody has a working example for permissions and roles
>in SQLServer 2000.|||I was really hopeful when I found this...but I'm getting zero records when
I run the SP created by the script.... any other tools or suggestions?
Thanks.
Neil
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:u7sr8217iivmuojp2cq7msh6mc7vplde0f@.
4ax.com...
> There are some good ones up on sqlservercentral.com
> It sounds like you may be looking for one like this one:
> http://www.sqlservercentral.com/scr...butions/268.asp
> -Sue
> On 7 Jun 2006 07:28:15 -0700, rc8740@.netscape.net wrote:
>
>|||I was really hopeful when I found this...but I'm getting zero records when
I run the SP created by the script.... any other tools or suggestions?
Thanks.
Neil
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:u7sr8217iivmuojp2cq7msh6mc7vplde0f@.
4ax.com...
> There are some good ones up on sqlservercentral.com
> It sounds like you may be looking for one like this one:
> http://www.sqlservercentral.com/scr...butions/268.asp
> -Sue
> On 7 Jun 2006 07:28:15 -0700, rc8740@.netscape.net wrote:
>
>

Permissions question on SQL Server 2000

I have created a new SQL Server user that I wanted to have limited access.
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
DonYes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:

>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell wha
t
>access the public role gives a user?
>Thanks
>Don
>

Permissions question on SQL Server 2000

I have created a new SQL Server user that I wanted to have limited access.
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
Don
Yes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:

>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell what
>access the public role gives a user?
>Thanks
>Don
>

Permissions question on SQL Server 2000

I have created a new SQL Server user that I wanted to have limited access.
I noticied that when logged onto Query Analyer as that user that the master
database is still showing.
Does the public role grant the user role this access? How can you tell what
access the public role gives a user?
Thanks
DonYes through the guest user. Guest always exists in tempdb
and master and guest is a member of public.
You can get somewhat of an idea of the permissions by
executing the following in master:
sp_helprotect NULL, 'Public'
By somewhat, there are other qualifiers in stored
procedures, etc which limit what public can do. For example,
public can execute sp_password. However, that's only to
change their own password. A user needs to be a member of
sysadmins or securityadmins to change other users passwords.
-Sue
On Wed, 02 Nov 2005 02:00:40 GMT, "Don"
<dons100@.ameritech.net> wrote:
>I have created a new SQL Server user that I wanted to have limited access.
>I noticied that when logged onto Query Analyer as that user that the master
>database is still showing.
>Does the public role grant the user role this access? How can you tell what
>access the public role gives a user?
>Thanks
>Don
>sql

Permissions Problem

I have got a peculiar problem. I am not able to query my tables without db username prefix like username.tablename. Worse, I have logged in as username.

Any ideas on what could be the problem?

Now I have to give queries like select * from celia.orders instead of just select * from orders.

Thank you.
Celiacan't u use the

use databasename
select * from table

if it was permission, you probably won't be able to see the tables.

Permissions grants, denies don't take effect

Hi,

I'm trying to grant/deny object permissions in a user database using Enterprise Manager and query tool without success (for ‘public’ role and individual sql logins). I’m not getting any error messages. Permission changes just don’t take effect. Although, there are few objects, which already have permissions granted and I'm able to change permissions for these ones.

How can I change the permissions?

you might have a granted permission on a GUEST account

or an NT domain group. please check.

thats why denying wont work

|||there is no permissions granted or denied for the 'guest' account. I don't use domain groups.|||

What are the actual denies that you are trying to set. Did you verify that they appear in the system catalog (server_permissions, database_permissions)? Have you tried applying the denies using TSQL: DENY?

Thanks
Laurentiu

permissions

I have a database with two tables. One is updated only through a form. The other is updated through a form that is attached to a query that is attached to the table. I cannot figure out what permissions are required to update the second table. What permissions should I grant in addition to the permissions that are granted to the first table as I can update the first table.no other permission...just update will work for both...whatevr the way ur updating it..|||What is the query? What application are you using? Does the query use a view on the back-end? If so you need to grant UPDATE on the view and not the base table. Can you explain the conditions more clearly?|||Refer to books online about db_datawriter fixed database role for more information about permissions on tables.|||The query is just used to sort the table for better presentation in the form used to add data to the table. But that form and the query used are not allowed to add records to the table.|||The query is just used to sort the table for better presentation in the form that is used to input new records in that table. The front end im using is access. What should I do?

Permission-problem on simple query inside CLR function

I have created an assembly with permission set safe, and a function inside the assembly.

The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read.

However when I execute my CLR function I get an error saying something like:

"The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed"

I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error.

And my .dll is also signed.

Has this something to do with writing something a config file?

I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?

Where do you execute your CLR proc from, i.e. where do you get the exception?

Niels
|||

Inside Management Studio. Management Studio is running on a different machine than the SQL Server, but the user that I use when logging into Management Studio is dbowner, and the same user that I use to create the assembly and CLR function.

If I extract the SQL from the CLR function and runs it as a basic query in Management Studio, everything works fine.

|||

I have also noted than when I try to create an SqlPermission in the code and run the Asset(), I get another type of error:

System.Security.Permissions.SecurityPermissions, mscorlib....

|||What happens if you try to do a "normal" query from the SSMS you are testing from, against a table in a database on the SQL Server you have deployed your assembly to?

Niels
|||

pnp wrote:

Inside Management Studio. Management Studio is running on a different machine than the SQL Server, but the user that I use when logging into Management Studio is dbowner, and the same user that I use to create the assembly and CLR function.

If I extract the SQL from the CLR function and runs it as a basic query in Management Studio, everything works fine.

Oh, sorry - disregard my previous posting anout executing a "normal query", I didn't read your post properly. I assume, when you say that you run it as a basic query, that you are running it from the same SSMS that you fail when going against the CLR proc?

What if you try and run from the same box that SQL is on?

Niels
|||

Niels, thanks for your suggestions. I have also installed Management Studio on the same server that is running the SQL Server but the error is the same.

However, as I don't have endless time to deal with this problem I have fixed it by setting the permission of the assembly to unsafe. This fixes it. However, from the documentation I have read, safe-permissions should be enough, and I don't really understand why it isn't in my case.

Permission-problem on simple query inside CLR function

I have created an assembly with permission set safe, and a function inside the assembly.

The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read.

However when I execute my CLR function I get an error saying something like:

"The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed"

I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error.

And my .dll is also signed.

Has this something to do with writing something a config file?

I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?

Where do you execute your CLR proc from, i.e. where do you get the exception?

Niels
|||

Inside Management Studio. Management Studio is running on a different machine than the SQL Server, but the user that I use when logging into Management Studio is dbowner, and the same user that I use to create the assembly and CLR function.

If I extract the SQL from the CLR function and runs it as a basic query in Management Studio, everything works fine.

|||

I have also noted than when I try to create an SqlPermission in the code and run the Asset(), I get another type of error:

System.Security.Permissions.SecurityPermissions, mscorlib....

|||What happens if you try to do a "normal" query from the SSMS you are testing from, against a table in a database on the SQL Server you have deployed your assembly to?

Niels
|||

pnp wrote:

Inside Management Studio. Management Studio is running on a different machine than the SQL Server, but the user that I use when logging into Management Studio is dbowner, and the same user that I use to create the assembly and CLR function.

If I extract the SQL from the CLR function and runs it as a basic query in Management Studio, everything works fine.

Oh, sorry - disregard my previous posting anout executing a "normal query", I didn't read your post properly. I assume, when you say that you run it as a basic query, that you are running it from the same SSMS that you fail when going against the CLR proc?

What if you try and run from the same box that SQL is on?

Niels
|||

Niels, thanks for your suggestions. I have also installed Management Studio on the same server that is running the SQL Server but the error is the same.

However, as I don't have endless time to deal with this problem I have fixed it by setting the permission of the assembly to unsafe. This fixes it. However, from the documentation I have read, safe-permissions should be enough, and I don't really understand why it isn't in my case.

Permissioned users... and permissioned stored proceudres

anyone know how to query system tables to retrieve a list of users that are permissioned for a stored procedure?

and vice versa... a list of stored procedure a user has permission to execute?

i searched for it online and came up with nothing... can someone please help. thanks, all.

Below is a statement to get somewhat of an idea of what you are looking for. When the user is setup to have databse level access it does not list the procedures out one by one of what they have access to, because they have access to everything. However if the user is granted access only to specific objects or columns it tells you what they do have permissions to.

select class_desc,prin.[name],obj.[name]

from sys.database_permissions p

left join sys.database_principals prin

on p.grantee_principal_id = prin.principal_id

left join sys.objects obj

on p.major_id = obj.object_id

If this doesn't totally answer your question at least it might help you get an idea of how you can get the information you are looking for.

Let me know if I can be of any further assistance.

Permission error on transfer to protected Excel worksheet

I am new to the forum and hope this issue is in the right place.
I have a SQL query that is transferring data from a SQL table to an Excel
spreadsheet on the same machine. I keep getting the following error unless I
unprotect the worksheet. I don't want to expose the unprotected worksheet to
users.
Server: Msg 7344, Level 16, State 1, Line 342
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' could not INSERT INTO table
'[Microsoft.Jet.OLEDB.4.0]' because of column 'MyColumnA'. The user did not
have permission to write to the column.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=MyColumnA STATUS=DBSTATUS_S_OK],
[COLUMN_NAME=MyColumnB STATUS=DBSTATUS_S_OK], [COLUMN_NAME=MyColumnC
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=MyColumnD STATUS=DBSTATUS_S_OK],
[COLUMN_NAME=MyColumnE STATUS=DBSTATUS_S_OK], ...
The SQL statement I am running under the sa login is as follows. The
spreadsheet has a column header row on row 3 and a couple rows of sample dat
a
that I wish to lock and protect. If I unprotect the sheet, the query runs
without error and inserts the data correctly into the spreadsheet. Any help
will be appreciated.
INSERT INTO
OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 8.0;Database=C:\myfile.xls;'
,'select * from [Import Template$A3:S7]'
)
(
[MyColumnA],
[MyColumnB],
[MyColumnC],
[MyColumnD],
[MyColumnE],
[MyColumnF],
[MyColumnG]
)
SELECT
#MyTempTable.MyColumnA,
#MyTempTable.MyColumnB,
#MyTempTable.MyColumnC,
#MyTempTable.MyColumnD,
#MyTempTable.MyColumnE,
#MyTempTable.MyColumnF,
#MyTempTable.MyColumnG
FROM
#MyTempTable
Sincerely,
GeraldAre you executing the query from Excel or from SQL Server? If not, you
might want to try that. A better solution than Excel protection would
be to limit access to the share on the local machine using Windows
ACLs. This way access is restricted via the file system if users can
connect to that machine through the network. Grant the account that
you are using for SQL Server permissions on that share. The security
offered by all Office applications, such as Access and Excel, is very
weak and has been bypassed long ago by attackers, so it's pretty much
a waste of time if the data truly needs to be secured.
--Mary
On Wed, 2 Nov 2005 13:17:18 -0800, "Gerald Hopkins"
<GeraldHopkins@.discussions.microsoft.com> wrote:

>I am new to the forum and hope this issue is in the right place.
>I have a SQL query that is transferring data from a SQL table to an Excel
>spreadsheet on the same machine. I keep getting the following error unless
I
>unprotect the worksheet. I don't want to expose the unprotected worksheet t
o
>users.
>Server: Msg 7344, Level 16, State 1, Line 342
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' could not INSERT INTO table
>'[Microsoft.Jet.OLEDB.4.0]' because of column 'MyColumnA'. The user did not
>have permission to write to the column.
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=MyColumnA STATUS=DBSTATUS_S_OK],
>[COLUMN_NAME=MyColumnB STATUS=DBSTATUS_S_OK], [COLUMN_NAME=MyColumnC
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=MyColumnD STATUS=DBSTATUS_S_OK],
>[COLUMN_NAME=MyColumnE STATUS=DBSTATUS_S_OK], ...
>
>The SQL statement I am running under the sa login is as follows. The
>spreadsheet has a column header row on row 3 and a couple rows of sample da
ta
>that I wish to lock and protect. If I unprotect the sheet, the query runs
>without error and inserts the data correctly into the spreadsheet. Any help
>will be appreciated.
>INSERT INTO
>OPENROWSET('Microsoft.Jet.OLEDB.4.0'
>,'Excel 8.0;Database=C:\myfile.xls;'
>,'select * from [Import Template$A3:S7]'
> )
> (
> [MyColumnA],
> [MyColumnB],
> [MyColumnC],
> [MyColumnD],
> [MyColumnE],
> [MyColumnF],
> [MyColumnG]
> )
>SELECT
> #MyTempTable.MyColumnA,
> #MyTempTable.MyColumnB,
> #MyTempTable.MyColumnC,
> #MyTempTable.MyColumnD,
> #MyTempTable.MyColumnE,
> #MyTempTable.MyColumnF,
> #MyTempTable.MyColumnG
>FROM
> #MyTempTable|||Mary,
Thanks for your response. I am executing the query from Query Analyzer. The
spreadsheet can be filled in manually OR automatically via the export query.
The data is not critical. I just have the header, some hidden rows and a
couple sample data rows that I want to protect. The protection is mainly for
when the users fill the spreadsheet manually. Would Windows ACL actually let
me protect several rows at the top of a spreadsheet and let the user insert,
edit and delete rows under them?
Sincerely,
Gerald
"Mary Chipman [MSFT]" wrote:

> Are you executing the query from Excel or from SQL Server? If not, you
> might want to try that. A better solution than Excel protection would
> be to limit access to the share on the local machine using Windows
> ACLs. This way access is restricted via the file system if users can
> connect to that machine through the network. Grant the account that
> you are using for SQL Server permissions on that share. The security
> offered by all Office applications, such as Access and Excel, is very
> weak and has been bypassed long ago by attackers, so it's pretty much
> a waste of time if the data truly needs to be secured.
> --Mary
> On Wed, 2 Nov 2005 13:17:18 -0800, "Gerald Hopkins"
> <GeraldHopkins@.discussions.microsoft.com> wrote:
>
>|||No, it's file-based, not application based. I'd tackle the problem
from the Excel side, not QA.
--Mary
On Sat, 5 Nov 2005 10:52:01 -0800, "Gerald Hopkins"
<GeraldHopkins@.discussions.microsoft.com> wrote:

>Mary,
>Thanks for your response. I am executing the query from Query Analyzer. The
>spreadsheet can be filled in manually OR automatically via the export query
.
>The data is not critical. I just have the header, some hidden rows and a
>couple sample data rows that I want to protect. The protection is mainly fo
r
>when the users fill the spreadsheet manually. Would Windows ACL actually le
t
>me protect several rows at the top of a spreadsheet and let the user insert
,
>edit and delete rows under them?|||OK. Thanks for the advice.
--
Sincerely,
Gerald
"Mary Chipman [MSFT]" wrote:

> No, it's file-based, not application based. I'd tackle the problem
> from the Excel side, not QA.
> --Mary
> On Sat, 5 Nov 2005 10:52:01 -0800, "Gerald Hopkins"
> <GeraldHopkins@.discussions.microsoft.com> wrote:
>
>