Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Set the owner of the database after restore #2081

Closed
gdoddsy opened this issue Aug 11, 2019 · 7 comments

Comments

@gdoddsy
Copy link
Contributor

commented Aug 11, 2019

Is your feature request related to a problem? Please describe.
We always want to set the database owner after a restore, so we'd like to specify the owner as part of the restore script.

Describe the solution you'd like
Provide an extra parameter to sp_DatabaseRestore that sends in the desired owner username. After restoring the database, validate that it's actually a user and then set the owner of the restored database to that user.

Describe alternatives you've considered
We currently run this as an additional step in the job, but for manual restores it's always forgotten until weeks (months) later.

Are you ready to build the code for the feature?
I've pretty much got this going already, just need to add in some checking that the username passed in is an actual user.

@BrentOzar

This comment has been minimized.

Copy link
Member

commented Aug 11, 2019

Makes sense! I like it. Go for it!

@gdoddsy

This comment has been minimized.

Copy link
Contributor Author

commented Aug 11, 2019

@BrentOzar - do you have a suggestion for how to validate the user exists and what to do if they don't?

I'm currently thinking something like:


IF @DatabaseOwner IS NOT NULL
	BEGIN
		IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE syslogins.loginname = @DatabaseOwner)
		BEGIN
			SET @sql = N'ALTER AUTHORIZATION ON DATABASE::[' + @RestoreDatabaseName + '] TO [' + @databaseowner + ']';

				IF @Debug = 1 OR @Execute = 'N'
				BEGIN
					IF @sql IS NULL PRINT '@sql is NULL for Set Database Owner';
					PRINT @sql;
				END;

			IF @Debug IN (0, 1) AND @Execute = 'Y'
				EXECUTE (@sql);
		END
		ELSE
		BEGIN
			PRINT @DatabaseOwner ' is not a valid Login. Database Owner not set.'
		END
	END;

@BrentOzar

This comment has been minimized.

Copy link
Member

commented Aug 11, 2019

No, no opinion here (out on vacation this month) - but other folks might have an opinion, will let them chime in.

@gdoddsy

This comment has been minimized.

Copy link
Contributor Author

commented Aug 11, 2019

Pull request sent through.

@BrentOzar BrentOzar added this to the 2019-08 Release milestone Aug 18, 2019

@BrentOzar

This comment has been minimized.

Copy link
Member

commented Aug 18, 2019

Good job! I've merged it into the dev branch, and it'll be in the August release, with credit to you in the release notes. Thanks!

@BrentOzar BrentOzar closed this Aug 18, 2019

@sm8680

This comment has been minimized.

Copy link
Contributor

commented Aug 27, 2019

Just an FYI and maybe this is just me. But when I ran the latest update in SQL Server Data Tools
@DatabaseOwner SYSNAME = NULL,
Errors comes up with unresolved reference type to dbo.SYSNAME
changing it to
@DatabaseOwner sysname = NULL,
where sysname is lower case resolves the issue at hand for case sensitive collations.

@BrentOzar

This comment has been minimized.

Copy link
Member

commented Aug 27, 2019

@sm8680 OK, this is a new issue - can you file a new issue? Thanks. Brent

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.