Cannot read the next data row for the data set

Hello all -
Our users are getting the above error when trying to execute a report.
Permission has been granted to the security group that they belong to, for
the report folder and report (browser permissions), as well as the Analytical
Services database and the associated data warehouse database (db_datareader).
The report uses two parameters, which have default values set (year and
month).
Any ideas as to what may be the problem?
Thanks!
Pam RichardsonIn case anyone else runs into this problem, here's the answer. In the
analysis services database, the Reader role/Cell Data had the "Enable read
permissions" checkbox checked, but nothing was in the "Allow reading of cube
content" textbox. Unchecking this checkbox solved the problem.
Thanks,
Pam Richardson
"Pam Richardson" wrote:
> Hello all -
> Our users are getting the above error when trying to execute a report.
> Permission has been granted to the security group that they belong to, for
> the report folder and report (browser permissions), as well as the Analytical
> Services database and the associated data warehouse database (db_datareader).
> The report uses two parameters, which have default values set (year and
> month).
> Any ideas as to what may be the problem?
> Thanks!
> Pam Richardson

Cannot get write permissions to a folder

I have a server ser1 which has the sql server running under the
security context of the local admin.Whn i try to copy the backup files
from this server to a network share it acnnot access the share unless
the sever has all authenticated user write permisssions.On setting the
auditing on we found that ser1$ uis the account which is trying to
write to the share.My question is as follows:- what is the difference
between the domaonname\ser1 and domainname\ser1$ account.I cannot see
this ser1$ account in the domain so I caaanot give it permissions.Is
there a way I could get to this account and give it permissions to
tighter up the security?
Thanks in anticipation.
AjayI'm not a network adminstrator, but I tried the same thing before and it
doesn't work. If you want SQL to have access to network resources it has to
run under a domain account. Then you can grant permissions to that account
on the network share you are trying to access. You will either need to setup
sql server to run under a domain account or have sql server write the backup
locally and schedule a job that can run with domain credentials to copy the
backup to the network share.
Mark
"Ag" <ajaygarg1962@.gmail.com> wrote in message
news:1154617829.443666.306270@.i42g2000cwa.googlegroups.com...
>I have a server ser1 which has the sql server running under the
> security context of the local admin.Whn i try to copy the backup files
> from this server to a network share it acnnot access the share unless
> the sever has all authenticated user write permisssions.On setting the
> auditing on we found that ser1$ uis the account which is trying to
> write to the share.My question is as follows:- what is the difference
> between the domaonname\ser1 and domainname\ser1$ account.I cannot see
> this ser1$ account in the domain so I caaanot give it permissions.Is
> there a way I could get to this account and give it permissions to
> tighter up the security?
> Thanks in anticipation.
>
> Ajay
>|||Mark Miller wrote:
> I'm not a network adminstrator, but I tried the same thing before and it
> doesn't work. If you want SQL to have access to network resources it has to
> run under a domain account. Then you can grant permissions to that account
> on the network share you are trying to access. You will either need to setup
> sql server to run under a domain account or have sql server write the backup
> locally and schedule a job that can run with domain credentials to copy the
> backup to the network share.
> Mark
> "Ag" <ajaygarg1962@.gmail.com> wrote in message
> news:1154617829.443666.306270@.i42g2000cwa.googlegroups.com...
> >I have a server ser1 which has the sql server running under the
> > security context of the local admin.Whn i try to copy the backup files
> > from this server to a network share it acnnot access the share unless
> > the sever has all authenticated user write permisssions.On setting the
> > auditing on we found that ser1$ uis the account which is trying to
> > write to the share.My question is as follows:- what is the difference
> > between the domaonname\ser1 and domainname\ser1$ account.I cannot see
> > this ser1$ account in the domain so I caaanot give it permissions.Is
> > there a way I could get to this account and give it permissions to
> > tighter up the security?
> >
> > Thanks in anticipation.
> >
> >
> > Ajay
Thanks Mark for the response:-
Here is what I notices and I was able to resolve the issue:-
ser1$ is the computername account which is trying to acces a directory
whenever the sql server uses a local admin account.We can resolve this
in three ways:-
1.Give the network share permissions for the the
domainname/computername account in this case domainname/compuetname$ .
2.Change sql servr to use a domain account means chaning the apps to
use the domain acocunt a non starteer in my enviormment as the apps are
owned by the vendors.
3.Use a proxy account for the sql server agent so that even if sql runs
on a local admin account the proxy account would enable the agent to
use a doain account.
Thanks for your response.
Ajay
> >|||Ag wrote:
> Mark Miller wrote:
>> I'm not a network adminstrator, but I tried the same thing before and it
>> doesn't work. If you want SQL to have access to network resources it has to
>> run under a domain account. Then you can grant permissions to that account
>> on the network share you are trying to access. You will either need to setup
>> sql server to run under a domain account or have sql server write the backup
>> locally and schedule a job that can run with domain credentials to copy the
>> backup to the network share.
>> Mark
>> "Ag" <ajaygarg1962@.gmail.com> wrote in message
>> news:1154617829.443666.306270@.i42g2000cwa.googlegroups.com...
>> I have a server ser1 which has the sql server running under the
>> security context of the local admin.Whn i try to copy the backup files
>> from this server to a network share it acnnot access the share unless
>> the sever has all authenticated user write permisssions.On setting the
>> auditing on we found that ser1$ uis the account which is trying to
>> write to the share.My question is as follows:- what is the difference
>> between the domaonname\ser1 and domainname\ser1$ account.I cannot see
>> this ser1$ account in the domain so I caaanot give it permissions.Is
>> there a way I could get to this account and give it permissions to
>> tighter up the security?
>> Thanks in anticipation.
>>
>> Ajay
> Thanks Mark for the response:-
> Here is what I notices and I was able to resolve the issue:-
> ser1$ is the computername account which is trying to acces a directory
> whenever the sql server uses a local admin account.We can resolve this
> in three ways:-
> 1.Give the network share permissions for the the
> domainname/computername account in this case domainname/compuetname$ .
> 2.Change sql servr to use a domain account means chaning the apps to
> use the domain acocunt a non starteer in my enviormment as the apps are
> owned by the vendors.
This is just plain wrong. Changing the account that the SQL Server and
SQL Agent services run under has absolutely nothing to do with the
logins use by applications to connect to SQL databases. If you want to
access network resources from within SQL, this is how you do it. Change
the MSSQLSERVER and SQLSERVERAGENT services to run as a domain user, and
grant share permissions to that domain user.
> 3.Use a proxy account for the sql server agent so that even if sql runs
> on a local admin account the proxy account would enable the agent to
> use a doain account.
> Thanks for your response.
>
> Ajay
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> Ag wrote:
> > Mark Miller wrote:
> >> I'm not a network adminstrator, but I tried the same thing before and it
> >> doesn't work. If you want SQL to have access to network resources it has to
> >> run under a domain account. Then you can grant permissions to that account
> >> on the network share you are trying to access. You will either need to setup
> >> sql server to run under a domain account or have sql server write the backup
> >> locally and schedule a job that can run with domain credentials to copy the
> >> backup to the network share.
> >>
> >> Mark
> >>
> >> "Ag" <ajaygarg1962@.gmail.com> wrote in message
> >> news:1154617829.443666.306270@.i42g2000cwa.googlegroups.com...
> >> I have a server ser1 which has the sql server running under the
> >> security context of the local admin.Whn i try to copy the backup files
> >> from this server to a network share it acnnot access the share unless
> >> the sever has all authenticated user write permisssions.On setting the
> >> auditing on we found that ser1$ uis the account which is trying to
> >> write to the share.My question is as follows:- what is the difference
> >> between the domaonname\ser1 and domainname\ser1$ account.I cannot see
> >> this ser1$ account in the domain so I caaanot give it permissions.Is
> >> there a way I could get to this account and give it permissions to
> >> tighter up the security?
> >>
> >> Thanks in anticipation.
> >>
> >>
> >> Ajay
> >
> > Thanks Mark for the response:-
> >
> > Here is what I notices and I was able to resolve the issue:-
> >
> > ser1$ is the computername account which is trying to acces a directory
> > whenever the sql server uses a local admin account.We can resolve this
> > in three ways:-
> >
> > 1.Give the network share permissions for the the
> > domainname/computername account in this case domainname/compuetname$ .
> > 2.Change sql servr to use a domain account means chaning the apps to
> > use the domain acocunt a non starteer in my enviormment as the apps are
> > owned by the vendors.
> This is just plain wrong. Changing the account that the SQL Server and
> SQL Agent services run under has absolutely nothing to do with the
> logins use by applications to connect to SQL databases. If you want to
> access network resources from within SQL, this is how you do it. Change
> the MSSQLSERVER and SQLSERVERAGENT services to run as a domain user, and
> grant share permissions to that domain user.
>
> > 3.Use a proxy account for the sql server agent so that even if sql runs
> > on a local admin account the proxy account would enable the agent to
> > use a doain account.
> >
> > Thanks for your response.
> >
> >
> > Ajay
> >
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Maybe i did not explain it right:- AT the sql end changing the sql
server to use the domain service accoutn would make the sql server run
on a domain account but the DSn which is configurd to use the local
admin account and use IIS has been configured to use the local admin by
defualt(that is what irt chooses) SO CHANING THE SECURITY CONTEXT OF
THE SQL SERVER SERVICE AND THE SQL AGENT SERICE WOULD MEAN THAT I
CHANGE THE DSN OF ALL THE APPLICATIONS WHICH ARE CONFIGURED TO LOGIN
USING NT ATHENTICATION AND LOCAL ADMIN account on the box.Surprisingly
most apps reside on the same box as the sql server and use the Local
Admin account and are configured to use a dsn /logins odf the local
account on sql server.
This is diiifcult as it entails coordinatig with vendors and so I wold
not wat to go with this option.

