Search code examples
sql-serverviewcatalog

sys.database_principals is not executed in current database if called from sp procedure (stored in master)

I would like to print out database users of an actual database in a SP procedure (see the code of sp_PrintUsers below), however, for some reason it print out database users of master. It seems that it is a general behavior of SP procedure for all database-level views despite the fact that any database-level SQL statement is executed in the actual database. If we print out the DB_NAME that it is clearly not master, so what is wrong?

Is there any workaround?

use [master]
go
create procedure sp_PrintUsers
as
begin
    SELECT DB_NAME() AS DataBaseName
    select name from sys.database_principals;
end
go

use [actual_database]
go

exec sp_PrintUsers
Solution

  • Try executing the select dynamically as in:

    EXEC('select name from sys.database_principals;');

    If that does not help build the query to reference the catalog view with a three part name.