Problem with sp_change_users_login

Hello,
I am trying to connect the user 'dbo' for a user database to an existing SQL
Server login, which, according to SQL Server BOL, should be accomplished by
using this syntax:
use MyUserDB
go
sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
I get an error message stating that 'dbo' is a forbidden value for the login
parameter in this procedure.
I tried reversing the order, but I get the same error.
If anyone has any clues to what is wrong I would appreciate a comment
I am working with a server which is to be a backup server for one of our Web
servers.
I am changing the setup to match the live server, where the 'dbo' user is
mapped to a login.
I did not set up either of them initially and I am not quite sure which user
names have to have a login, so I am playing it safe by matching the setup of
the live server.
Thank you
Ragnar
Use "sp_changedbowner"
Geoff N. Hiten
Microsoft SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:O6CaM$vWFHA.2060@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to connect the user 'dbo' for a user database to an existing
> SQL Server login, which, according to SQL Server BOL, should be
> accomplished by using this syntax:
> use MyUserDB
> go
> sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
> I get an error message stating that 'dbo' is a forbidden value for the
> login parameter in this procedure.
> I tried reversing the order, but I get the same error.
> If anyone has any clues to what is wrong I would appreciate a comment
> I am working with a server which is to be a backup server for one of our
> Web servers.
> I am changing the setup to match the live server, where the 'dbo' user is
> mapped to a login.
> I did not set up either of them initially and I am not quite sure which
> user names have to have a login, so I am playing it safe by matching the
> setup of the live server.
> Thank you
> Ragnar
>
|||Thank you Geoff,
It worked!
I did not try that because according to sp_helpdb the user with the login I
want to connect to dbo was already the owner.
However, when I displayed the users for the DB there was no login name shown
for the dbo user.
In the logins, under Security, this user name has the master as the default
DB, but that is true for the live server too.
I thought maybe this was similar to a case of orphaned users, which happens
when I restore database from a backup of the live server DB, even though dbo
was not shown as an orphaned user when I ran the report..
BTW, I assume the problem with orphaned users is because I have not been
able to restore the master DB with a backup from the live server, because I
have not been able to start SQL Server in single user mode.
I stop it, then start it from the command line with sqlservr.exe -c, -m, as
described in SQL Server BOL
Ragnar

Problem with sp_change_users_login

Hello,
I am trying to connect the user 'dbo' for a user database to an existing SQL
Server login, which, according to SQL Server BOL, should be accomplished by
using this syntax:
use MyUserDB
go
sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
I get an error message stating that 'dbo' is a forbidden value for the login
parameter in this procedure.
I tried reversing the order, but I get the same error.
If anyone has any clues to what is wrong I would appreciate a comment
I am working with a server which is to be a backup server for one of our Web
servers.
I am changing the setup to match the live server, where the 'dbo' user is
mapped to a login.
I did not set up either of them initially and I am not quite sure which user
names have to have a login, so I am playing it safe by matching the setup of
the live server.
Thank you
RagnarUse "sp_changedbowner"
Geoff N. Hiten
Microsoft SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:O6CaM$vWFHA.2060@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to connect the user 'dbo' for a user database to an existing
> SQL Server login, which, according to SQL Server BOL, should be
> accomplished by using this syntax:
> use MyUserDB
> go
> sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
> I get an error message stating that 'dbo' is a forbidden value for the
> login parameter in this procedure.
> I tried reversing the order, but I get the same error.
> If anyone has any clues to what is wrong I would appreciate a comment
> I am working with a server which is to be a backup server for one of our
> Web servers.
> I am changing the setup to match the live server, where the 'dbo' user is
> mapped to a login.
> I did not set up either of them initially and I am not quite sure which
> user names have to have a login, so I am playing it safe by matching the
> setup of the live server.
> Thank you
> Ragnar
>|||Thank you Geoff,
It worked!
I did not try that because according to sp_helpdb the user with the login I
want to connect to dbo was already the owner.
However, when I displayed the users for the DB there was no login name shown
for the dbo user.
In the logins, under Security, this user name has the master as the default
DB, but that is true for the live server too.
I thought maybe this was similar to a case of orphaned users, which happens
when I restore database from a backup of the live server DB, even though dbo
was not shown as an orphaned user when I ran the report..
BTW, I assume the problem with orphaned users is because I have not been
able to restore the master DB with a backup from the live server, because I
have not been able to start SQL Server in single user mode.
I stop it, then start it from the command line with sqlservr.exe -c, -m, as
described in SQL Server BOL
Ragnarsql

Problem with sp_change_users_login

