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.

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?

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
+1. Never thought about using access this way before. –  StanleyJohns Sep 1 '11 at 19:35
    
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. –  Shawn Melton Sep 1 '11 at 20:06
add comment

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.