PF Usage

I have a SQL server 2005 box with 8 GB physical memory. The windows task
manager reports 7.4G PF Usage. Is this accurate?Hello,
It should be correct. But use the performance monitor to measure the correct
usage. Take a look int the below URL:-
http://msdn2.microsoft.com/en-us/library/ms176018.aspx
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:55174532-F7F0-4EEC-8427-81C271DFF935@.microsoft.com...
>I have a SQL server 2005 box with 8 GB physical memory. The windows task
> manaer reports 7.4G PF Usage. Is this accurate?

PF numbers

Hello!
I am puzzled by PF Usage numbers reported on our SQL server. It is SQL
Server 2000 (SP3a) running on Windows 2003 Enterprise Server with 12 GB of
RAM. SQL Server is restricted to 10GB. There is only one page file 2046MB.
When I open Task Manager PF Usage numbers are 10.3 GB. I am not sure how
this number is calculated. According to help : It is 'The amount of paging
file being used by the system.' Considering I have paging file 2GB big how
come Task Manager shows 10.3 GB?
Thanks you in advance,
Igor
Hi
Have you checked that the Page File is not also on other discs?
What does performance monitor show for this?
John
"imarchenko" wrote:

> Hello!
> I am puzzled by PF Usage numbers reported on our SQL server. It is SQL
> Server 2000 (SP3a) running on Windows 2003 Enterprise Server with 12 GB of
> RAM. SQL Server is restricted to 10GB. There is only one page file 2046MB.
> When I open Task Manager PF Usage numbers are 10.3 GB. I am not sure how
> this number is calculated. According to help : It is 'The amount of paging
> file being used by the system.' Considering I have paging file 2GB big how
> come Task Manager shows 10.3 GB?
>
> Thanks you in advance,
> Igor
>
>
|||John,
There is only one Page File located on drive C.
Igor
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ABD53369-1AEC-4893-A353-58DE3D716359@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you checked that the Page File is not also on other discs?
> What does performance monitor show for this?
> John
> "imarchenko" wrote:

PF numbers

Hello!
I am puzzled by PF Usage numbers reported on our SQL server. It is SQL
Server 2000 (SP3a) running on Windows 2003 Enterprise Server with 12 GB of
RAM. SQL Server is restricted to 10GB. There is only one page file 2046MB.
When I open Task Manager PF Usage numbers are 10.3 GB. I am not sure how
this number is calculated. According to help : It is 'The amount of paging
file being used by the system.' Considering I have paging file 2GB big how
come Task Manager shows 10.3 GB?
Thanks you in advance,
IgorHi
Have you checked that the Page File is not also on other discs?
What does performance monitor show for this?
John
"imarchenko" wrote:
> Hello!
> I am puzzled by PF Usage numbers reported on our SQL server. It is SQL
> Server 2000 (SP3a) running on Windows 2003 Enterprise Server with 12 GB of
> RAM. SQL Server is restricted to 10GB. There is only one page file 2046MB.
> When I open Task Manager PF Usage numbers are 10.3 GB. I am not sure how
> this number is calculated. According to help : It is 'The amount of paging
> file being used by the system.' Considering I have paging file 2GB big how
> come Task Manager shows 10.3 GB?
>
> Thanks you in advance,
> Igor
>
>|||John,
There is only one Page File located on drive C.
Igor
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ABD53369-1AEC-4893-A353-58DE3D716359@.microsoft.com...
> Hi
> Have you checked that the Page File is not also on other discs?
> What does performance monitor show for this?
> John
> "imarchenko" wrote:
>> Hello!
>> I am puzzled by PF Usage numbers reported on our SQL server. It is SQL
>> Server 2000 (SP3a) running on Windows 2003 Enterprise Server with 12 GB
>> of
>> RAM. SQL Server is restricted to 10GB. There is only one page file
>> 2046MB.
>> When I open Task Manager PF Usage numbers are 10.3 GB. I am not sure how
>> this number is calculated. According to help : It is 'The amount of
>> paging
>> file being used by the system.' Considering I have paging file 2GB big
>> how
>> come Task Manager shows 10.3 GB?
>>
>> Thanks you in advance,
>> Igor
>>
>>

PF numbers

