SQL Server: how to get a stored procedure to execute tsql from output

Question

I wanted to see if it's possible to create a SQL Server stored procedure to run a select statement, which will generate a list of rows/users as the result set. I then want the result set to be executed. All within the same stored procedure. Is that possible?

Here's the query I'm using:

SELECT 'REVOKE CONNECT FROM [' + name + '];' 
 from sys.sysusers 
 where hasdbaccess =1
 and name not in ('public','dbo','guest','sys')
 and hasdbaccess = 0 --0 = no access, 1 = access
 AND name LIKE 'RMT%'

This query may return 1-15+ rows of revoke connect commands. example: REVOKE CONNECT FROM [<AD>\RMTABC];

I then would want the stored procedure to take that result set / output and execute the command/s.

Answer 1

You can use sp_executesql to execute your queries.

Note the following:

• Ensure you correctly escape object names by using QUOTENAME, not just by enclosing in [].
• Any data which can be parameterized in a normal query can and should be passed through to sp_executesql, see the documentation. (Parameterization is important to avoid SQL injection issues.)
• sys.sysusers is deprecated and for compatibility only. Use sys.database_principals instead.
• The SQL variable should be nvarchar(max), and any variables containing object names should be sysname, alias for nvarchar(128).
• Your hasdbaccess conditions appear contradictory.

DECLARE @sql nvarchar(max);
SELECT @sql = STRING_AGG(
 'REVOKE ' + dp.permission_name + ' FROM ' + QUOTENAME(u.name) + ';',
 '
')
FROM sys.database_principals u
JOIN sys.database_permissions dp ON u.principal_id = dp.grantee_principal_id
WHERE dp.major_id = 0
 AND dp.type = 'CO' -- CONNECT permission
 AND dp.state IN ('G', 'W') -- GRANT
 AND u.name NOT IN ('public', 'dbo', 'guest', 'sys')
 AND u.name LIKE 'RMT%';
EXEC sp_executesql @sql;

• michaw: This helped. Thank you.