Home / server / Questions / 301160
Accepted
NullOrEmpty
Asked: 2011-08-16 01:45:47 +0800 CST

Configure user login and stored procedures permissions on SQL Server

  • 772

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.

sql-server sql-server-2008 security login stored-procedures

2 Answers

  1. Best Answer

    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];
...
    • 2

  2. 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.

    • 0