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

persistent SQLBrowser error eventID 3

I have what looks like a completed install of SQL Server Express Edition, accomplished via unattended setup as part of a custom application (on a machine hereafter dubbed Machine "A").

So far, though, I can't access this instance from a remote SQL Server Management Studio because SSMSE can't see the installed database engine.

I've verified that:

The SQL Server Express installation on Machine A has remote connections enabled.

The SQLBrowser on Machine A is enabled and running.

Machine A's Windows Firewall is disabled.

Using a test tool, I can see that UDP messages sent to SQLBrowser (on port 1434), arrive (i.e. it complains about their format in the System Log).

It seems that Machine A's SQLBrowser isn't responding to requests, but there's no obvious reason why.

There's one clue in Machine A's event log:

Whenever SQLBrowser is started or restarted, there's a warning in the App Log:

"The configuration of the AdminConnection\TCP protocol in the SQL instance SQLEXPRESS is not valid." (eventid 3).

Any help will be much appreciated.

Thanks in advance
Josh

Some more diverse testing reveals that the problem seems to occur only when SSMSEE is running on Windows 2000.

The source of the problem appears to be that W2K is not picking up the correct instance name (in other words, it puts MYSERVER in the list of engines, but not MYSERVER\SQLEXPRESS). If I key in MYSERVER\SQLEXPRESS by hand, things work as expected.

Incidentally, I noticed that when running under XP, the Network Server list shows which version of the SQL Engine is running on each server, but under Windows 2000, it does this only for very few of the servers.

Is this a shade of a known problem?

Josh

Permissions within groups

Running SQL Server 2000 under Windows 2003 server. All
updates and service packs applied. Using Windows
Authentication via group membership. No individual users
are defined in SQL Server. Some users can update tables,
while other users in the same group cannot (permission
denied).
I'm baffled...These people are probably members of another group that has been denied
those rights. Remember that permissions are cumulative except that deny
trumps...
"Jay Varner" <dimsjay@.dims-vote.com> wrote in message
news:e55901c3f0e8$da00f2c0$a301280a@.phx.gbl...
> Running SQL Server 2000 under Windows 2003 server. All
> updates and service packs applied. Using Windows
> Authentication via group membership. No individual users
> are defined in SQL Server. Some users can update tables,
> while other users in the same group cannot (permission
> denied).
> I'm baffled...|||Try comparing users that can update vs those that can't using gpresult.
321709 HOW TO: Use the Group Policy Results Tool in Windows 2000
http://support.microsoft.com/?id=321709
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I appreciate your quick reply, Don, but am not sure I expressed the
problem sufficiently.
Using Active Directory in W2K3 server, we defined specific user groups
for each of three domains that need to access the database. Each user
appears in only one group. All of these groups have been added to SQL
Server, and all have the same rights to the database(s). The problem
seems to occur for several people in each group, and does not seem to be
related to any permissions they are allowed on the domain. For
instance, in one of the groups, one user is a standard DOMAIN USER in
Windows, and he is able to perform updates to any of the tables in the
database; another user is a DOMAIN ADMIN who belongs to the same group,
but he is denied access to perform updates.
If we assign the group SYSTEM ADMINISTRATOR priveleges on the database,
it seems to resolve the problem, but thats not an acceptable resolution
for this large office.
If we assign each individual user (rather than the groups) to SQL, the
problem goes away. Again, this is a large office, and they would like
to avoid the additional overhead of having to add each new user to both
Windows and SQL Server.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Permissions with Windows groups and view to other databases

I am having a problem with permissions using Windows groups. I have a database (database1) that has permissions granted via Windows groups. Two groups (group1 and group2) are members of the db_datareader role in database1, and this work fine. Do to the number of tables that get created during our work, using db_datareader is the easiest way to keep up with permissions without creating a maintenance problem. Now I have a table that I want to add to this database, but I only want group2 to have select permission on this one table which is a problem because group1 has the db_datareader role. So I thought I could create a view in this database to the restricted table that I put in database2. Then in database2 I only added group2 as a user with the permission to select from this table. Unfortunately the group membership does not seem to get interpretted correctly in database2 and no one can successfult select from the view in database1.