Hello!
I am puzzled by PF Usage numbers reported on our SQL server. It is SQL
Server 2000 (SP3a) running on Windows 2003 Enterprise Server with 12 GB of
RAM. SQL Server is restricted to 10GB. There is only one page file 2046MB.
When I open Task Manager PF Usage numbers are 10.3 GB. I am not sure how
this number is calculated. According to help : It is 'The amount of paging
file being used by the system.' Considering I have paging file 2GB big how
come Task Manager shows 10.3 GB?
Thanks you in advance,
IgorHi
Have you checked that the Page File is not also on other discs?
What does performance monitor show for this?
John
"imarchenko" wrote:

> Hello!
> I am puzzled by PF Usage numbers reported on our SQL server. It is SQL
> Server 2000 (SP3a) running on Windows 2003 Enterprise Server with 12 GB of
> RAM. SQL Server is restricted to 10GB. There is only one page file 2046MB.
> When I open Task Manager PF Usage numbers are 10.3 GB. I am not sure how
> this number is calculated. According to help : It is 'The amount of paging
> file being used by the system.' Considering I have paging file 2GB big how
> come Task Manager shows 10.3 GB?
>
> Thanks you in advance,
> Igor
>
>|||John,
There is only one Page File located on drive C.
Igor
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ABD53369-1AEC-4893-A353-58DE3D716359@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you checked that the Page File is not also on other discs?
> What does performance monitor show for this?
> John
> "imarchenko" wrote:
>

Pervassive Client Install

Would anyone install a Pervassive client application into the windows & sql
2005 cluster environment?
I don't see why not, although I'd prefer not to install Pervasive in any
environment.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"John Kamensky" <jkamensky@.whitfordww.com> wrote in message
news:%23fTtgDCVGHA.5332@.TK2MSFTNGP10.phx.gbl...
> Would anyone install a Pervassive client application into the windows &
> sql 2005 cluster environment?
>
sql

Pervassive Client Install

Would anyone install a Pervassive client application into the windows & sql
2005 cluster environment?I don't see why not, although I'd prefer not to install Pervasive in any
environment.
--
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"John Kamensky" <jkamensky@.whitfordww.com> wrote in message
news:%23fTtgDCVGHA.5332@.TK2MSFTNGP10.phx.gbl...
> Would anyone install a Pervassive client application into the windows &
> sql 2005 cluster environment?
>

Pervassive Client Install

Would anyone install a Pervassive client application into the windows & sql
2005 cluster environment?I don't see why not, although I'd prefer not to install Pervasive in any
environment.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"John Kamensky" <jkamensky@.whitfordww.com> wrote in message
news:%23fTtgDCVGHA.5332@.TK2MSFTNGP10.phx.gbl...
> Would anyone install a Pervassive client application into the windows &
> sql 2005 cluster environment?
>

Personalize Report

Hi all, I was wondering if there is a way to capture the current users windows login and use that to personalize as report by displaying the users name at the top...or customizing report output by using the windows login as a query or report parameter.

This is very simple with Visual Studio 2005.

If you only have SQL 2005, on the edit expression screen use Globals -> UserID.

|||

Yes, it is easy to do... You can create a report parameter to capture the user id by using

=User!UserID

This will return you the credentials of the user who is trying to run the report which you can use anywhere in the report.

sql

Personal Edition

