No login name for database user.

I am using SQL2000. I took a backup of the database on the live server to setup the database on my new development computer. In the tables, there are 2 owners’ dbo and indiankarma. The tables that are owned by indiankarma are the tables that the site runs from.

The problem: The database user was created by the .bak file and does not have a login name. I created a security login named indiankarma and set the password, default database, server roles, BUT when I set the database access and click OK, it says User 'Indiankarma' already exists. I tried to delete the database user and start from scratch but it tells me the selected user cannot be dropped because the user owns objects.

Now I have tried sp_changedbowner 'indiankarma' which does give the security login name indiankarma database access but it makes the user dbo not indiankarma. I need the web application to read indiankarma.[tablename] not dbo.[tablename]

Can anyone help me with this problem??

This is a common issue after restoring a database. Check the following blog

http://weblogs.asp.net/steveschofield/archive/2005/12/31/434280.aspx

Regards

|||

I don't think I explained the problem correctly. When I restored the data base it created 2 sets of tables for each table. One set owned by dbo and the other set by indiankarma. So I have a table that is indiankarma.ikuserinfo and a table right below it that is dbo.ikuserinfo. All the site data is in the indiankarma owned tables. (see below 1.1)

1.1

The problem is that the 'indiankarma' database user has no login name. (See below 1.2).

1.2

Also, the security login 'indiankarma' does not have access to the database (see below 1.3) and when I try to give it access, I get this error 'User 'Indiankarma' already exists.' (see below 1.4)

1.3

1.4

I need to know what I can do to get database user 'indiankara' a login name and how to permit security login 'indiankarma' database access with the user 'indiankarma'. So it looks like the figure below (see below 1.5) but when I click ok, it does not give me the error. Please help!!! Sorry for all the images, I am bad at explaining problems thought they might help!

1.5

|||

I don't think I explained the problem correctly. When I restored the data base it created 2 sets of tables for each table. One set owned by dbo and the other set by indiankarma. So I have a table that is indiankarma.ikuserinfo and a table right below it that is dbo.ikuserinfo. All the site data is in the indiankarma owned tables. (see below 1.1)

1.1

The problem is that the 'indiankarma' database user has no login name. (See below 1.2).

1.2

Also, the security login 'indiankarma' does not have access to the database (see below 1.3) and when I try to give it access, I get this error 'User 'Indiankarma' already exists.' (see below 1.4)

1.3

1.4

I need to know what I can do to get database user 'indiankara' a login name and how to permit security login 'indiankarma' database access with the user 'indiankarma'. So it looks like the figure below (see below 1.5) but when I click ok, it does not give me the error. Please help!!! Sorry for all the images, I am bad at explaining problems thought they might help!

1.5

|||

I don't think I explained the problem correctly. When I restored the data base it created 2 sets of tables for each table. One set owned by dbo and the other set by indiankarma. So I have a table that is indiankarma.ikuserinfo and a table right below it that is dbo.ikuserinfo. All the site data is in the indiankarma owned tables. (see below 1.1)

1.1

The problem is that the 'indiankarma' database user has no login name. (See below 1.2).

1.2

Also, the security login 'indiankarma' does not have access to the database (see below 1.3) and when I try to give it access, I get this error 'User 'Indiankarma' already exists.' (see below 1.4)

1.3

1.4

I need to know what I can do to get database user 'indiankara' a login name and how to permit security login 'indiankarma' database access with the user 'indiankarma'. So it looks like the figure below (see below 1.5) but when I click ok, it does not give me the error. Please help!!! Sorry for all the images, I am bad at explaining problems thought they might help!

1.5

|||

I don't think I explained the problem correctly. When I restored the data base it created 2 sets of tables for each table. One set owned by dbo and the other set by indiankarma. So I have a table that is indiankarma.ikuserinfo and a table right below it that is dbo.ikuserinfo. All the site data is in the indiankarma owned tables. (see below 1.1)1.1
The problem is that the 'indiankarma' database user has no login name. (See below 1.2).1.2

