I'm a software developer finishing a web application. The app uses the database "DB1" in my SQL Server 2008 "SQLServer_1".

I would like to create a limited user that the web app will use in its connection string. This users will be allow to execute a list of stored procedures, nothing else. So the user shouldn't be allowed to execute adhoc SQL or other stored procedures out of that list.

I have a little mess between "user" and "login", I don't really know where should I create the new "account".

Which are the steps I should follow?

Thanks in advance.


It all comes down to scope. A login is a server-level account and a user is a database-level account. The user needs to be mapped to a login, so you'll create your login first (while at the same time creating a way for it to authenticate to the server). Next, in your application's database, you'll create the user. When you do this, you'll be given a choice of which login this user represents. You'll pick the login that you previously created. You can then assign permissions to the stored procedures that you want it to be able to execute. Of course, there's a less clickety-clack way of doing this:

create login [your login name here] from windows; --if the login is a Windows account
create login [your login name here] with password 'some super secret password'; --if not a Windwos account
use [DB1];
create user [your login name here] for login [your login name here];
grant execute on [your stored procedure here] to [your login name here];
grant execute on [another stored procedure here] to [your login name here];
| improve this answer | |
  • Thanks, that explanation is exactly what I was looking for. Thanks so much for the SQL lines indeed :) – NullOrEmpty Aug 15 '11 at 13:59

I'm not sure what you mean? If you don't know the differences between "User", "Login" and "Role" then follow the excellent documentation from Microsoft. From there you can reach any other related topic like granting execution rights for stored procedures.

| improve this answer | |
  • In the MSSMS, there are two "Security" folders, one is in the root node, and other is inside your database. You can create a Login in the first, and a User in the second, but I don't know what is what I need. – NullOrEmpty Aug 15 '11 at 11:41
  • That's the global context and the database context. Put it wherever you need it. – mailq Aug 15 '11 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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