[sqlserver2008] permission to read data from another database system tables

[pit977]

Hello,

As a topic, I have 2 databases (eg. base1; base2) with the same users, could You help me how to grant permissions to a user, that he would be able to read data from another database system tables, I mean tables:

sys.database_principals
sys.database_role_members

and then use it in a query (eg, in the Where clause)

now I have message:

The server principal "XXXX" is not able to access the database "base1" under the current security context.

regards

 

[gmmastros]

Are both databases attached to the same SQL Server instance?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pit977]

yes, both databases are attached to the same SQL Server instance.

sorry, I forgot about it.

 

[gmmastros]

Can you show the query you are trying to run?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pit977]

here are some details:

I have 2 databases: base1 and base2. Base1 has some roles with memmbers. In base2 I have following view named View_1:

SELECT     [base1].dbo.PERM_CATEGORY.PERMIS
FROM         (SELECT     Roles_1.Role_Name AS dbrole, Principals.name AS MemberName, Principals.sid AS memberSID
                       FROM          (SELECT     Principals.name AS Role_Name, Roles.role_principal_id AS Role_ID, Roles.member_principal_id AS Member_ID
                                               FROM          [base1].sys.database_principals AS Principals INNER JOIN
                                                                      [base1].sys.database_role_members AS Roles ON Principals.principal_id = Roles.role_principal_id) AS Roles_1 INNER JOIN
                                              [base1].sys.database_principals AS Principals ON Roles_1.Member_ID = Principals.principal_id) AS dt RIGHT OUTER JOIN
                      [base1].dbo.PERM_CATEGORY ON dt.dbrole = [base1].dbo.PERM_CATEGORY.ROLA
WHERE     (dt.MemberName = SUSER_SNAME())

and when try to run:

USE [base2]
GO

EXECUTE AS USER = 'TEST';
GO

select * from View_1

I get message:

The server principal "TEST" is not able to access the database "base1" under the current security context.

 

[gmmastros]

Hmmm..... this is not my area of expertise, but I would recommend you read this:

http://msdn.microsoft.com/en-us/library/ms178640.aspx

I think you need to grant permissions to impersonate. Something like...

use master
go
Grant Impersonate on login::[Login to impersonate] to [login you are running as];

And then....

USE [base2]
GO

EXECUTE AS LOGIN = 'TEST';
GO

select * from View_1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

Start with eliminating the obvious. Does Test have permissions to access and read data on BASE1? Confirm this by logging on as Test and running the select statement right on BASE1. If that fails, you know the issue is with Test's access on BASE1.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875