Also, the security login 'indiankarma' does not have access to the database (see below 1.3) and when I try to give it access, I get this error 'User 'Indiankarma' already exists.' (see below 1.4)1.3
1.4
I need to know what I can do to get database user 'indiankara' a login name and how to permit security login 'indiankarma' database access with the user 'indiankarma'. So it looks like the figure below (see below 1.5) but when I click ok, it does not give me the error. Please help!!! Sorry for all the images, I am bad at explaining problems thought they might help!1.5

No attachments in subscription mail messages

We have reportingservices installed on the sql box. It's a W2K SP4 server
with sql2000 SP3a.
We have deployed several reports. All reports we have deployed after
installing SP1 do not contain an attached report (PDF, TIFF, Excel, CSV) but
when the report is rendered in the body of the message or when we view it in
the viewer there are no problems at all.
Reports deployed before installing SP1 are functioning correctly.
The following message we find in the logfiles:
aspnet_wp!library!c48!03/08/2005-14:29:41:: i INFO: Call to RenderFirst(
'/GMA_Test' )
aspnet_wp!library!c48!03/08/2005-14:29:51:: i INFO: Initializing
EnableExecutionLogging to 'True' as specified in Server system properties.
aspnet_wp!webserver!c48!03/08/2005-14:29:51:: i INFO: Processed report.
Report='/GMA_Test', Stream=''
aspnet_wp!library!c48!03/08/2005-14:29:52:: i INFO: Call to RenderNext(
'/GMA_Test' )
aspnet_wp!chunks!c48!03/08/2005-14:29:52:: i INFO: ###
GetReportChunk('RenderingInfo_HTML4.0', 2), chunk was not found!
this=8b4f21e9-99c6-45e0-a642-91bf34101b25
aspnet_wp!cache!c48!03/08/2005-14:29:52:: i INFO: Session live: /GMA_Test
aspnet_wp!webserver!c48!03/08/2005-14:29:52:: i INFO: Processed report.
Report='/GMA_Test', Stream=''
aspnet_wp!webserver!c48!03/08/2005-14:29:52:: i INFO: Processed report.
Report='/GMA_Test', Stream='524d8435-4afe-4a86-a02e-1bc9b4cb7f9d'
We have absolutly no clue!
Please help!Odd. Try http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
I've sent posts by MSFT stating that hotfix fixes more than Lotus Notes
attachment issues.
--
Adrian M.
MCP
"Stefan Cremers" <s.cremers@.fritom.com> wrote in message
news:e%23jZcUJJFHA.3500@.TK2MSFTNGP14.phx.gbl...
> We have reportingservices installed on the sql box. It's a W2K SP4 server
> with sql2000 SP3a.
> We have deployed several reports. All reports we have deployed after
> installing SP1 do not contain an attached report (PDF, TIFF, Excel, CSV)
> but when the report is rendered in the body of the message or when we view
> it in the viewer there are no problems at all.
> Reports deployed before installing SP1 are functioning correctly.
> The following message we find in the logfiles:
> aspnet_wp!library!c48!03/08/2005-14:29:41:: i INFO: Call to RenderFirst(
> '/GMA_Test' )
> aspnet_wp!library!c48!03/08/2005-14:29:51:: i INFO: Initializing
> EnableExecutionLogging to 'True' as specified in Server system
> properties.
> aspnet_wp!webserver!c48!03/08/2005-14:29:51:: i INFO: Processed report.
> Report='/GMA_Test', Stream=''
> aspnet_wp!library!c48!03/08/2005-14:29:52:: i INFO: Call to RenderNext(
> '/GMA_Test' )
> aspnet_wp!chunks!c48!03/08/2005-14:29:52:: i INFO: ###
> GetReportChunk('RenderingInfo_HTML4.0', 2), chunk was not found!
> this=8b4f21e9-99c6-45e0-a642-91bf34101b25
> aspnet_wp!cache!c48!03/08/2005-14:29:52:: i INFO: Session live: /GMA_Test
> aspnet_wp!webserver!c48!03/08/2005-14:29:52:: i INFO: Processed report.
> Report='/GMA_Test', Stream=''
> aspnet_wp!webserver!c48!03/08/2005-14:29:52:: i INFO: Processed report.
> Report='/GMA_Test', Stream='524d8435-4afe-4a86-a02e-1bc9b4cb7f9d'
>
> We have absolutly no clue!
> Please help!
>|||Adrian,
Thanks for your help.
I installed the hotfix but nithing chanced. Our problem still exists.
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:uARmooKJFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Odd. Try http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
> I've sent posts by MSFT stating that hotfix fixes more than Lotus Notes
> attachment issues.
> --
> Adrian M.
> MCP
>|||I have seen this similar issue. In my case I can send a link but if I send
the report nothing gets sent. I gave up and decided to wait for SP2 (this
month) and see if that fixes the problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stefan Cremers" <s.cremers@.fritom.com> wrote in message
news:OubUtTOJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Adrian,
> Thanks for your help.
> I installed the hotfix but nithing chanced. Our problem still exists.
>
> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> news:uARmooKJFHA.1476@.TK2MSFTNGP09.phx.gbl...
> > Odd. Try http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
> >
> > I've sent posts by MSFT stating that hotfix fixes more than Lotus Notes
> > attachment issues.
> >
> > --
> > Adrian M.
> > MCP
> >
>|||This is odd. In my web app I programmatically create subscriptions that
include the report as an attachment and it works fine.
--
Adrian M.
MCP
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uILWefOJFHA.3500@.TK2MSFTNGP14.phx.gbl...
>I have seen this similar issue. In my case I can send a link but if I send
> the report nothing gets sent. I gave up and decided to wait for SP2 (this
> month) and see if that fixes the problem.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Stefan Cremers" <s.cremers@.fritom.com> wrote in message
> news:OubUtTOJFHA.732@.TK2MSFTNGP12.phx.gbl...
>> Adrian,
>> Thanks for your help.
>> I installed the hotfix but nithing chanced. Our problem still exists.
>>
>> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
>> news:uARmooKJFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> > Odd. Try http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
>> >
>> > I've sent posts by MSFT stating that hotfix fixes more than Lotus Notes
>> > attachment issues.
>> >
>> > --
>> > Adrian M.
>> > MCP
>> >
>|||One thing I was told (but it was more than I wanted to do) was that the most
reliable method is to have the subscription be put in a directory for pickup
by your mail service. Might be something you would want to try.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:uAA4RzOJFHA.580@.TK2MSFTNGP15.phx.gbl...
> This is odd. In my web app I programmatically create subscriptions that
> include the report as an attachment and it works fine.
> --
> Adrian M.
> MCP
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uILWefOJFHA.3500@.TK2MSFTNGP14.phx.gbl...
> >I have seen this similar issue. In my case I can send a link but if I
send
> > the report nothing gets sent. I gave up and decided to wait for SP2
(this
> > month) and see if that fixes the problem.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Stefan Cremers" <s.cremers@.fritom.com> wrote in message
> > news:OubUtTOJFHA.732@.TK2MSFTNGP12.phx.gbl...
> >> Adrian,
> >>
> >> Thanks for your help.
> >>
> >> I installed the hotfix but nithing chanced. Our problem still exists.
> >>
> >>
> >>
> >> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> >> news:uARmooKJFHA.1476@.TK2MSFTNGP09.phx.gbl...
> >> > Odd. Try
http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
> >> >
> >> > I've sent posts by MSFT stating that hotfix fixes more than Lotus
Notes
> >> > attachment issues.
> >> >
> >> > --
> >> > Adrian M.
> >> > MCP
> >> >
> >>
> >
> >
>|||Here are some other things to check:
- Make sure the firewall (if one is being used) is set up to allow the MIME
type of the exported report to go out (PDF, Excel, etc). On most firewalls
you have to set up specific firewall rules to allow this (at least we had
to)
- On the SMTP server, make sure the 'Limit message size to (KB)' checkbox on
the SMTP Virtual Server property sheet Messages tab, is unchecked or specify
a sufficiently large enough value in the textbox. The default is is 2048KB.
If the default is checked and a report is bigger than this, the email will
not go with the attachment.
--
Adrian M.
MCP
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eEI2b4OJFHA.3916@.TK2MSFTNGP14.phx.gbl...
> One thing I was told (but it was more than I wanted to do) was that the
> most
> reliable method is to have the subscription be put in a directory for
> pickup
> by your mail service. Might be something you would want to try.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> news:uAA4RzOJFHA.580@.TK2MSFTNGP15.phx.gbl...
>> This is odd. In my web app I programmatically create subscriptions that
>> include the report as an attachment and it works fine.
>> --
>> Adrian M.
>> MCP
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:uILWefOJFHA.3500@.TK2MSFTNGP14.phx.gbl...
>> >I have seen this similar issue. In my case I can send a link but if I
> send
>> > the report nothing gets sent. I gave up and decided to wait for SP2
> (this
>> > month) and see if that fixes the problem.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Stefan Cremers" <s.cremers@.fritom.com> wrote in message
>> > news:OubUtTOJFHA.732@.TK2MSFTNGP12.phx.gbl...
>> >> Adrian,
>> >>
>> >> Thanks for your help.
>> >>
>> >> I installed the hotfix but nithing chanced. Our problem still exists.
>> >>
>> >>
>> >>
>> >> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
>> >> news:uARmooKJFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> >> > Odd. Try
> http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
>> >> >
>> >> > I've sent posts by MSFT stating that hotfix fixes more than Lotus
> Notes
>> >> > attachment issues.
>> >> >
>> >> > --
>> >> > Adrian M.
>> >> > MCP
>> >> >
>> >>
>> >
>> >
>>
>

NEWSEQUENTIALID sample code

We're currently converting the database of our asset management system to a SQL2005-database. The SQL2000 database uses an INT32 as the primary key with a clustered index on that column on most tables. Needless to say that the current environment is not ideal for replication, so we're investigating the use of GUIDs as primary keys.

We know the usual recommendations as far as GUIDs are concerned (4 times bigger, slower, not good for clustered indexes, yet better for replication). However, on one of the ascend-program training days one of my colleagues talked to one of the instructors and that person suggested that the use and performance of GUIDs is significantely improved in SQL2005 and that they have become the preferred datatype for primary keys if the database is used in replication.

I have tried to find some information about how GUIDs are improved in SQL2005 but I don't find any documents containing anything relevant. The only thing I've found is the documentation of the NEWSEQUENTIALID function which doesn't really tell me much. It just looks like this function improves the performance during an insert if a GUID is used on a clustered index. I have noticed for example that SharePoint only uses GUIDs for most tables; so the performance should be all that bad.

So I was wondering:

1. has the performance of GUIDs improved?

2. are there new suggestions concerning the datatype of the primary key column if the database might be used in a merge replication environment (all sites can do modifications) on SQL2005?

3. what about the performance of NEWSEQUENTIALID? Is there a bigger chance of running into duplicates using NEWSEQUENTIALID?

