0

My question is similar to Rights to execute stored procedure, but not read underlying tables directly, however my storedprocedure reads from different databases.

I'm trying to read databases matching some naming pattern, to which the user has access. Everything works per partes, however executing the stored procedure by the dedicated user returns nothing. I believe it's because he doesn't have rights to access the databases (which is correct, a prerequisity). I somehow expected the stored procedure would be able to access them (like in the other thread mentioned above, which I read after that), even though the user can't.

Stored Procedure:

ALTER PROCEDURE [dbo].[GetUsersDatabases]
    @UserName varchar(15)
AS
BEGIN
    SET NOCOUNT ON;

    DROP TABLE IF EXISTS [#TMP]
    CREATE TABLE [dbo].[#TMP] ([DBNAME] NVARCHAR(256) NULL, [NAME] SYSNAME NOT NULL);
    DECLARE @command varchar(1000) 

    SELECT @command = 'IF ''?'' LIKE ''Pr_%'' 
        BEGIN 
            USE ? 
            INSERT INTO #tmp 
            SELECT ''?'' as DBname, [name] as Uname 
            FROM  sys.database_principals 
            WHERE [TYPE] NOT IN (''A'', ''G'', ''R'', ''X'') 
                AND sid IS NOT NULL 
                AND [name] = ''' + @UserName + ''' 
        END' 

    EXEC sp_MSforeachdb @command 

    SELECT * FROM #tmp;
END

Now I created login and user this way:

USE [master]
CREATE LOGIN [ListUsersDBs] WITH PASSWORD=N'secretpwd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE MyDefaultDB
CREATE USER ListUsersDBs FOR LOGIN ListUsersDBs
GO
GRANT EXECUTE ON GetUsersDatabases to ListUsersDBs   

It all works, if I login as myself and execute

exec dbo.GetUsersDatabases @UserName = 'MyUserName'

If I log in as the ListUsersDBs, then I get 0 rows with the very same code. The user can access the stored procedure, but it returns no rows for the same input.

I think it can be set properly, but I don't know what I'm missing.

5
  • 1
    You've create a login and associated user in the master database. Does that user exist in any other database where it has rights to connect to the database and view metadata? Feb 23, 2023 at 15:57
  • @SeanGallardy No, it's on purpose and that is the point of this whole procedure. The user cannot have access anywhere else but to the stored procedure. Also not to the master, that was not intentional. I'll try to recreate the user in the database, where i s the procedure stored. Feb 24, 2023 at 6:58
  • If the there are no users in other databases for the login, how do you expect this to authenticate and connect to other databases? Feb 24, 2023 at 11:31
  • I probably misunderstood the post above. So does that mean, that I have to create a stored procedure and grant user in every affected database and then sp_MSforEachDB all of them to get it working? Feb 24, 2023 at 14:07
  • Yes, there are other options available such as module signing, but at the end of the day even that needs a user in each database which can authenticate and connect along with the permissions needed for the queries used. Feb 24, 2023 at 14:18

0

Your Answer

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