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.
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 only takes a minute to sign up.Sign up to join this community
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