I am going to be purchasing SQL 2000 Standard Edition, in order to use the personal edition software, because we only have windows 98 and me. What my questions is, that we I have the personal edition running on one of my two machines. Will I be able to
connect to the database from another machine. We use a manufactoring program and it will be running a SQL database, when I connect to my program is it going to give me an error about not being able to connect to the personal edition? Thanks
Rocky,
Is this solely for development purposes? Then go for SQL2000 dev edition.It
comes cheap too!Refer the below url for pricing details:
'Developer Edition Licensing'
http://www.microsoft.com/sql/howtobuy/development.asp
[vbcol=seagreen]
Yes.Please note that personal edition has a documented limitation of
handling concurrent queries.
[vbcol=seagreen]
being able to connect to the personal edition?
Well, since I have not seen the connection code, I cant say for sure.But ,
provided the connection code is correct, there is no reason why it wont
connect to the SQLServer just because its a personal edition.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"rocky" <anonymous@.discussions.microsoft.com> wrote in message
news:B8C34350-D3A5-4BAB-A9DF-22719F142E01@.microsoft.com...
> I am going to be purchasing SQL 2000 Standard Edition, in order to use the
personal edition software, because we only have windows 98 and me. What my
questions is, that we I have the personal edition running on one of my two
machines. Will I be able to connect to the database from another machine.
We use a manufactoring program and it will be running a SQL database, when I
connect to my program is it going to give me an error about not being able
to connect to the personal edition? Thanks
|||All that I will be doing with it is installing the SQL Server on a machine, and connecting to the database through a program that we use.
-- Dinesh T.K wrote: --
Rocky,
Is this solely for development purposes? Then go for SQL2000 dev edition.It
comes cheap too!Refer the below url for pricing details:
'Developer Edition Licensing'
http://www.microsoft.com/sql/howtobuy/development.asp
[vbcol=seagreen]
Yes.Please note that personal edition has a documented limitation of
handling concurrent queries.
[vbcol=seagreen]
being able to connect to the personal edition?
Well, since I have not seen the connection code, I cant say for sure.But ,
provided the connection code is correct, there is no reason why it wont
connect to the SQLServer just because its a personal edition.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"rocky" <anonymous@.discussions.microsoft.com> wrote in message
news:B8C34350-D3A5-4BAB-A9DF-22719F142E01@.microsoft.com...
> I am going to be purchasing SQL 2000 Standard Edition, in order to use the
personal edition software, because we only have windows 98 and me. What my
questions is, that we I have the personal edition running on one of my two
machines. Will I be able to connect to the database from another machine.
We use a manufactoring program and it will be running a SQL database, when I
connect to my program is it going to give me an error about not being able
to connect to the personal edition? Thanks
|||Rocky,
So, as per your plan, your database would be running SQL2000 personal
edition and the application connects to this database.Coming back to the
concurrent query limit, does many users use this application concurrently?
Personal edition as well as MSDE can support only upto 8 concurrent queries
before things get real slow and messy.
I must admit, even at this point, Iam not sure if this application is used
for dev purposes or production.Can you please answer that?
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rocky" <anonymous@.discussions.microsoft.com> wrote in message
news:2A41E566-1039-48F4-8F2C-AF53CD8A6382@.microsoft.com...
> All that I will be doing with it is installing the SQL Server on a
machine, and connecting to the database through a program that we use.
>
> -- Dinesh T.K wrote: --
> Rocky,
> Is this solely for development purposes? Then go for SQL2000 dev
edition.It[vbcol=seagreen]
> comes cheap too!Refer the below url for pricing details:
> 'Developer Edition Licensing'
> http://www.microsoft.com/sql/howtobuy/development.asp
> Yes.Please note that personal edition has a documented limitation of
> handling concurrent queries.
not
> being able to connect to the personal edition?
> Well, since I have not seen the connection code, I cant say for
sure.But ,
> provided the connection code is correct, there is no reason why it
wont[vbcol=seagreen]
> connect to the SQLServer just because its a personal edition.
>
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "rocky" <anonymous@.discussions.microsoft.com> wrote in message
> news:B8C34350-D3A5-4BAB-A9DF-22719F142E01@.microsoft.com...
use the
> personal edition software, because we only have windows 98 and me.
What my
> questions is, that we I have the personal edition running on one of
my two
> machines. Will I be able to connect to the database from another
machine.
> We use a manufactoring program and it will be running a SQL database,
when I
> connect to my program is it going to give me an error about not being
able
> to connect to the personal edition? Thanks
>
>
|||First of all thanks for all your help. There will only be two at most concurrently connected. That is if you count the server as one. As far as your question I guess I am confused and I am sorry for that. Whether or not it is development or not. I th
ink it is just production. Because all we are doing is using a program that connects to the database, and updates the database as necessary, so whether or not that is developement or production I am not sure. From reading off of Microsoft's site it wasn
't clear if I could connect more than one machines to a personal edition server, but from your answer I guess I can. So thanks for you help.

Personal Edition

I am going to be purchasing SQL 2000 Standard Edition, in order to use the p
ersonal edition software, because we only have Windows 98 and me. What my q
uestions is, that we I have the personal edition running on one of my two ma
chines. Will I be able to
connect to the database from another machine. We use a manufactoring progra
m and it will be running a SQL database, when I connect to my program is it
going to give me an error about not being able to connect to the personal ed
ition? ThanksRocky,
Is this solely for development purposes? Then go for SQL2000 dev edition.It
comes cheap too!Refer the below url for pricing details:
'Developer Edition Licensing'
http://www.microsoft.com/sql/howtobuy/development.asp