Regards,

Michael

NEWSEQUENTIALID is derived based on several hashes, one of which is the network card. Since no two network cards are identical, duplicates should not occur. However should you remove your network card, there's a slight chance you can get the same value as another machine whose network card has been removed, but this is highly unlikely.

Since NEWSEQUENTIALID values are incrementing as they're created, clustered index performance should improve as they'll be inserted ascending and in sort order. You also have less page splits, thus less fragmentation.

So, is NEWSEQUENTIALID a good candidate for a primary key? NEWSEQUENTIALID can only be used as a default constraint. that means if this is your primary key, and you have to update it, you can only use NEWID as the new value. Or, you can do delete/insert. If you do a lot of updates to your primary key, you'll be doing a lot of page splits introducing fragmentation.

|||How does the program get the NEWSEQUENTIALID value? In other words, the code has:
sqlCmd = "insert ..."
ExecuteNonQuery
If you have foreign key references, you'll need to retrieve your NEWSEQUENTIALID value and pass it as the foreign key value into the child table(s).|||Just use the OUTPUT clause of the INSERT statement.|||

For a parent child relationship, you can do this:

CREATE TABLE Employee(
EmployeeID uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
EmployeeName nchar(10) NOT NULL
)

insert Employee (EmployeeName)
output Inserted.EmployeeID
values ('Ima Person')

The OUTPUT clause essentially works like a SELECT statement. You can retrieve multiple values with an OUTPUT clause.

Alternatively, you may wish to use this syntax for CREATE TABLE:

CREATE TABLE Employee(
EmployeeID uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
EmployeeName nchar(10) NOT NULL
)