Cannot get CREATE LOGIN from a Windows group to work

I have created a database fronted by an ASP.Net application. It's all nice and simple, and I only need a very simple level of security (and even that is only as a protection against accidents rather than maliciousness). My intention is that users connect using Windows impersonation (<identity="true">), with the database creator having full access and the public group (I'm talking SQL groups here) having specific premissions granted on specific tables.

If I set <identity="false"> on my XP box the application connects to the database as [MACHINE\ASPNET]. This is easy to set up access for - I simply do a

CREATE LOGIN [MACHINE\ASPNET] FROM WINDOWS

and then within the actual database do a

CREATE USER [MACHINE\ASPNET]

But as I said, I want to use Windows impersonation. When I set <identity="true">, the application correctly attempts to connect as the actual Windows user account (e.g. [MACHINE\testuser]). If that user is the user who installed the database, then all is well and it has full access. For anything else, I get a "cannot log on" error - this much I expect.

So I want to permit logins for all other users, and I want this to work regardless of whether the machine is a standalone machine whose "domain" is simply the machine's own name, whether it is in some form of traditional peer-to-peer workgroup, or whether the machine is connected to a real domain. I also want it to work on XP and Windows Server 2003 (and ideally Vista also, but that can wait). When I try the following:

CREATE LOGIN [MACHINE\Users] FROM WINDOWS

I get this error:

Msg 15401, Level 16, State 1, Server MACHINE\SQLEXPRESS, Line 1
Windows NT user or group 'MACHINE\Users' not found. Check the name again.

Nor does it work with [Everyone] (that one has no domain/folder listed against it in any permissions dialogs on my domainless development PC). So I'm stuck and confused. It's taken me ages just to get this far. Any suggestions anyone?

Thanks in advance.

Hi,

Ensure your windows users have grant access for that db.

Refer for adding windows user/group to sql server for work around.

http://msdn2.microsoft.com/en-gb/library/aa905177(sql.80).aspx

http://msdn2.microsoft.com/en-us/library/aa163753(office.10).aspx

http://www.databasedesign-resource.com/adding-users-in-sql-server.html

Hemantgiri S. Goswami

|||

Thanks, but that doesn't really help. One of these articles simply explains how to create Windows accounts, one explains how to use the sp_grantlogin storproc (which essentially issues the same SQL commands as I have listed above, and it gives exactly the same error messages), and the third tells you how to do it all manually, whereas I need to do it programmatically.

I should add that when I try to add a specific user account rather than the group account, the command works fine, so something is wrong with the attempt to add the group account. Does anyone know anything specific to groups?

|||I have found the answer. The issue is local versus global groups. CREATE LOGIN [domain\group] FROM WINDOWS works fine (i.e. a global group), but if you try it with a local group such as MACHINE\Users it will fail every time - this is a security feature of SQL Server. Which means that this approach will only work in a domain environment and not in a workgroup or on a shared single machine. The alternative is to use a nice GUI tool to manually add logins and users to SQL Express, but since SQL Express does not come with nice tools like Enterprise Manager, I may have to write my own and include it in the distribution. The group 'Everyone' fails also because it's what I would term a pseudo-group rather than either a local or a glocal group.|||

hi,

michael412 wrote:

I have found the answer. The issue is local versus global groups. CREATE LOGIN [domain\group] FROM WINDOWS works fine (i.e. a global group), but if you try it with a local group such as MACHINE\Users it will fail every time - this is a security feature of SQL Server. Which means that this approach will only work in a domain environment and not in a workgroup or on a shared single machine. The alternative is to use a nice GUI tool to manually add logins and users to SQL Express, but since SQL Express does not come with nice tools like Enterprise Manager, I may have to write my own and include it in the distribution. The group 'Everyone' fails also because it's what I would term a pseudo-group rather than either a local or a glocal group.

you can try defining your own local Windows group...
add a new Group via the "Computer Management" MMC snap-in, say you name it "MyMachineAllowedAccounts" and add the required windows accounts as members to that group...

then use the "nice ui tool" SQLExpress is provided with (yes, SQLEpress provides an official one ) and execute a standard
CREATE LOGIN [MACHINE_NAME\MyMachineAllowedAccounts] FROM WINDOWS;
and it will succed as expected..

regards

|||

The plot thickens. If I define a group MyGroup myself, then I can do CREATE LOGIN [MACHINE\MyGroup] FROM WINDOWS and it works. If I try to use one of the predefined groups such as MACHINE\Users, it fails. I don't at this point know whether this rule is constrained to local groups or whether it applies to global groups also. The group I added this morning in the office was a global group that we had created (i.e. it wasn't a predefined one), whereas the group that I failed to add was a predefined local group. I concluded that the important difference was whether the group was local or global, but now I realise that it could have been purely down to whether the group was predefined or created after installation. I don't see why SQL Server would want to prevent the use of predefined groups, and I've seen no mention of the importance of this distinction in any of the documentation. Can anyone throw any light on this?

Also, what is this GUI tool that is supposedly provided? I've looked through all the directories that were installed with SQL EXPRESS and haven't found one.

|||

hi Michael,

michael412 wrote:

The plot thickens. If I define a group MyGroup myself, then I can do CREATE LOGIN [MACHINE\MyGroup] FROM WINDOWS and it works. If I try to use one of the predefined groups such as MACHINE\Users, it fails.

it's only a "syntactical" problem as "how" you reference your predefined group's names...

CREATE LOGIN [BUILTIN\Users] FROM WINDOWS;

I don't at this point know whether this rule is constrained to local groups or whether it applies to global groups also. The group I added this morning in the office was a global group that we had created (i.e. it wasn't a predefined one), whereas the group that I failed to add was a predefined local group. I concluded that the important difference was whether the group was local or global, but now I realise that it could have been purely down to whether the group was predefined or created after installation. I don't see why SQL Server would want to prevent the use of predefined groups, and I've seen no mention of the importance of this distinction in any of the documentation. Can anyone throw any light on this?

check your syntax

CREATE LOGIN [BUILTIN\Users] FROM WINDOWS;

Also, what is this GUI tool that is supposedly provided? I've looked through all the directories that were installed with SQL EXPRESS and haven't found one.

SQL Server Management Studio Express is not installed as part of the "client components" of the "standard" package and must be manually and separately downloaded and installed.. on the contrary, SQL Server Express with Advanced Services includes it and the tool can be selected to be installed as part of the installation process..

regards

|||

Thanks Andrea - I think that's me sorted out now. Sorry to take so long to reply - I got tired and took a couple of days break from the PC.

Best regards,

Michael

Cannot find the queue 'SqlQueryNotificationService-{guid}'

I am trying to get my application running on a customer site. They are being very picky about security on the database. What are the correct permissions to setup to allow my sql user to use the Service Broker.

Currently I am receiving the following error:

Cannot fin the user 'owner', because it does not exist or you do not have permission.

Cannot find the queue 'SqlQueryNotificationService-<guid>', because it does not exist or you do not have permission.

If I make my user db_owner it seems to work on other installs.

Thanks so much,
~ Steve

Based on the service name, this seems to be a SqlDependency case, isn't it?

Can you explain which command do you run when you're getting this error?

Thanks,
~ Remus

|||

I am using the code exactly from the following article in MSDN Magazine for an ASP.NET website. http://msdn.microsoft.com/msdnmag/issues/06/02/WickedCode/

Basically I'm substituting a sql database for the XML file to make things better on my end.

When I try to run the site and hit the first page, I get that error above.

I have deployed this app a 5-6 locations already, in each of those cases I have my own SQL Express database so I'm controlling the security (which I made my sql user db_owner).

This is a SQL SQL2005 Standard install which I don't have full access.

Please let me know if you need any more information.

Thanks,

~ Steve

|||

In order to use the SqlDependency infrastructure, the sql user must be able to create a procedure, a service and a queue, must be granted REFERENCES permission on the QN contract and must have 'subscribe query notifications' permission. In adition, for reasons I'm not sure I comprehend, it must have have suficient permissions over the [dbo] schema to be able to create a queue and procedure in it and be able impersonate the queue owner (that is, [dbo]).

GRANT CREATE PROCEDURE TO [SqlUser];

GRANT CREATE SERVICE TO [SqlUser];

GRANT CREATE QUEUE TO [SqlUser];

GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [SqlUser];

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [SqlUser];

GRANT CONTROL ON SCHEMA::[dbo] TO [SqlUser];

GRANT IMPERSONATE ON USER::DBO TO [SqlUser];

HTH,
~ Remus

|||

Of note is that you don't need to give control over the dbo schema (which seems like it would be dangerous), rather you can create a new, empty schema and give your user rights to it.

The reason for granting rights to a schema is that all of the objects the user creates (the query notification stored procedure, etc.) need to be created in some space or "schema" so you need to configure the permissions accordingly.