Yes.Please note that personal edition has a documented limitation of
handling concurrent queries.
[vbcol=seagreen]
being able to connect to the personal edition?
Well, since I have not seen the connection code, I cant say for sure.But ,
provided the connection code is correct, there is no reason why it wont
connect to the SQLServer just because its a personal edition.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"rocky" <anonymous@.discussions.microsoft.com> wrote in message
news:B8C34350-D3A5-4BAB-A9DF-22719F142E01@.microsoft.com...[vbcol=seagreen]
> I am going to be purchasing SQL 2000 Standard Edition, in order to use the
personal edition software, because we only have Windows 98 and me. What my
questions is, that we I have the personal edition running on one of my two
machines. Will I be able to connect to the database from another machine.
We use a manufactoring program and it will be running a SQL database, when I
connect to my program is it going to give me an error about not being able
to connect to the personal edition? Thanks|||All that I will be doing with it is installing the SQL Server on a machine,
and connecting to the database through a program that we use.
-- Dinesh T.K wrote: --
Rocky,
Is this solely for development purposes? Then go for SQL2000 dev edition.It
comes cheap too!Refer the below url for pricing details:
'Developer Edition Licensing'
http://www.microsoft.com/sql/howtobuy/development.asp

Yes.Please note that personal edition has a documented limitation of
handling concurrent queries.
[vbcol=seagreen]
being able to connect to the personal edition?
Well, since I have not seen the connection code, I cant say for sure.But ,
provided the connection code is correct, there is no reason why it wont
connect to the SQLServer just because its a personal edition.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"rocky" <anonymous@.discussions.microsoft.com> wrote in message
news:B8C34350-D3A5-4BAB-A9DF-22719F142E01@.microsoft.com...[vbcol=seagreen]
> I am going to be purchasing SQL 2000 Standard Edition, in order to use the
personal edition software, because we only have Windows 98 and me. What my
questions is, that we I have the personal edition running on one of my two
machines. Will I be able to connect to the database from another machine.
We use a manufactoring program and it will be running a SQL database, when I
connect to my program is it going to give me an error about not being able
to connect to the personal edition? Thanks|||Rocky,
So, as per your plan, your database would be running SQL2000 personal
edition and the application connects to this database.Coming back to the
concurrent query limit, does many users use this application concurrently?
Personal edition as well as MSDE can support only upto 8 concurrent queries
before things get real slow and messy.
I must admit, even at this point, Iam not sure if this application is used
for dev purposes or production.Can you please answer that?
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rocky" <anonymous@.discussions.microsoft.com> wrote in message
news:2A41E566-1039-48F4-8F2C-AF53CD8A6382@.microsoft.com...
> All that I will be doing with it is installing the SQL Server on a
machine, and connecting to the database through a program that we use.
>
> -- Dinesh T.K wrote: --
> Rocky,
> Is this solely for development purposes? Then go for SQL2000 dev
edition.It
> comes cheap too!Refer the below url for pricing details:
> 'Developer Edition Licensing'
> http://www.microsoft.com/sql/howtobuy/development.asp
>
> Yes.Please note that personal edition has a documented limitation of
> handling concurrent queries.
>
not[vbcol=seagreen]
> being able to connect to the personal edition?
> Well, since I have not seen the connection code, I cant say for
sure.But ,
> provided the connection code is correct, there is no reason why it
wont
> connect to the SQLServer just because its a personal edition.
>
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "rocky" <anonymous@.discussions.microsoft.com> wrote in message
> news:B8C34350-D3A5-4BAB-A9DF-22719F142E01@.microsoft.com...
use the[vbcol=seagreen]
> personal edition software, because we only have Windows 98 and me.
What my
> questions is, that we I have the personal edition running on one of
my two
> machines. Will I be able to connect to the database from another
machine.
> We use a manufactoring program and it will be running a SQL database,
when I
> connect to my program is it going to give me an error about not being
able
> to connect to the personal edition? Thanks
>
>|||First of all thanks for all your help. There will only be two at most concu
rrently connected. That is if you count the server as one. As far as your
question I guess I am confused and I am sorry for that. Whether or not it i
s development or not. I th
ink it is just production. Because all we are doing is using a program that
connects to the database, and updates the database as necessary, so whether
or not that is developement or production I am not sure. From reading off
of Microsoft's site it wasn
't clear if I could connect more than one machines to a personal edition ser
ver, but from your answer I guess I can. So thanks for you help.