Hello,
I am trying to connect the user 'dbo' for a user database to an existing SQL
Server login, which, according to SQL Server BOL, should be accomplished by
using this syntax:
use MyUserDB
go
sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
I get an error message stating that 'dbo' is a forbidden value for the login
parameter in this procedure.
I tried reversing the order, but I get the same error.
If anyone has any clues to what is wrong I would appreciate a comment
I am working with a server which is to be a backup server for one of our Web
servers.
I am changing the setup to match the live server, where the 'dbo' user is
mapped to a login.
I did not set up either of them initially and I am not quite sure which user
names have to have a login, so I am playing it safe by matching the setup of
the live server.
Thank you
RagnarUse "sp_changedbowner"
Geoff N. Hiten
Microsoft SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:O6CaM$vWFHA.2060@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to connect the user 'dbo' for a user database to an existing
> SQL Server login, which, according to SQL Server BOL, should be
> accomplished by using this syntax:
> use MyUserDB
> go
> sp_change_users_login 'update_one', 'dbo', 'MyServerLogin'
> I get an error message stating that 'dbo' is a forbidden value for the
> login parameter in this procedure.
> I tried reversing the order, but I get the same error.
> If anyone has any clues to what is wrong I would appreciate a comment
> I am working with a server which is to be a backup server for one of our
> Web servers.
> I am changing the setup to match the live server, where the 'dbo' user is
> mapped to a login.
> I did not set up either of them initially and I am not quite sure which
> user names have to have a login, so I am playing it safe by matching the
> setup of the live server.
> Thank you
> Ragnar
>|||Thank you Geoff,
It worked!
I did not try that because according to sp_helpdb the user with the login I
want to connect to dbo was already the owner.
However, when I displayed the users for the DB there was no login name shown
for the dbo user.
In the logins, under Security, this user name has the master as the default
DB, but that is true for the live server too.
I thought maybe this was similar to a case of orphaned users, which happens
when I restore database from a backup of the live server DB, even though dbo
was not shown as an orphaned user when I ran the report..
BTW, I assume the problem with orphaned users is because I have not been
able to restore the master DB with a backup from the live server, because I
have not been able to start SQL Server in single user mode.
I stop it, then start it from the command line with sqlservr.exe -c, -m, as
described in SQL Server BOL
Ragnar

Problem with regenerated report model.

Hi,

I regenerated a report model (just added a new field) and now the existing reports created using Report Builder no longer run. I get the following error:

An error has occurred during report processing.
Semantic query compilation failed: e EmptySemanticQuery The SemanticQuery does not contain any Groupings or MeasureGroups. SemanticQuery must contain at least one of these elements. (SemanticQuery '').

When I try to open the report in Repoprt Builder, it does not see any of the entities in the model. Instead, it displays "Unknown Entity".

How can I get the reports to be usable again?

Thanks,

Rocco M.

Hi Rocco,

I am also facing the same issue. Did you find the resolution ?

Thanks

Ashutosh

|||

It sounds like you used the Regenerate Model button in Report Manager, which will delete elements of the model if the underlying schema element is no longer present. The Generate command in a VS Report Model project does not do this -- it is only additive.

It appears model regeneration was unable to discover any tables/columns in the underlying database. I don't know why this might have been the case (messed up connection string?), but unfortunately the model is gone now, and it will be very tedious to resuscitate your reports. If you have a recent backup of your report server database, you are in much better shape, however.

Server-based model generation is for quick-and-dirty scenarios. If you are serious about developing and deploying reports based on a report model, you should create one in Visual Studio (or create an empty project, download the generated one from the server, and add it to the project). You will have much finer control over the evolution of the model over time, and it will be much easier to maintain backups and/or old versions as needed.

|||

Hi Ashutosh,

I did not find a resolution to this problem. All of the reports created with Report Builder needed to be re-created. This was not a good situation.

All of the changes made to the report model were done using Visual Studio.

To prevent this situation from happening again, I have created a duplicate "development" version of the report model. All proposed changes are first done to this development version and some reports created specifically against this development version are used to verify proper operation. If the verification is successful, the changs are then made to the production version of the report model using the exact same steps performed on the development version.

Regards,

Rocco

Problem with regenerated report model.

Hi,

I regenerated a report model (just added a new field) and now the existing reports created using Report Builder no longer run. I get the following error:

An error has occurred during report processing.
Semantic query compilation failed: e EmptySemanticQuery The SemanticQuery does not contain any Groupings or MeasureGroups. SemanticQuery must contain at least one of these elements. (SemanticQuery '').

When I try to open the report in Repoprt Builder, it does not see any of the entities in the model. Instead, it displays "Unknown Entity".

How can I get the reports to be usable again?

Thanks,

Rocco M.

Hi Rocco,

I am also facing the same issue. Did you find the resolution ?

Thanks

Ashutosh

|||

It sounds like you used the Regenerate Model button in Report Manager, which will delete elements of the model if the underlying schema element is no longer present. The Generate command in a VS Report Model project does not do this -- it is only additive.

It appears model regeneration was unable to discover any tables/columns in the underlying database. I don't know why this might have been the case (messed up connection string?), but unfortunately the model is gone now, and it will be very tedious to resuscitate your reports. If you have a recent backup of your report server database, you are in much better shape, however.

Server-based model generation is for quick-and-dirty scenarios. If you are serious about developing and deploying reports based on a report model, you should create one in Visual Studio (or create an empty project, download the generated one from the server, and add it to the project). You will have much finer control over the evolution of the model over time, and it will be much easier to maintain backups and/or old versions as needed.

|||

Hi Ashutosh,

I did not find a resolution to this problem. All of the reports created with Report Builder needed to be re-created. This was not a good situation.

All of the changes made to the report model were done using Visual Studio.

To prevent this situation from happening again, I have created a duplicate "development" version of the report model. All proposed changes are first done to this development version and some reports created specifically against this development version are used to verify proper operation. If the verification is successful, the changs are then made to the production version of the report model using the exact same steps performed on the development version.

Regards,

Rocco