Hi,
I am trying to run a set up script on SQL Server 2005 on w 2003 which
uses AD for authentication.
I have an idea that the error is caused by AD cos the script is know
to work when AD is not used.
Can anyone advise, please?
It is kind of urgent!

TIA


PRINT 'Adding user reporter with password reporter'
EXEC sp_adduser 'reporter', 'XXXXXX'
PRINT 'Granting reporter database access.' <----------------
EXEC sp_grantdbaccess N'reporter', N'reporter'

Adding user reporter with password reporter
Granting reporter database access. <----------------
Msg 15063, Level 16, State 1, Line 1
The login already has an account under a different user name.
Adding database owner role to user reporter.


//set up script
/*
** Set up repository database, create repository user, AND associate
user with
** database.
*/
SETUSER
GO
USE master
GO

IF NOT EXISTS( SELECT * FROM master..sysdatabases WHERE name =
'REPORTER' )
BEGIN
PRINT 'Creating database REPORTER'
CREATE DATABASE REPORTER
EXEC sp_dboption N'REPORTER', N'autoclose', N'false'
EXEC sp_dboption N'REPORTER', N'bulkcopy', N'false'
EXEC sp_dboption N'REPORTER', N'trunc. log', N'false'
EXEC sp_dboption N'REPORTER', N'torn page detection', N'true'
EXEC sp_dboption N'REPORTER', N'read only', N'false'
EXEC sp_dboption N'REPORTER', N'dbo use', N'false'
EXEC sp_dboption N'REPORTER', N'single', N'false'
EXEC sp_dboption N'REPORTER', N'autoshrink', N'false'
EXEC sp_dboption N'REPORTER', N'ANSI null default', N'false'
EXEC sp_dboption N'REPORTER', N'recursive triggers', N'false'
EXEC sp_dboption N'REPORTER', N'ANSI nulls', N'false'
EXEC sp_dboption N'REPORTER', N'concat null yields null', N'false'
EXEC sp_dboption N'REPORTER', N'cursor close on commit', N'false'
EXEC sp_dboption N'REPORTER', N'default to local cursor', N'false'
EXEC sp_dboption N'REPORTER', N'quoted identifier', N'false'
EXEC sp_dboption N'REPORTER', N'ANSI warnings', N'false'
EXEC sp_dboption N'REPORTER', N'auto create statistics', N'true'
EXEC sp_dboption N'REPORTER', N'auto update statistics', N'true'
END
GO

IF NOT EXISTS (SELECT * FROM master..syslogins WHERE name =
N'reporter')
BEGIN
PRINT 'Adding login REPORTER'
EXEC sp_addlogin 'reporter', 'XXXXXX', 'REPORTER'
END

GO

PRINT 'Changing to database REPORTER'
GO

USE REPORTER
GO

IF NOT EXISTS( SELECT * FROM .sysusers WHERE name = N'reporter' )
BEGIN
PRINT 'Adding user reporter with password reporter'
EXEC sp_adduser 'reporter', 'XXXXXX'

PRINT 'Granting reporter database access.'
EXEC sp_grantdbaccess N'reporter', N'reporter' <---------------

(tbarratt (AT) mibtree (DOT) com) writes:
sp_adduser and sp_grantdbaccess are two system procedures for the same
thing. If you try to add a user a second time, you get an error. It is
as simple as that.

Now, since you are using SQL 2005, you should not use these commands
at all, but instead use CREATE USER. (And likewise use CREATE LOGIN
to create logins.)

You should also replace all your calls to sp_dboption with ALTER DATABASE
commands.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 14 Jan, 22:23, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
I take your point about using more modern syntax - interesting.
For the moment the pressure is on to just get it working!
Please help further if you can, cos once this is working, I can move
back into my comfort zone!
I have deleted the two lines:
PRINT 'Granting reporter database access.'
EXEC sp_grantdbaccess N'reporter', N'reporter

Now I am getting error's further down.
I have added some <------'s to clarify