Perrormance Monitor

Using Windows performance monitor, I noticed that the
Output Queue Length under my Network Interface is showing
a very high number of 4294967291. I read somewhere that
if this is longer than 2, then delays are being
experienced and a bottle neck exists. This is a Win2K
server running MS SQL 2000. What can do to determine the
source of this bottleneck and correct it?
EmmaThat would indeed be a high number. However, PerfMon counters sometimes get
messed up and I don't believe that's really the number...
Do you have the ability to reboot the box?
--
Brian
"Emma" <eeemore@.hotmail.com> wrote in message
news:025301c3aad7$2213a830$a101280a@.phx.gbl...
> Using Windows performance monitor, I noticed that the
> Output Queue Length under my Network Interface is showing
> a very high number of 4294967291. I read somewhere that
> if this is longer than 2, then delays are being
> experienced and a bottle neck exists. This is a Win2K
> server running MS SQL 2000. What can do to determine the
> source of this bottleneck and correct it?
> Emma|||I can reboot the box after business hours. I will do that
and check the number again.
Thanks
>--Original Message--
>That would indeed be a high number. However, PerfMon
counters sometimes get
>messed up and I don't believe that's really the
number...
>Do you have the ability to reboot the box?
>--
>Brian
>
>"Emma" <eeemore@.hotmail.com> wrote in message
>news:025301c3aad7$2213a830$a101280a@.phx.gbl...
>> Using Windows performance monitor, I noticed that the
>> Output Queue Length under my Network Interface is
showing
>> a very high number of 4294967291. I read somewhere that
>> if this is longer than 2, then delays are being
>> experienced and a bottle neck exists. This is a Win2K
>> server running MS SQL 2000. What can do to determine
the
>> source of this bottleneck and correct it?
>> Emma
>
>.
>|||Emma,
How did you resolve your problem with the OUTPUT QUEUE LENGTH- as I am having the same issues on my ISA servers Rebooting the server does not seem to help as it shoots right up again. this does appear to be an ISA specific issue but it would be helpful to know why.
regards,
Ade
-- Emma wrote: --
I can reboot the box after business hours. I will do that
and check the number again.
Thanks
>--Original Message--
>That would indeed be a high number. However, PerfMon
counters sometimes get
>messed up and I don't believe that's really the
number...
>>Do you have the ability to reboot the box?
>>--
>>Brian
>>"Emma" <eeemore@.hotmail.com> wrote in message
>news:025301c3aad7$2213a830$a101280a@.phx.gbl...
>> Using Windows performance monitor, I noticed that the
>> Output Queue Length under my Network Interface is
showing
>> a very high number of 4294967291. I read somewhere that
>> if this is longer than 2, then delays are being
>> experienced and a bottle neck exists. This is a Win2K
>> server running MS SQL 2000. What can do to determine
the
>> source of this bottleneck and correct it?
>> Emma
>>.
>

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 when using trusted connection

HI,
I am having a little different issue. When my windows users login into sql s
erver with trustes connection, they can select the data but no update and in
sert. It said permission denied. But I have put them into DBO group or role
and have all permission on
database. What went wrong ? Please help. Many thanks.Why don't you make these users members of the db_datawriter fixed database
role? You can find more info in books online but here's an example:
EXEC sp_addrolemember 'db_datawriter', 'username'
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Eugene" <eliu@.ctisinc.com> wrote in message
news:30D10394-6085-4C6B-A268-9E3BFA75833A@.microsoft.com...

quote:

---

> HI,
> I am having a little different issue. When my windows users login into sql

---

server with trustes connection, they can select the data but no update and
insert. It said permission denied. But I have put them into DBO group or
role and have all permission on database. What went wrong ? Please help.
Many thanks.

permissions not scripting in 2000

