0

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.

1 Answer 1

4

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;
1
  • This helped. Thank you.
    – michaw
    Mar 1, 2023 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.