Cannot log into a SQL Server 2005 database

Hello:
I just installed SQL Server 2005. I was trying to set up the database to
support IBM's RequisitePro.
RequisitePro requires the creation of a database, RequisitePro and 4
scripts to be executed.
After creating the databse, the first step executes the following SQL:
/* Microsoft SQL Server - Scripting */
/* Database: ReqPro */
/* Creation Date 11/2/98 09:50:13 */
/* Version: 4.02i */
/* Notes: Creates ReqPro default Login and User. */
/* This script is optional; if you have established your */
/* own login and user scheme, you need not run this. */
sp_addlogin 'ReqPro', 'reqpro', 'RequisitePro', default
Go
sp_adduser 'ReqPro', 'ReqPro', 'public'
Go
Grant Create Default, Create Procedure, Create Rule, Create Table, Create
View To ReqPro
Go
After executing the script, I tried to log in as ReqPro but got an error
message:
Cannot connect to venkat-wxp2.
Additional information:
Cannot open user default database; login failed.
Login failed for user, 'ReqPro'.
This script works on a SQL Server 2000 without any problem.
Can somebody tell me what I have to change?
Thanks.
VenkiI see one thing different on 2005 as opposed to 2000. On reviewing the user
mapping for the login, ReqPro, I see that it has access only to Master
databse but not the database, RequistePro even though that database has bee
n
designated as its default database.
How can I alter the script so that it is given that access privilege?
Thanks.
venki
"vvenk" wrote:

> Hello:
> I just installed SQL Server 2005. I was trying to set up the database to
> support IBM's RequisitePro.
> RequisitePro requires the creation of a database, RequisitePro and 4
> scripts to be executed.
> After creating the databse, the first step executes the following SQL:
> /* Microsoft SQL Server - Scripting */
> /* Database: ReqPro */
> /* Creation Date 11/2/98 09:50:13 */
> /* Version: 4.02i */
> /* Notes: Creates ReqPro default Login and User. */
> /* This script is optional; if you have established your */
> /* own login and user scheme, you need not run this. */
> sp_addlogin 'ReqPro', 'reqpro', 'RequisitePro', default
> Go
> sp_adduser 'ReqPro', 'ReqPro', 'public'
> Go
> Grant Create Default, Create Procedure, Create Rule, Create Table, Create
> View To ReqPro
> Go
> After executing the script, I tried to log in as ReqPro but got an error
> message:
> Cannot connect to venkat-wxp2.
> Additional information:
> Cannot open user default database; login failed.
> Login failed for user, 'ReqPro'.
> This script works on a SQL Server 2000 without any problem.
> Can somebody tell me what I have to change?
> Thanks.
> Venki|||Change context to your new database and issue an sp_adduser.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:3DAEB6C6-C708-4DD9-AD04-82E2CC76FA2B@.microsoft.com...[vbcol=seagreen]
>I see one thing different on 2005 as opposed to 2000. On reviewing the user
> mapping for the login, ReqPro, I see that it has access only to Master
> databse but not the database, RequistePro even though that database has
> been
> designated as its default database.
> How can I alter the script so that it is given that access privilege?
> Thanks.
> venki
> "vvenk" wrote:
>|||Michael:
I am trying to understand the differences between 2000 and 2005.
I did not have to change the context to the new database when I run the
script against 2000. But I see that I have to, when running the script
against 2005.
Is this a change?
"Michael Hotek" wrote:

> Change context to your new database and issue an sp_adduser.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "vvenk" <vvenk@.discussions.microsoft.com> wrote in message
> news:3DAEB6C6-C708-4DD9-AD04-82E2CC76FA2B@.microsoft.com...
>
>|||yes
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:C6FEF0A4-9A7F-41FD-8379-A9A0452960CF@.microsoft.com...[vbcol=seagreen]
> Michael:
> I am trying to understand the differences between 2000 and 2005.
> I did not have to change the context to the new database when I run the
> script against 2000. But I see that I have to, when running the script
> against 2005.
> Is this a change?
> "Michael Hotek" wrote:
>|||Understanding security is going to be the single biggest issue with 2005. I
would very strongly recommend spending several days with the security topics
in BOL and testing many of them to understand what they do. An
administrator can actually lock themselves out of their own database server
with zero possibility of getting back into it. Security isn't hard, it just
has a LOT of components to it.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:C6FEF0A4-9A7F-41FD-8379-A9A0452960CF@.microsoft.com...[vbcol=seagreen]
> Michael:
> I am trying to understand the differences between 2000 and 2005.
> I did not have to change the context to the new database when I run the
> script against 2000. But I see that I have to, when running the script
> against 2005.
> Is this a change?
> "Michael Hotek" wrote:
>|||Hi Venki
I think maybe you might have just been 'lucky' in SQL 2000. Your script
creates a login and then in the same database context it creates a user.
What database were you in when the sp_addlogin and sp_adduser were created?
Perhaps the user running the script has a default db of the correct database
in SQL 2000, but not in SQL 2005.
There is NO difference between SQL 2000 and 2005 in this regard. Whatever
database you are in when you run sp_adduser is the database that the new
user will have access to.
So you either have to issue a USE, as Mike suggested, or you have to make
sure that whoever runs this script will start in the right database.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:C6FEF0A4-9A7F-41FD-8379-A9A0452960CF@.microsoft.com...
> Michael:
> I am trying to understand the differences between 2000 and 2005.
> I did not have to change the context to the new database when I run the
> script against 2000. But I see that I have to, when running the script
> against 2005.
> Is this a change?
> "Michael Hotek" wrote:
>
>|||> Is this a change?
No. To add to Mike and Kalen's comments, perhaps the login gained access to
the new database in the SQL 2000 instance via a server role membership like
'sysadmin' but this is not the case in the 2005 instance. Other than that,
I would not expect the login/user to have access to the RequisitePro
database unless the database context was set to the RequisitePro database
before executing the script.
Hope this helps.
Dan Guzman
SQL Server MVP
"vvenk" <vvenk@.discussions.microsoft.com> wrote in message
news:C6FEF0A4-9A7F-41FD-8379-A9A0452960CF@.microsoft.com...[vbcol=seagreen]
> Michael:
> I am trying to understand the differences between 2000 and 2005.
> I did not have to change the context to the new database when I run the
> script against 2000. But I see that I have to, when running the script
> against 2005.
> Is this a change?
> "Michael Hotek" wrote:
>|||Hi Mike
Although security is one of the biggest areas of change in SQL 2005 that
will cause a lot of confusion, it's not clear what specific change you're
referring to here.
Can you elaborate?
Thanks
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:e35OwGJEGHA.3052@.TK2MSFTNGP10.phx.gbl...
> yes
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "vvenk" <vvenk@.discussions.microsoft.com> wrote in message
> news:C6FEF0A4-9A7F-41FD-8379-A9A0452960CF@.microsoft.com...
>
>