After the installation, the databases were owner by my login, and I would like to have the owner changed to "sa". I my case it is renamed, but that is another story.
Renaming a database in SQL Server Management Studio is done on the properties of the database in the page "Files".
When I ask for the change script by clicking the Script drop-down, I get a script that uses the stored procedure "sp_changedbowner".
The article on this procedure in Books Online (MSDN Library) notes that this feature will be removed, and that I should use ALTER AUTHORIZATION instead.
Then I rewrote the statement to use the recommendation
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sql_sa];
Please recall that I have renamed "sa".
The samples generates several databases. Six in my case.
Instead of generating a script for each database, I made a script to generate these scripts
SELECT N'ALTER AUTHORIZATION ON DATABASE::[' + [databases].[name] + N'] TO [sql_sa];' AS [DCL]
FROM [master].[sys].[databases]
WHERE [databases].[owner_sid] != (
SELECT [server_principals].[sid]
FROM [master].[sys].[server_principals]
WHERE [server_principals].[name] = N'sql_sa'
);
I could have generated the scripts for the first principal, as "sa" always is the first, but maybe another day I would like to use another principal.
The generated scripts are like this
The generated scripts are like this
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks2008R2] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksDW2008R2] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksLT2008R2] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksDW] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksLT] TO [sql_sa];
No comments:
Post a Comment