In other words, user1 who belongs to group1 connects to database1 and cannot select from the restricted view -- this is what I would expect. However, when user2 who belongs to group2 connects to database1 they also cannot select from the restricted view -- not the behvior I would expect. Now, if I make user2 a user in database2 with select on the restricted table then user2 can connect to database1 and successfuly get data from the restricted view. So it looks like the fact that user2 belongs to group2 is never passed to database2 via the select from the view on database1. Is this indeed the way that Windows group security is working or is meant to work in SQL Server?

I realize I could solve this simplified version of the problem by creating my own role in database1 for group1 etc., but I am trying to solve a bigger problem in our environment that has hundreds of databases across numerous servers.

Thanks

Rob

Why not simply deny SELECT permission to group1 for that particular table? The rule that you need to remember is that a deny will trump a grant, so the deny will take precedence over the db_datareader membership.

Thanks

Laurentiu

|||Well, that won't quite work. The two Windows groups we are talking about have some overlapping members, but one group is not a complete subset of the other. So if I deny SELECT to group1 then some people that I want to access the data (group2) because they are in both groups and as you point out deny has a higher priority. Is there any reason group permissions are not valid in database2 when selecting from the view in database1?|||

This is the expected behavior; but it sounds like you may be trying to attempt cross-database ownership chaining (also known as CDOC, look for “Using ownership chains” topic in BOL). CDOC is a feature that is disabled by default and we recommend against using it because of the security risks inherent from this feature. For more information on CDOC look for “Using ownership chains” topic in BOL.

The reason why user2 is failing to access the table is that there is a separate user token for database1 and database2 (both derived from the same Windows login token). On database1 the user2 token will look similar to this:

Primary identity:

· user2, Windows user

Secondary identities:

· group2, Windows group

· db_datareader, role

When accessing the view, the permissions are checked against this token, and they will succeed, but the view is making reference to database2.<some_schema>.restricted_table, therefore it is necessary to create a token for database2.

On your first attempt (without creating a user in Database2, and granting permission to access the table) the user token creation process for database2 should have failed with a “user cannot access this database”-type of error.

Here are a few potential workarounds that may help you:

Instead of using db_datareader you can use different schemas and grant SELECT based on the schemas to differentiate groups, for example:

GRANT SELECT ON SCHEMA::[Schema_group1] TO group1

GRANT SELECT ON SCHEMA::[Schema_group2] TO group2

GRANT SELECT ON SCHEMA::[Schema_all] TO group1, group2

That way the SELECT permission would be restricted to only the schemas you defined.

Another alternative for cross-DB access could be using signatures, similar to the one I described in the following article: http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

Please, let us know if any of this alternatives worked for you or if you have any additional questions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Permissions with Windows groups and view to other databases

I am having a problem with permissions using Windows groups. I have a database (database1) that has permissions granted via Windows groups. Two groups (group1 and group2) are members of the db_datareader role in database1, and this work fine. Do to the number of tables that get created during our work, using db_datareader is the easiest way to keep up with permissions without creating a maintenance problem. Now I have a table that I want to add to this database, but I only want group2 to have select permission on this one table which is a problem because group1 has the db_datareader role. So I thought I could create a view in this database to the restricted table that I put in database2. Then in database2 I only added group2 as a user with the permission to select from this table. Unfortunately the group membership does not seem to get interpretted correctly in database2 and no one can successfult select from the view in database1.

In other words, user1 who belongs to group1 connects to database1 and cannot select from the restricted view -- this is what I would expect. However, when user2 who belongs to group2 connects to database1 they also cannot select from the restricted view -- not the behvior I would expect. Now, if I make user2 a user in database2 with select on the restricted table then user2 can connect to database1 and successfuly get data from the restricted view. So it looks like the fact that user2 belongs to group2 is never passed to database2 via the select from the view on database1. Is this indeed the way that Windows group security is working or is meant to work in SQL Server?

I realize I could solve this simplified version of the problem by creating my own role in database1 for group1 etc., but I am trying to solve a bigger problem in our environment that has hundreds of databases across numerous servers.

Thanks

Rob

Why not simply deny SELECT permission to group1 for that particular table? The rule that you need to remember is that a deny will trump a grant, so the deny will take precedence over the db_datareader membership.

Thanks

Laurentiu

|||Well, that won't quite work. The two Windows groups we are talking about have some overlapping members, but one group is not a complete subset of the other. So if I deny SELECT to group1 then some people that I want to access the data (group2) because they are in both groups and as you point out deny has a higher priority. Is there any reason group permissions are not valid in database2 when selecting from the view in database1?|||

This is the expected behavior; but it sounds like you may be trying to attempt cross-database ownership chaining (also known as CDOC, look for “Using ownership chains” topic in BOL). CDOC is a feature that is disabled by default and we recommend against using it because of the security risks inherent from this feature. For more information on CDOC look for “Using ownership chains” topic in BOL.

The reason why user2 is failing to access the table is that there is a separate user token for database1 and database2 (both derived from the same Windows login token). On database1 the user2 token will look similar to this:

Primary identity:

· user2, Windows user

Secondary identities:

· group2, Windows group

· db_datareader, role

When accessing the view, the permissions are checked against this token, and they will succeed, but the view is making reference to database2.<some_schema>.restricted_table, therefore it is necessary to create a token for database2.

On your first attempt (without creating a user in Database2, and granting permission to access the table) the user token creation process for database2 should have failed with a “user cannot access this database”-type of error.

Here are a few potential workarounds that may help you:

Instead of using db_datareader you can use different schemas and grant SELECT based on the schemas to differentiate groups, for example:

GRANT SELECT ON SCHEMA::[Schema_group1] TO group1

GRANT SELECT ON SCHEMA::[Schema_group2] TO group2

GRANT SELECT ON SCHEMA::[Schema_all] TO group1, group2

That way the SELECT permission would be restricted to only the schemas you defined.

Another alternative for cross-DB access could be using signatures, similar to the one I described in the following article: http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

Please, let us know if any of this alternatives worked for you or if you have any additional questions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

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 via ASP.NET web site

I have a website with membership system. I've been through a lot implementing the site and I think I'm on the verge of full implementation. I can't get my registration page or my admin page to add, edit or delete users. I'm assuming that asp does not have proper permissions on SQL to do such.

I'm running the site on a provider that has a SQL 2000 server. I access the database through VS2005 and SQL Server Management Studio Express.

