Results 1 to 12 of 12
  1. #1
    Join Date
    May 2004
    Posts
    14

    Unhappy Unanswered: Login's 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!)

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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

  3. #3
    Join Date
    May 2004
    Posts
    14

    Databases already exist

    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?

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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?

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  6. #6
    Join Date
    May 2004
    Posts
    14

    More config info

    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.

  7. #7
    Join Date
    May 2004
    Posts
    14
    Other than dbcreator role, they are standard logins. They each own their own database, so within that db they're dbo.

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  9. #9
    Join Date
    May 2004
    Posts
    14
    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.)

  10. #10
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  11. #11
    Join Date
    May 2004
    Posts
    14

    Talking

    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

  12. #12
    Join Date
    May 2004
    Posts
    14
    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....)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •