Logins permissions for non-sa RESTORE?

I hope this is a nice fat ball that someone can knock out of the park...

We've recently started to upgrade our development servers from Win2k to Windows Server 2003. Naturally, the SQL Server boxes receive MS SQL Server 2000 SP3a. Our database users now cannot restore to their own databases.

We usually grant each login the 'dbcreator' server role (and hope the developers are too busy to realize everything else it allows). Each user's login is dbo in his/her database. Typically one developer will produce a reference dump file, and all the other developers load it as they need it.

If a user executes a restore, it trundles along happily until almost the end, spitting out an error. Here's a sample:

100 percent restored.
Processed 376 pages for database 'bobdb', file 'Data' on file 1.
Server: Msg 916, Level 14, State 1, Line 68
Server user 'bob' is not a valid user in database 'bobdb'.
Server: Msg 3013, Level 16, State 1, Line 68
RESTORE DATABASE is terminating abnormally.

Oh, and this worked with SQL Server 2000 (any patch) on Windows 2000 Server.

Developers reload their databases so often (and DBA resources are so spare) that routing all restores through a DBA will bring development to its knees. So, each developer must be able to restore dumps to their own database.

Help? Which server roles/permissions/GRANTs do we need to provide our developers with the power they need? (and not the powers they don't need!)If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.

[Books Online] RESTORE|||Our DBAs initially set up databases for each developer, so they should be appropriately configured at that point. Each developer login has the dbcreator role. Each developer's login owns the corresponding database.

GRANT CREATE DATABASE only applies to database users, not SQL Server logins. That is to say, the first sentence from the Online Books doesn't exactly make sense. (What user? in which database?)

Essentially, at some point during the restore, SQL Server forgets that the user doing the restore used to own the database in question, and in so doing it gets a bit confused.

If I create my own dump file, I can restore it to my account. Previously, I could restore a dump created by anyone else.

This is the only other clue I have. I executed this immediately after restoring my database from my dump file.

use bobdb
go
exec sp_changedbowner 'bob'
go

Server: Msg 15247, Level 16, State 1, Procedure sp_changedbowner, Line 15
User does not have permission to perform this action.

Previously I could execute this on my own databases (it's a no-op but it doesn't fail). Was there a security hole in sp_changedbowner, until SP3a?

Does that line about "RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles" mean each user must have both server roles, as opposed to either?|||i dont believe that you have to have dual membership
but it should be relevant to try. and see what pops up
i am more concerned about what has changed between sp2 and sp3 to cause this to happen.

Our DBAs initially set up databases for each developer, so they should be appropriately configured at that point. Each developer login has the dbcreator role. Each developer's login owns the corresponding database.
except for dbcreator what are their admin privileges?
do they own the dbs that they work on?|||I might be wrong about this, but it sounds like you're developers are restoring on other servers, perhaps their own pcs? Anyway, you probably just have a SID issue here. What you need to do is have them drop their permissions, then run sp_help_revlogin to sync up the SIDs on the user accounts. That should solve the restore and the sp_changedbowner problem.

Take these two scripts and put them in your master database on the source server.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_hexadecimal Script Date: 5/17/2004 9:46:25 PM ******/
ALTER PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_help_revlogin Script Date: 5/17/2004 9:46:39 PM ******/
ALTER PROCEDURE sp_help_revlogin @.login_name
sysname = NULL
AS
--Declare needed variables.
DECLARE
@.name sysname,
@.xstatus int,
@.binpwd varbinary (256),
@.txtpwd sysname,
@.tmpstr varchar (256),
@.SID_varbinary varbinary(85),
@.SID_string varchar(256),
@.dbname varchar(255)
--Determine whether to process one login or all. Set up cursor accordingly.
IF (@.login_name IS NULL)
BEGIN
DECLARE login_curs CURSOR FOR
SELECT
sxl.sid,
sxl.name,
sxl.xstatus,
sxl.password,
sd.name AS dbname
FROM
master..sysxlogins sxl
INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
WHERE
sxl.srvid IS NULL
AND sxl.name <> 'sa'
END
ELSE
BEGIN
DECLARE login_curs CURSOR FOR
SELECT
sxl.sid,
sxl.name,
sxl.xstatus,
sxl.password,
sd.name AS dbname
FROM
master..sysxlogins sxl
INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
WHERE
sxl.srvid IS NULL
AND sxl.name <> @.login_name
END
OPEN login_curs
FETCH NEXT FROM login_curs
INTO
@.SID_varbinary,
@.name,
@.xstatus,
@.binpwd,
@.dbname
--If no logins found, exit the procedure.
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SELECT @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SELECT @.tmpstr =
'** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE
BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication

IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password

EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
BEGIN
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
END
ELSE
BEGIN
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
END

PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr =
'EXEC master..sp_addlogin ''' + @.name + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE
BEGIN
BEGIN

-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr =
'EXEC master..sp_addlogin ''' + @.name + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
END
IF (@.xstatus & 2048) = 2048
BEGIN
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
PRINT @.tmpstr
END
ELSE
BEGIN
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
--Add the default database.
SET @.tmpstr = 'EXEC master..sp_defaultdb ''' + @.name + ''',''' + @.dbname + ''''
PRINT @.tmpstr
END
FETCH NEXT FROM login_curs
INTO
@.SID_varbinary,
@.name,
@.xstatus,
@.binpwd,
@.dbname
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Run the sp_help_revlogin from the master database. Copy the results of the user they need, and have them run the script on their personal server.s They should then be able to do a restore.|||Thanks for the procedures -- I think we'll make good use of them.

I'm working with two servers at the moment -- A is Win2k-sp3/SQL2k-RTM, while B (the problem child) is Win2k3-RTM+hotfixes/SQL2k-sp3a . The dumps are generally from A, and we're trying to migrate developers to B. However, both will be needed for the forseeable future, as both platform combinations are supported. So, one of them will need to take dumps of the other.

To avoid constantly maintaining this, would it be sufficient to simply create a 'dump master' account on each machine? This account would have the same SID on each machine. We can also try to keep each developer's SID consistent across boxes (if/when they use multiple servers).

I have another server box C, which is Win2k-sp4/SQL2k-sp3a+818 . It also chokes on this dump from server A, and a dump from server B. One odd part is that I can't even restore a dump created from one login's database on server B to a different login's database on server B, without getting these permissions issues.

We've fortunately kept the budget supplying us with enough hardware for good, central database servers. Meanwhile, the DBAs have put enough Fear Of God in the developers (and kept enough control of the install CDs) that developers don't have local database servers. This could be worse, and isn't.|||Other than dbcreator role, they are standard logins. They each own their own database, so within that db they're dbo.|||I would try the single 'dump master' account. This is a lot of authority to give someone though. Have you thought about just setting up a directory and table structure to support this. You can put new dumps into a directory and make a table entry. You have a job that sweeps through every 15 minutes or so and does restores.

My biggest concern with the whole operation would be the fact you don't have SP3a across the board. You are running a server RTM?? That's insane.|||I'm as nervous as you are about that RTM server... but someone didn't write license or support contracts that exclude out of date service levels, so we have to keep an RTM box around. (It's a QA regressions box.)|||It's a freaking free service pack update. Tell your clients to use their brains. That one server can endanger your whole enterprise, unless it's just a standalone box sitting on a shelf with a keyboard and monitor attached to it.|||Many of our customers are ... cautious... about service packs. They're too large to want to apply them. Either the bureaucracy is too thick for those who must approve the work to understand the value. Or, they want uptime on the app (sales guys hate not seeing their commission amounts) and don't want to endanger or delay a payroll source because of an unknown service pack.

Others have been hit with performance issues after applying service packs. (SQL Server 2000, SP1 to SP2)

They also have a habit of going against our wishes by deploying other apps against the same SQL Server instance. This then means they have two (or more!) apps to re-validate after the service pack! More than once the other app vendor "hasn't certified" the latest service pack, so customer (believes it) is stuck.

Besides, if they actually had brains which they could use, they would have deployed our $VERY_LARGE app against Oracle (or even DB2) :^) Sales people seem to believe "one sale is as good as another" ... ughh|||The fun part of the automated restore daemon approach is groking the structure of the dump -- file groups, files, etc. Once the files are known, appropriate MOVE options can be crafted, and a restore can start. (See new thread...)