I have an idea to create database user (account with username and password that can access MS SQL database with those credentials) in a SQL stored procedure, and use those credentials in my web application, when user logs in with that account, to connect and interact with database.
This way I will be able to set permissions for each account and then it won’t matter if that user manages to somehow get in touch with my database without my web app, as he will not be able to make changes that he couldn’t do through my web app.
In short -> security is directly in my database, not in my web app.
As you can probably tell, I’m not well versed in this type of stuff, I bet that this is already a standard way of doing security, or there’s a better way.
My question is:
Can I create a DB user in MS SQL using one of it’s stored procedures?
If possible, how would I go about for example changing it’s password?
Or if I want to add some more information other than just username and password, I would create a table with those new colums (for example first and last name, nickname, phone number, etc), how would I go about connecting a DB user with my table?
(I imagine it would be something like ‘usernames for DB users are unique, so just save the username’).
As you can see I have a broad idea, and it’s still a bit messy in my head, and was wondering if someone could point me in the right direction with my train of thought.
Advertisement
Answer
You can create a Login / User using
USE MyDatabase CREATE LOGIN MyLogin WITH PASSWORD = 'MyStrongPassword' CREATE USER MyUser FOR LOGIN MyLogin
To change a password
ALTER LOGIN MyLogin WITH PASSWORD = 'MyNewStrongerPassword'
To grant the user access to a table within the database:
GRANT SELECT ON MyTable TO MyUser
You can indeed do this directly within a stored procedure, though I’m not sure why you’d want to – surely this would be a one off operation by a database administrator, rather than something that would require the re-usability a stored procedure gives?