Link to home
Start Free TrialLog in
Avatar of George
GeorgeFlag for Canada

asked on

Creating SQL Server Logins from application interface

Hi Everyone,

We have a vb.net 2005 application with a SQL Server 2005 Backend
SQL Authentication is used to authenticate users.

We need to change the application to allow application administrator's to create users using the front end interface.

We have a store procedure that creates the logins, no problem there, the problem is that the account that the application uses needs to be on the  Server role 'securityadmin" otherwise, will not run.

There is a concern that the application account has elevated privileges on the server and can access other DBs.

Is there a way to create logins but restrict the user that the application uses to create the login to only one database?

Any idea is very welcome

Jorge
Avatar of brejk
brejk
Flag of Poland image

Try to use EXECUTE AS clause in your stored procedure definition to execute the procedure in a context of SecurityAdmins member.
Oh, and btw, having a login does not guarantee database access. You have to create a user in a database and grant some permissions to this user.
Avatar of George

ASKER

Hi brejk,

Thank you for your comment,
In that case I thing that the SP needs to have user name and password to do the impersonation...Im I right?
Then there will be a breach to other databases... that is what we would like to prevent.

ASKER CERTIFIED SOLUTION
Avatar of brejk
brejk
Flag of Poland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of George

ASKER

Brejk ,Thank you very much for your solution.

Yes, that was the solution I was looking for, the key here is that the account that runs the SP does not have any other permmision than execute, so the passsword used for impersonation will not be exposed.

That gives you an A!

Thanks again

Jorge