Is there a tutorial out there that could help a newbie to SQL understand how to check and correct my permissions issue (or find out that I'm haveing a different issue entirely.

-Bill

SQL Server 2000 require you to go in and manually create the account before you can run it I have answered this question in the thread below go through it and apply the instructions in the last link and post again if you still need help. Hope this helps.

http://forums.asp.net/thread/1534299.aspx

|||

Thank you for your prompt reply.

Further research indicates that my roles are conflicted also. My registrations are being created, but I can't see the role that's being created for them. Once I figure that out, I'll be able to add that role to the proper table and be able to see all of the users I create. Editing and deleting users is still not possible given the instructions you posted. I still need to change the permissions from my admin page. The create user is working.

Thank you

|||

You are not supposed to edit and delete users so delete the database you have created and start new this time do everything in SQL Server 2000 because Express and 2000 are not the same. Hope this helps.

http://blog.vanslaars.com/archive/2006/05/17/ASP.NET-2.0-Membership-with-SQL-2000.aspx

sql

Permissions problem accessing external DLL through reports

Hi,
I'm having this wee problem when trying to view my report via the report
manager screen - I get the following error "Request for the permission of
type System.Security.Permissions.SecurityPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed"...This seems to be due to me trying to access a seperate DLL which in
turn accesses the database that I created for the reports for translation
purposes (I added the DLL to the References collection for each of the
reports). Any suggestions would be greatly appreciated.I am also tring to use an assembly to translate y i got the same message.
I already try to configure my rssvpolicy.config like:
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="XMLFile_MultiLang_Permission"
Name="MultiLangCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\MultLang.dll"/>
</CodeGroup>
but i habe the same error.
Have you found out how to solve it ?
"Rowan Massey" wrote:
> Hi,
> I'm having this wee problem when trying to view my report via the report
> manager screen - I get the following error "Request for the permission of
> type System.Security.Permissions.SecurityPermission, mscorlib,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
> failed"...This seems to be due to me trying to access a seperate DLL which in
> turn accesses the database that I created for the reports for translation
> purposes (I added the DLL to the References collection for each of the
> reports). Any suggestions would be greatly appreciated.sql

Permission to run master..xp_cmdshell

Hi!
I have a user (SQL login), who has access to master database and the
right to run xp_cmdshell via database role membership. When running
it:
SETUSER 'Smith'
go
exec master..xp_cmdshell 'dir c:'
go
SETUSER
=============================== We get:
Msg 50001, Level 1, State 50001
xpsql.c: Error 997 from GetPassword on line 465
When I give the user the sa server role, it works fine. I am not very
happy to give so many users the sa role. Please help me to figure out,
how to allow a user to run xp_cmdshell.
/* I suspect that this is the consequences? of a recent security patch
installation (SQL2000-KB815495-8.00.0818-ENU.exe), since there were no
complains about it before */
Thanks.I assume you are running SQL 7. This error may indicate a problem with
the proxy and/or service accounts.
To ensure the proxy account is configured properly, use Enterprise
Manager to navigate to Management --> SQL Server Agent --> Job System
and ensure 'only allow users with Sysadmin privileges ...' is unchecked
and click the Reset Proxy Account button.
If the problem persists, ensure the SQL Server service account has the
needed permissions. The easiest way to do this is to re-specify the
service account using Enterprise Manager (server properties -->
Security). Assuming you are using a domain account, change it to the
System account and then back to the domain account. Enterprise Manager
will assign the domain account the necessary rights during the change.
You'll need to restart SQL Server after the change. Sometimes a reboot
is required as well.
Separately, you might consider leveraging cross database ownership
chaining so that you don't need to grant direct execute permissions to
users on xp_cmdshell. If your proc is owned by dbo, all you need to do
is change ownership of your user database to the 'sa' login. Due to the
unbroken ownership chain, users can then execute xp_cmdshell only via
your user proc and cannot execute it directly. However, you should
employ this technique only if you fully trust users that have
permissions to create dbo-owned objects in your user database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0308192241.7a70bf5f@.posting.google.com...
> Hi!
> I have a user (SQL login), who has access to master database and the
> right to run xp_cmdshell via database role membership. When running
> it:
> SETUSER 'Smith'
> go
> exec master..xp_cmdshell 'dir c:'
> go
> SETUSER
> ===============================> We get:
> Msg 50001, Level 1, State 50001
> xpsql.c: Error 997 from GetPassword on line 465
> When I give the user the sa server role, it works fine. I am not very
> happy to give so many users the sa role. Please help me to figure out,
> how to allow a user to run xp_cmdshell.
> /* I suspect that this is the consequences? of a recent security patch
> installation (SQL2000-KB815495-8.00.0818-ENU.exe), since there were no
> complains about it before */
> Thanks.

Permission to access dB problem in SQL Server 2005 Express from inside VS2005

I am trying to program a database from inside C++.NET via Visual
Studio 2005 using the ADO.NET set of classes, but this I believe is a
SQL Server 2005 Express permissions question under Windows XP
Professional OS.
In another thread, http://tinyurl.com/373lzx, I was able to open a
database successfully using VS2005 (thanks to a suggestion by Erland
Sommarskog). But subsequent to this I played around with the local
policy permissons while logged in as Administrator and may have
blocked access to this database. How to undo this? (I forgot what I
changed, but at the time it was pretty innocuous, and none of my other
programs have been affected).
Two problems really:
0/ though I can create and access a new database as a Power User, I
cannot access the old database anymore. Literally I have a red 'x'
next to the icon for the old database, and I get the error, under the
Server Explorer of Visual Studio 2005, when I right click "refresh" of
"Exception of the type 'System.Exception' was thrown".
1/ Switching to Administrator is no help (see below for the error),
but that also raises another question (see Q2 below)
2/ I played around with SQL Server 2005 Express Manager,and tried
adding various groups, but to no avail.
Question #1: how to undo this
Question #2: how to add "Admin" as an "object" or whatever you call
it under the SQL Server 2005 Express manager so I avoid the below
error (i.e., so Admin can access this dB).
Thank you.
RL
-
No connection the following error occurred: Unable to open the
physical file "C
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
\NEWPC_USERDCV_DB.mdf". Operating system error 32: "32(The process
cannot access the file because it is being
used by another process.)".
Unable to open the physical file "C:\Program Files\Microsoft SQL Server
\MSSQL.1
MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF". Operating system error 32:
"32(The process canot access the file because it is being used by
another process.)".
Cannot open database "NEWPC_USERDCV_DB" requested by the login. The
login failed.
Login failed for user 'PC_NAME-PC_USER\Administrator'.
File activation failure. The physical file name "C:\Program Files
\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF " may be incorrect.
Press any key to continue . . .
This article may help.
http://support.microsoft.com/kb/313222/en-us
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"raylopez99" wrote:
>I am trying to program a database from inside C++.NET via Visual
> Studio 2005 using the ADO.NET set of classes, but this I believe is a
> SQL Server 2005 Express permissions question under Windows XP
> Professional OS.
> In another thread, http://tinyurl.com/373lzx, I was able to open a
> database successfully using VS2005 (thanks to a suggestion by Erland
> Sommarskog). But subsequent to this I played around with the local
> policy permissons while logged in as Administrator and may have
> blocked access to this database. How to undo this? (I forgot what I
> changed, but at the time it was pretty innocuous, and none of my other
> programs have been affected).
> Two problems really:
> 0/ though I can create and access a new database as a Power User, I
> cannot access the old database anymore. Literally I have a red 'x'
> next to the icon for the old database, and I get the error, under the
> Server Explorer of Visual Studio 2005, when I right click "refresh" of
> "Exception of the type 'System.Exception' was thrown".
> 1/ Switching to Administrator is no help (see below for the error),
> but that also raises another question (see Q2 below)
> 2/ I played around with SQL Server 2005 Express Manager,and tried
> adding various groups, but to no avail.
> Question #1: how to undo this
> Question #2: how to add "Admin" as an "object" or whatever you call
> it under the SQL Server 2005 Express manager so I avoid the below
> error (i.e., so Admin can access this dB).
> Thank you.
> RL
> -
> No connection the following error occurred: Unable to open the
> physical file "C
> \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
> \NEWPC_USERDCV_DB.mdf". Operating system error 32: "32(The process
> cannot access the file because it is being
> used by another process.)".
> Unable to open the physical file "C:\Program Files\Microsoft SQL Server
> \MSSQL.1
> MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF". Operating system error 32:
> "32(The process canot access the file because it is being used by
> another process.)".
> Cannot open database "NEWPC_USERDCV_DB" requested by the login. The
> login failed.
> Login failed for user 'PC_NAME-PC_USER\Administrator'.
> File activation failure. The physical file name "C:\Program Files
> \Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF " may be incorrect.
> Press any key to continue . . .
>
|||On Apr 8, 11:04 am, "Dave Patrick" <DSPatr...@.nospam.gmail.com> wrote:
> This article may help.
> http://support.microsoft.com/kb/313222/en-us
>
Thanks but I was thinking of a less extreme fix.
I'm thinking there must be some way of 'unlocking' the old database.
The reason is, I figured out a workaround, in that I constructed the
same database from scratch, from inside of VS 2005 (i.e. mechanically
reconstructing the entire database), and the new, identical database
can now be accessed as before, from inside VS 2005, and the connection
works (refresh works, 'test connection' from Modify also works, and
the program accessing the new database). But the old database still
has a red "X" next to it, and refreshing does not work.
Just a weird aspect.
I've ordered some books on dB programming and maybe they'll have some
insight, but in general I'd like to know how to reset permissions and/
or fix such problems in the future (short of reconstructing the
database 'from scratch' as I did here)
RL