We have several databases that were migrated to SQL2000 from 7.0 on the same
server running Windows 2000 Server. Only a couple of the databases are having
a problem that when we script out the stored procedures or any object for
that matter (with the script permissions box selected) the object or SP is
scripted out but the GRANT permissions portion of the script is omitted. We
also cannot see the permissions for the object in the GUI of Enterprise
Manager. The other databases work fine on the same server. Is it a switch for
this particular database or something? Any help on this matter would be
greatly appreciated.
Did you check "Script object-level permissions" on the Options tab.
Also, you can generate scripts via Query Analyzer. It has the options for
scripting permissions, too.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> We have several databases that were migrated to SQL2000 from 7.0 on the
> same
> server running Windows 2000 Server. Only a couple of the databases are
> having
> a problem that when we script out the stored procedures or any object for
> that matter (with the script permissions box selected) the object or SP is
> scripted out but the GRANT permissions portion of the script is omitted.
> We
> also cannot see the permissions for the object in the GUI of Enterprise
> Manager. The other databases work fine on the same server. Is it a switch
> for
> this particular database or something? Any help on this matter would be
> greatly appreciated.
|||First of all thanks for the response. Yes, the "Script object-level
permissions" box is checked. It's weird because it seems to work on the other
databases that are on the same instance of SQLServer but not on just this
one. The object level permissions do not show up if you pull up the object in
the EM GUI either. I have ben told that you can see the permissions in the
syspermissions table they just don't appear in the GUI or when scripted. Any
help is appreciated.
"oj" wrote:

> Did you check "Script object-level permissions" on the Options tab.
> Also, you can generate scripts via Query Analyzer. It has the options for
> scripting permissions, too.
>
> --
> -oj
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
>
>
|||Sorry for the late reply...been out of town for the last few weeks.
Anyway, this sounds like a problem with the GUI (though, I'm unable to repro
it at my end). If this is important to you, you can open a case with MS
Support. They won't charge you if this is indeed a bug.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:90927829-B83C-4A24-BDF0-E87B276914B8@.microsoft.com...[vbcol=seagreen]
> First of all thanks for the response. Yes, the "Script object-level
> permissions" box is checked. It's weird because it seems to work on the
> other
> databases that are on the same instance of SQLServer but not on just this
> one. The object level permissions do not show up if you pull up the object
> in
> the EM GUI either. I have ben told that you can see the permissions in the
> syspermissions table they just don't appear in the GUI or when scripted.
> Any
> help is appreciated.
> "oj" wrote:

permissions not scripting in 2000

We have several databases that were migrated to SQL2000 from 7.0 on the same
server running Windows 2000 Server. Only a couple of the databases are havin
g
a problem that when we script out the stored procedures or any object for
that matter (with the script permissions box selected) the object or SP is
scripted out but the GRANT permissions portion of the script is omitted. We
also cannot see the permissions for the object in the GUI of Enterprise
Manager. The other databases work fine on the same server. Is it a switch fo
r
this particular database or something? Any help on this matter would be
greatly appreciated.Did you check "Script object-level permissions" on the Options tab.
Also, you can generate scripts via Query Analyzer. It has the options for
scripting permissions, too.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> We have several databases that were migrated to SQL2000 from 7.0 on the
> same
> server running Windows 2000 Server. Only a couple of the databases are
> having
> a problem that when we script out the stored procedures or any object for
> that matter (with the script permissions box selected) the object or SP is
> scripted out but the GRANT permissions portion of the script is omitted.
> We
> also cannot see the permissions for the object in the GUI of Enterprise
> Manager. The other databases work fine on the same server. Is it a switch
> for
> this particular database or something? Any help on this matter would be
> greatly appreciated.|||First of all thanks for the response. Yes, the "Script object-level
permissions" box is checked. It's weird because it seems to work on the othe
r
databases that are on the same instance of SQLServer but not on just this
one. The object level permissions do not show up if you pull up the object i
n
the EM GUI either. I have ben told that you can see the permissions in the
syspermissions table they just don't appear in the GUI or when scripted. Any
help is appreciated.
"oj" wrote:

> Did you check "Script object-level permissions" on the Options tab.
> Also, you can generate scripts via Query Analyzer. It has the options for
> scripting permissions, too.
>
> --
> -oj
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
>
>|||Sorry for the late reply...been out of town for the last few weeks.
Anyway, this sounds like a problem with the GUI (though, I'm unable to repro
it at my end). If this is important to you, you can open a case with MS
Support. They won't charge you if this is indeed a bug.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:90927829-B83C-4A24-BDF0-E87B276914B8@.microsoft.com...[vbcol=seagreen]
> First of all thanks for the response. Yes, the "Script object-level
> permissions" box is checked. It's weird because it seems to work on the
> other
> databases that are on the same instance of SQLServer but not on just this
> one. The object level permissions do not show up if you pull up the object
> in
> the EM GUI either. I have ben told that you can see the permissions in the
> syspermissions table they just don't appear in the GUI or when scripted.
> Any
> help is appreciated.
> "oj" wrote:
>

