INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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:

CODE

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

regards

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

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

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

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

sorry, I forgot about it.

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

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

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

(OP)

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:

CODE -->

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:

CODE -->

USE [base2]
GO

EXECUTE AS USER = 'TEST';
GO

select * from View_1 



I get message:

CODE -->

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

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

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

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

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

CODE

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

And then....

CODE

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

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

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: What should I know before I post?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close