Sql-server – Creating a User to access multiple databases

Securitysql server

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.

Best Answer

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
Related Question