Permission to access dB problem in SQL Server 2005 Express from inside VS2005

I am trying to program a database from inside C++.NET via Visual
Studio 2005 using the ADO.NET set of classes, but this I believe is a
SQL Server 2005 Express permissions question under Windows XP
Professional OS.
In another thread, http://tinyurl.com/373lzx, I was able to open a
database successfully using VS2005 (thanks to a suggestion by Erland
Sommarskog). But subsequent to this I played around with the local
policy permissons while logged in as Administrator and may have
blocked access to this database. How to undo this? (I forgot what I
changed, but at the time it was pretty innocuous, and none of my other
programs have been affected).
Two problems really:
0/ though I can create and access a new database as a Power User, I
cannot access the old database anymore. Literally I have a red 'x'
next to the icon for the old database, and I get the error, under the
Server Explorer of Visual Studio 2005, when I right click "refresh" of
"Exception of the type 'System.Exception' was thrown".
1/ Switching to Administrator is no help (see below for the error),
but that also raises another question (see Q2 below)
2/ I played around with SQL Server 2005 Express Manager,and tried
adding various groups, but to no avail.
Question #1: how to undo this
Question #2: how to add "Admin" as an "object" or whatever you call
it under the SQL Server 2005 Express manager so I avoid the below
error (i.e., so Admin can access this dB).
Thank you.
RL
-
No connection the following error occurred: Unable to open the
physical file "C
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
\NEWPC_USERDCV_DB.mdf". Operating system error 32: "32(The process
cannot access the file because it is being
used by another process.)".
Unable to open the physical file "C:\Program Files\Microsoft SQL Server
\MSSQL.1
MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF". Operating system error 32:
"32(The process canot access the file because it is being used by
another process.)".
Cannot open database "NEWPC_USERDCV_DB" requested by the login. The
login failed.
Login failed for user 'PC_NAME-PC_USER\Administrator'.
File activation failure. The physical file name "C:\Program Files
\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF" may be incorrect.
Press any key to continue . . .This article may help.
http://support.microsoft.com/kb/313222/en-us
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"raylopez99" wrote:
>I am trying to program a database from inside C++.NET via Visual
> Studio 2005 using the ADO.NET set of classes, but this I believe is a
> SQL Server 2005 Express permissions question under Windows XP
> Professional OS.
> In another thread, http://tinyurl.com/373lzx, I was able to open a
> database successfully using VS2005 (thanks to a suggestion by Erland
> Sommarskog). But subsequent to this I played around with the local
> policy permissons while logged in as Administrator and may have
> blocked access to this database. How to undo this? (I forgot what I
> changed, but at the time it was pretty innocuous, and none of my other
> programs have been affected).
> Two problems really:
> 0/ though I can create and access a new database as a Power User, I
> cannot access the old database anymore. Literally I have a red 'x'
> next to the icon for the old database, and I get the error, under the
> Server Explorer of Visual Studio 2005, when I right click "refresh" of
> "Exception of the type 'System.Exception' was thrown".
> 1/ Switching to Administrator is no help (see below for the error),
> but that also raises another question (see Q2 below)
> 2/ I played around with SQL Server 2005 Express Manager,and tried
> adding various groups, but to no avail.
> Question #1: how to undo this
> Question #2: how to add "Admin" as an "object" or whatever you call
> it under the SQL Server 2005 Express manager so I avoid the below
> error (i.e., so Admin can access this dB).
> Thank you.
> RL
> -
> No connection the following error occurred: Unable to open the
> physical file "C
> \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
> \NEWPC_USERDCV_DB.mdf". Operating system error 32: "32(The process
> cannot access the file because it is being
> used by another process.)".
> Unable to open the physical file "C:\Program Files\Microsoft SQL Server
> \MSSQL.1
> MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF". Operating system error 32:
> "32(The process canot access the file because it is being used by
> another process.)".
> Cannot open database "NEWPC_USERDCV_DB" requested by the login. The
> login failed.
> Login failed for user 'PC_NAME-PC_USER\Administrator'.
> File activation failure. The physical file name "C:\Program Files
> \Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF" may be incorrect.
> Press any key to continue . . .
>|||On Apr 8, 11:04 am, "Dave Patrick" <DSPatr...@.nospam.gmail.com> wrote:
> This article may help.
> http://support.microsoft.com/kb/313222/en-us
>
Thanks but I was thinking of a less extreme fix.
I'm thinking there must be some way of 'unlocking' the old database.
The reason is, I figured out a workaround, in that I constructed the
same database from scratch, from inside of VS 2005 (i.e. mechanically
reconstructing the entire database), and the new, identical database
can now be accessed as before, from inside VS 2005, and the connection
works (refresh works, 'test connection' from Modify also works, and
the program accessing the new database). But the old database still
has a red "X" next to it, and refreshing does not work.
Just a weird aspect.
I've ordered some books on dB programming and maybe they'll have some
insight, but in general I'd like to know how to reset permissions and/
or fix such problems in the future (short of reconstructing the
database 'from scratch' as I did here)
RL

Permission to access dB problem in SQL Server 2005 Express from inside VS2005

I am trying to program a database from inside C++.NET via Visual
Studio 2005 using the ADO.NET set of classes, but this I believe is a
SQL Server 2005 Express permissions question under Windows XP
Professional OS.
In another thread, http://tinyurl.com/373lzx, I was able to open a
database successfully using VS2005 (thanks to a suggestion by Erland
Sommarskog). But subsequent to this I played around with the local
policy permissons while logged in as Administrator and may have
blocked access to this database. How to undo this? (I forgot what I
changed, but at the time it was pretty innocuous, and none of my other
programs have been affected).
Two problems really:
0/ though I can create and access a new database as a Power User, I
cannot access the old database anymore. Literally I have a red 'x'
next to the icon for the old database, and I get the error, under the
Server Explorer of Visual Studio 2005, when I right click "refresh" of
"Exception of the type 'System.Exception' was thrown".
1/ Switching to Administrator is no help (see below for the error),
but that also raises another question (see Q2 below)
2/ I played around with SQL Server 2005 Express Manager,and tried
adding various groups, but to no avail.
Question #1: how to undo this
Question #2: how to add "Admin" as an "object" or whatever you call
it under the SQL Server 2005 Express manager so I avoid the below
error (i.e., so Admin can access this dB).
Thank you.
RL
-
No connection the following error occurred: Unable to open the
physical file "C
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
\NEWPC_USERDCV_DB.mdf". Operating system error 32: "32(The process
cannot access the file because it is being
used by another process.)".
Unable to open the physical file "C:\Program Files\Microsoft SQL Server
\MSSQL.1
MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF". Operating system error 32:
"32(The process canot access the file because it is being used by
another process.)".
Cannot open database "NEWPC_USERDCV_DB" requested by the login. The
login failed.
Login failed for user 'PC_NAME-PC_USER\Administrator'.
File activation failure. The physical file name "C:\Program Files
\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF" may be incorrect.
Press any key to continue . . .This article may help.
http://support.microsoft.com/kb/313222/en-us
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"raylopez99" wrote:
>I am trying to program a database from inside C++.NET via Visual
> Studio 2005 using the ADO.NET set of classes, but this I believe is a
> SQL Server 2005 Express permissions question under Windows XP
> Professional OS.
> In another thread, http://tinyurl.com/373lzx, I was able to open a
> database successfully using VS2005 (thanks to a suggestion by Erland
> Sommarskog). But subsequent to this I played around with the local
> policy permissons while logged in as Administrator and may have
> blocked access to this database. How to undo this? (I forgot what I
> changed, but at the time it was pretty innocuous, and none of my other
> programs have been affected).
> Two problems really:
> 0/ though I can create and access a new database as a Power User, I
> cannot access the old database anymore. Literally I have a red 'x'
> next to the icon for the old database, and I get the error, under the
> Server Explorer of Visual Studio 2005, when I right click "refresh" of
> "Exception of the type 'System.Exception' was thrown".
> 1/ Switching to Administrator is no help (see below for the error),
> but that also raises another question (see Q2 below)
> 2/ I played around with SQL Server 2005 Express Manager,and tried
> adding various groups, but to no avail.
> Question #1: how to undo this
> Question #2: how to add "Admin" as an "object" or whatever you call
> it under the SQL Server 2005 Express manager so I avoid the below
> error (i.e., so Admin can access this dB).
> Thank you.
> RL
> -
> No connection the following error occurred: Unable to open the
> physical file "C
> \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
> \NEWPC_USERDCV_DB.mdf". Operating system error 32: "32(The process
> cannot access the file because it is being
> used by another process.)".
> Unable to open the physical file "C:\Program Files\Microsoft SQL Server
> \MSSQL.1
> MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF". Operating system error 32:
> "32(The process canot access the file because it is being used by
> another process.)".
> Cannot open database "NEWPC_USERDCV_DB" requested by the login. The
> login failed.
> Login failed for user 'PC_NAME-PC_USER\Administrator'.
> File activation failure. The physical file name "C:\Program Files
> \Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\NEWPC_USERDCV_DB_log.LDF" may be incorrect.
> Press any key to continue . . .
>|||On Apr 8, 11:04 am, "Dave Patrick" <DSPatr...@.nospam.gmail.com> wrote:
> This article may help.
> http://support.microsoft.com/kb/313222/en-us
>
Thanks but I was thinking of a less extreme fix.
I'm thinking there must be some way of 'unlocking' the old database.
The reason is, I figured out a workaround, in that I constructed the
same database from scratch, from inside of VS 2005 (i.e. mechanically
reconstructing the entire database), and the new, identical database
can now be accessed as before, from inside VS 2005, and the connection
works (refresh works, 'test connection' from Modify also works, and
the program accessing the new database). But the old database still
has a red "X" next to it, and refreshing does not work.
Just a weird aspect.
I've ordered some books on dB programming and maybe they'll have some
insight, but in general I'd like to know how to reset permissions and/
or fix such problems in the future (short of reconstructing the
database 'from scratch' as I did here)
RL

Permission Issues running DTS from Agent

We have a DTS which loads files using FTP to another
server. This will be used by app groups and will be run
via click of button using sp_start_job,

Somehow the userid running the job gets "not sysadmin to
run cmdshell.." Top fix that, we granted it to execute the
xp_cmdshell and also created a proxy account for SQL Agent
which is an admin on server and sql. However, still we get
permission errors like "...The needed permission is
missing to run command shell.." Please help . The DTS is
being called from SQl Agent job and we don't want an admin
ID to be used .Do you have 'exec master.dbo.' in from of xp_command... :confused:

Permission editor?

Hi,
I wonder is there a tool for easy setting of custom permissions for user on
database. When i try to do this via the Enterprise Manager it shows all the
objects in the DB without any grouping so it's difficult set permissions.
For example if i want to give SELECT permissions on all tables but not to
give SELECT on the VIEWS and i want to give EXECUTE permission on all
functions i must go around the list and put check-s on the boxes manually.
Thanks in advance
Georgi Peshterski
I suggest you script your tasks instead of performing them from Enterprise
Manager. Take a look at
http://vyaskn.tripod.com/generate_sc..._sql_tasks.htm to find
some useful examples.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Georgi Peshterski" <unseen@.mail.bg> wrote in message
news:%233VrKC2aFHA.3040@.TK2MSFTNGP14.phx.gbl...
Hi,
I wonder is there a tool for easy setting of custom permissions for user on
database. When i try to do this via the Enterprise Manager it shows all the
objects in the DB without any grouping so it's difficult set permissions.
For example if i want to give SELECT permissions on all tables but not to
give SELECT on the VIEWS and i want to give EXECUTE permission on all
functions i must go around the list and put check-s on the boxes manually.
Thanks in advance
Georgi Peshterski