Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible in SQL server to create a User without a Login that can access multiple databases?

I am creating a user setting up a service broker queue and the receiver of the message needs to be able to collect data from several databases.

share|improve this question

1 Answer 1

I'm not certain about the service broker que but it is possible to setup a user without a login. This user will not be able to authenticate but you can still assign the user rights. The examples below are from the microsoft article for createing users http://msdn.microsoft.com/en-us/library/ms173463.aspx

D. Creating and using a user without a login

The following example creates a database user CustomApp that does not map to a SQL Server login. The example then grants a user adventure-works\tengiz0 permission to impersonate the CustomApp user.

USE AdventureWorks2012 ;
CREATE USER CustomApp WITHOUT LOGIN ;
GRANT IMPERSONATE ON USER::CustomApp TO [adventure-works\tengiz0] ;
GO 

To use the CustomApp credentials, the user adventure-works\tengiz0 executes the following statement.

EXECUTE AS USER = 'CustomApp' ;
GO

To revert back to the adventure-works\tengiz0 credentials, the user executes the following statement.

REVERT ;
GO
share|improve this answer
    
Hmm.. I need it to be the other way round. The sp invoked to receive the data at the end of the queue is the WITHOUT LOGIN. It would need to impersonate the User who would have access to the other database. –  Mongus Pong Sep 18 '12 at 16:03
    
I see. So could you grant impersonate to the user at the end of the queue then? Or does it not allow that? –  Lumpy Sep 18 '12 at 16:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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