Creating database REPORTER
Adding login REPORTER
Changing to database REPORTER
Adding user reporter with password reporter
Adding database owner role to user reporter.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 80
<-------
User or role 'reporter' does not exist in this database.
Aliasing to user reporter
Msg 15157, Level 16, State 1, Line 2 <----------
Setuser failed because of one of the following reasons: the database
principal 'reporter' does not exist, its corresponding server
principal does not have server access, this type of database principal
cannot be impersonated, or you do not have permission.
/*
** Set up repository database, create repository user, AND associate
user with
** database.
*/
SETUSER
GO
USE master
GO

IF NOT EXISTS( SELECT * FROM master..sysdatabases WHERE name =
'REPORTER' )
BEGIN
PRINT 'Creating database REPORTER'
CREATE DATABASE REPORTER
EXEC sp_dboption N'REPORTER', N'autoclose', N'false'
EXEC sp_dboption N'REPORTER', N'bulkcopy', N'false'
EXEC sp_dboption N'REPORTER', N'trunc. log', N'false'
EXEC sp_dboption N'REPORTER', N'torn page detection', N'true'
EXEC sp_dboption N'REPORTER', N'read only', N'false'
EXEC sp_dboption N'REPORTER', N'dbo use', N'false'
EXEC sp_dboption N'REPORTER', N'single', N'false'
EXEC sp_dboption N'REPORTER', N'autoshrink', N'false'
EXEC sp_dboption N'REPORTER', N'ANSI null default', N'false'
EXEC sp_dboption N'REPORTER', N'recursive triggers', N'false'
EXEC sp_dboption N'REPORTER', N'ANSI nulls', N'false'
EXEC sp_dboption N'REPORTER', N'concat null yields null', N'false'
EXEC sp_dboption N'REPORTER', N'cursor close on commit', N'false'
EXEC sp_dboption N'REPORTER', N'default to local cursor', N'false'
EXEC sp_dboption N'REPORTER', N'quoted identifier', N'false'
EXEC sp_dboption N'REPORTER', N'ANSI warnings', N'false'
EXEC sp_dboption N'REPORTER', N'auto create statistics', N'true'
EXEC sp_dboption N'REPORTER', N'auto update statistics', N'true'
END
GO

IF NOT EXISTS (SELECT * FROM master..syslogins WHERE name =
N'reporter')
BEGIN
PRINT 'Adding login REPORTER'
EXEC sp_addlogin 'reporter', 'XXXXXX', 'REPORTER'
END
GO

PRINT 'Changing to database REPORTER'
GO

USE REPORTER
GO

IF NOT EXISTS( SELECT * FROM .sysusers WHERE name = N'reporter' )
BEGIN
PRINT 'Adding user reporter with password reporter'
EXEC sp_adduser 'reporter', 'XXXXXX'

/****** Object: User reporter Script Date: 11/05/2000 15:44:15
******/
PRINT 'Adding database owner role to user reporter.'

EXEC sp_addrolemember N'db_owner', N'reporter' <-------
END
GO
'
PRINT 'Aliasing to user reporter'
GO

SETUSER 'reporter' <---------
GO

(tbarratt (AT) mibtree (DOT) com) writes:
That's another. SETUSER has been deprecated. Use EXECUTE AS USER/
LOGIN instead.


You are adding the login reporter to the database, and give the username
XXXXXX. Then you should use that username across the board.

I recommand that you use the new syntax instead. You might create a
mess this way.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 15 Jan, 22:59, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Don't know if this will work for you, but the following worked for me. I was
running my CREATE USER script in 2005 and got this error via my AD account
that had sysadmin privs. I tried all sorts of things, and just before I threw
in the towel, I decided to run the script as the actual SA account--for some
reason the script ran perfectly--go figure. I'm still not convince of the
issue though--I'm going to have to see this more than once to be a believer
that SA fixes the issue.

Do replace deprecated sp_adduser and sp_grantdbaccess with CREATE USER. The
bummer is that you cannot substitute variables with CREATE USER but need to
create an execution string and run via EXEC or sp_executesql.

"tbarratt (AT) mibtree (DOT) com" wrote:

p.s. make sure the user you're trying to add isn't assigned to the actual dbo.

"Max" wrote: