3
\$\begingroup\$

I am not very experienced with T-SQL and feel this can be done better. The script changes ownership of all "user" (non-system) databases to sa.

Usage of a dynamic SQL expression seems unavoidable to me. Could I at least iterate the table names in a more elegant way — imperative code looks like a code smell in a SQL script. Nested query in the while loop absolutely hurts my eyes...

How to rewrite this script to be more idiomatic?

DROP TABLE IF EXISTS #databaseNames;

SELECT name
INTO #databaseNames
FROM master.sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

DECLARE @tableCount INTEGER
SELECT @tableCount = COUNT(*) FROM #databaseNames

DECLARE @tableNumber INTEGER
SET @tableNumber = 1

WHILE @tableNumber <= @tableCount
BEGIN

  DECLARE @tableName VARCHAR(1024)
  
  SELECT @tableName = name FROM (
    SELECT
      ROW_NUMBER() OVER (ORDER BY name ASC) AS rowNumber,
      name
    FROM #databaseNames
  ) numberedDatabaseNames
  WHERE rowNumber = @tableNumber

  EXEC('ALTER AUTHORIZATION ON database::' + @tableName + ' to sa;')

  SET @tableNumber = @tableNumber + 1
END;
\$\endgroup\$

2 Answers 2

Reset to default
3
\$\begingroup\$

You could use INFORMATION_SCHEMA.TABLES instead (note that INFORMATION_SCHEMA is found in other ANSI-compliant DBMS and should be the preferred way to explore tables and other structures in a database).

For example:

SELECT *
from INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG NOT IN ('master', 'tempdb', 'model', 'msdb')
AND TABLE_TYPE = 'BASE TABLE'

Then you can fetch DB name (TABLE_CATALOG) and table name at the same time, and run your loop.

Then you can simply use a CURSOR but a classic WHILE loop is doable too. But you'll still need some dynamic SQL, because you can't use variables for object names. But your script can be simplified.

Edit

Say you are only interested in changing ownership of databases, and not tables, here is some sample code:

DECLARE @db_name varchar(100)
    
Declare Cur_databases Cursor 
For 
    SELECT name
    --INTO #databaseNames
    FROM master.sys.databases
    WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
For Read only

OPEN Cur_databases
FETCH NEXT FROM Cur_databases 
INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN
    print 'ALTER AUTHORIZATION ON database::' + @db_name + ' to sa;'
    --  EXEC('ALTER AUTHORIZATION ON database::' + @tableName + ' to sa;')

    FETCH NEXT FROM Cur_databases 
    INTO @db_name

END

CLOSE Cur_databases
DEALLOCATE Cur_databases
\$\endgroup\$
3
  • \$\begingroup\$ Could you please provide specific code to illustrate the simplified version? \$\endgroup\$ Commented Jun 19, 2021 at 5:11
  • \$\begingroup\$ I edited my post to add some code with a cursor. I did not bother to use a temp table to store results because it's probably unlikely that the list of databases would change during execution of this script. \$\endgroup\$ Commented Jun 20, 2021 at 18:50
  • \$\begingroup\$ Thank you! Definitely better than my variant. At least, in terms of the number of requests made. Like it! The WHILE @@FETCH_STATUS = 0 hurts my eyes, but I guess I should get used to SQL's idioms... \$\endgroup\$ Commented Jun 21, 2021 at 7:26
1
\$\begingroup\$

The current query would get all databases except system databases, ignoring the current owners. What I prefer is to include the owner in the filter as well, so it'll always have the databases with different owner than sa.

here is a suggested revision :

SELECT db.name
FROM sys.databases db
LEFT JOIN sys.server_principals sp ON sp.sid = db.owner_sid
WHERE 
    db.name NOT IN ('master', 'tempdb', 'model', 'msdb')
AND sp.name <> 'sa'

Now, you can use that to make it more reusable, such as having a procedure that would change the database owner to any given owner, or excluding some owners from the queries ..etc.

narrowing your queries would avoid bugs and future changes hassles.

If you're looking for a dynamic sql and avoid using Cursor, you can do this :

DECLARE 
    @sql    NVARCHAR(MAX)
,   @owner  NVARCHAR(100) = 'sa'


SELECT @sql = COALESCE(@sql + 'ALTER AUTHORIZATION ON database::' + db.name + ' to ' + @owner + '; ', '')
FROM sys.databases db
LEFT JOIN sys.server_principals s ON s.sid = db.owner_sid
WHERE 
    db.name NOT IN ('master', 'tempdb', 'model', 'msdb')
AND s.name <> @owner

EXEC sp_executesql @sql
\$\endgroup\$

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.