permissions not scripting in 2000

We have several databases that were migrated to SQL2000 from 7.0 on the same
server running Windows 2000 Server. Only a couple of the databases are having
a problem that when we script out the stored procedures or any object for
that matter (with the script permissions box selected) the object or SP is
scripted out but the GRANT permissions portion of the script is omitted. We
also cannot see the permissions for the object in the GUI of Enterprise
Manager. The other databases work fine on the same server. Is it a switch for
this particular database or something? Any help on this matter would be
greatly appreciated.Did you check "Script object-level permissions" on the Options tab.
Also, you can generate scripts via Query Analyzer. It has the options for
scripting permissions, too.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> We have several databases that were migrated to SQL2000 from 7.0 on the
> same
> server running Windows 2000 Server. Only a couple of the databases are
> having
> a problem that when we script out the stored procedures or any object for
> that matter (with the script permissions box selected) the object or SP is
> scripted out but the GRANT permissions portion of the script is omitted.
> We
> also cannot see the permissions for the object in the GUI of Enterprise
> Manager. The other databases work fine on the same server. Is it a switch
> for
> this particular database or something? Any help on this matter would be
> greatly appreciated.|||First of all thanks for the response. Yes, the "Script object-level
permissions" box is checked. It's weird because it seems to work on the other
databases that are on the same instance of SQLServer but not on just this
one. The object level permissions do not show up if you pull up the object in
the EM GUI either. I have ben told that you can see the permissions in the
syspermissions table they just don't appear in the GUI or when scripted. Any
help is appreciated.
"oj" wrote:
> Did you check "Script object-level permissions" on the Options tab.
> Also, you can generate scripts via Query Analyzer. It has the options for
> scripting permissions, too.
>
> --
> -oj
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> > We have several databases that were migrated to SQL2000 from 7.0 on the
> > same
> > server running Windows 2000 Server. Only a couple of the databases are
> > having
> > a problem that when we script out the stored procedures or any object for
> > that matter (with the script permissions box selected) the object or SP is
> > scripted out but the GRANT permissions portion of the script is omitted.
> > We
> > also cannot see the permissions for the object in the GUI of Enterprise
> > Manager. The other databases work fine on the same server. Is it a switch
> > for
> > this particular database or something? Any help on this matter would be
> > greatly appreciated.
>
>|||Sorry for the late reply...been out of town for the last few weeks.
Anyway, this sounds like a problem with the GUI (though, I'm unable to repro
it at my end). If this is important to you, you can open a case with MS
Support. They won't charge you if this is indeed a bug.
--
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:90927829-B83C-4A24-BDF0-E87B276914B8@.microsoft.com...
> First of all thanks for the response. Yes, the "Script object-level
> permissions" box is checked. It's weird because it seems to work on the
> other
> databases that are on the same instance of SQLServer but not on just this
> one. The object level permissions do not show up if you pull up the object
> in
> the EM GUI either. I have ben told that you can see the permissions in the
> syspermissions table they just don't appear in the GUI or when scripted.
> Any
> help is appreciated.
> "oj" wrote:
>> Did you check "Script object-level permissions" on the Options tab.
>> Also, you can generate scripts via Query Analyzer. It has the options for
>> scripting permissions, too.
>>
>> --
>> -oj
>>
>> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
>> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
>> > We have several databases that were migrated to SQL2000 from 7.0 on the
>> > same
>> > server running Windows 2000 Server. Only a couple of the databases are
>> > having
>> > a problem that when we script out the stored procedures or any object
>> > for
>> > that matter (with the script permissions box selected) the object or SP
>> > is
>> > scripted out but the GRANT permissions portion of the script is
>> > omitted.
>> > We
>> > also cannot see the permissions for the object in the GUI of Enterprise
>> > Manager. The other databases work fine on the same server. Is it a
>> > switch
>> > for
>> > this particular database or something? Any help on this matter would be
>> > greatly appreciated.
>>sql

Permissions not effective for Windows Authentication login

Hello All,

I'm hoping someone can help me with this puzzle.

Most logins I've created have been SQL Server authenticated. I assign the login newEmployee to a role existingRole, and ensure the role has the required permissions. This didn't seem to be rocket science....

My company has been provided with an application with a SQL Server back-end. My instructions were to create a Windows authenticated login and give it full access to the database. I followed the above principles, but running the application, the user got the error -

SELECT permission denied on object 'sysobjects', database 'databasename', owner 'dbo'.

So I decided to try the simplest possible scenario to make it work:

I've created a login DOMAIN\newEmployee with Windows authentication.

DOMAIN\newEmployee has been granted access to databasename.

By default, DOMAIN\newEmployee is a member of Public.

Public has been granted all available permissions on all objects.

ie... grant all on userTables to public

........grant all on sysobjects to public

........grant all on otherSystemTables to public

etc.

Running the application, the user still gets the above error. I'd send the problem back to the vendor, except if I've logged onto the PC as DOMAIN\newEmployee, querying -

select * from dbo.sysobjects

via Query Analyser produces the same error message. (An equivalent error message is produced when querying a user-created table).

To compare, I then created a login newEmployee2 with SQL Server authentication.

newEmployee2 has been granted access to databasename.

select * from dbo.sysobjects

runs successfully from Query Analyser (as to any queries on user-created tables).

What else is required to grant access to tables from a Windows authenticated login?

( What really scares me, is that the application will run if I make the Windows authenticated login a member of server roles System Administrator and Database Creators, then the application will run - but I don't want this to be the permanent solution. Even after doing this, the above query still fails in Query Analyser for that login, suggesting that there is something wrong with how I configured the permissions. )

Any help would be appreciated.

Thanks.

Kim.

Moved to Security.|||

Let me see if I understand the scenario, please correct me if I am missing something:

· DOMAIN\newEmployee & DOMAIN\newEmploee2 are Windows domain users

· DOMAIN\newEmployee is a member of existingRole

· public has been granted the permissions you mentioned.

· DOMAIN\newEmploee2 can select from dbo.sysobjects

· DOMAIN\newEmploee cannot select from dbo.sysobjects and gets back a permission denied error

From your description, it seems like the most likely cause is that newEmployee has an explicit denied permission on dbo.sysobjects either directly or via a role membership. Check the permissions for all the roles and groups that newEmployee is a member of.

BTW. Some of the objects and permissions that you mentioned here are deprecated, they will still work on SQL Server 2005, but they are supported only for backwards compatibility.

Let us know if this information was useful or/and if you have further question, please also let us know what version of SQL Server you are using in order to better assist you.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

D'oh !! I've checked db_denydatareader instead of db_datawriter.

I'll now crawl into a hole and die of embarrassment.

Thanks for the help.

Kim.

Permissions issue- a previous windows group still has full cube rights

Previously, a windows group was added to a 'full access users' role. It was removed and the cube was processed and deployed, replacing rights, but people in that group still have rights to see the data. They are not listed in any roles.

I've looked through all local windows groups on the supporting server and the group in question is not in any groups (ie, if they were in administrators, then they'd have full access through that). The people in the group are not administrators or domain administrators either.

Is there somewhere else I could check on the machine to better find the source of the problem and get the group removed?

Several places to check.

See if you allowed anonymous access to your cubes.
Check memebership of server-wide Admnistrators role.

It is sometimes little hard to track down amongst several groups what exactly is going on. To make sure you are looking at last version of data use SQL Management Studio to verify roles and memberships.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I just checked if other users that never had rights could see the cube and they can't. Anonymous access cannot be allowed.

The windows group "Adminstrators" was checked, but I don't see the admin role present in the dwproj area.
Where would one look besides the roles folder in the solution explorer or this kb: http://msdn2.microsoft.com/en-us/library/ms174561.aspx ? The group is not in any of these areas.

sql mgmt studio was used for examination - the cube was scripted to a file and scanned for groups and user names.

|||

In general there are 2 places to check:
One- role membership. And you check that on database role
Second - access permissions. There are several permissions on different objects. Cube, dimension ...

I also suggested you take a look at memebership of Analysis Server Administrators role and not OS Administrators group.

To access AS Administrators role right click on the Server name in SQL Management Studio and select properties and click on the Security node.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||It seems like all of these have been checked. The server security was examined and the cube's xmla was parsed - this should cover any cube/dimension security, right?|||

2 more things.

Make sure you installed latest product update.

And finally. It is good idea to get your security model examined by someone with good knowlege of Analysis Services 2005.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||For product updates, we are on sp1. I could not find any hot fixes related to this.
I will keep investigating and will see if the problem can be recreated on a copy of the cube.