2

In SQL server 2008, I have one database that is a common database that is accessed thru stored procedures in all other databases. Each of the other databases have their own login.

I need a way that all logged in users can access that common database. I know I could create a user for the common database for each login but I would need to create over 100 users in the database then.

Is there a better way?

Improve this question

1 Answer 1

Reset to default
3

I believe you can create a user account in that database, using WITHOUT LOGIN. This will create it where you can grant execute as permissions to the other users for any particular stored procedure in that common database. They don't have to use a password for it so you control it by who can EXECUTE AS with it. Here is a pretty good write-up on how it could be used, near the end he talks on how it can be used outside the context of the database.

Improve this answer
1
  • I didn't either until earlier this year. Saw it being used in a instance I support and got to looking more on it. Found a few folks that use it for testing application access and security roles. Since you don't have to create a login first makes it much easier to create/drop when you need to.
    – user507
    Commented Sep 1, 2011 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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