above refer to uri
If you want to give your user all read permissions, you could use:
EXEC sp_addrolemember N'db_datareader', N'your-user-name'
That adds the default db_datareader role (read permission on all tables) to that user.
There's also a db_datawriter role - which gives your user all WRITE permissions (INSERT, UPDATE, DELETE) on all tables:
EXEC sp_addrolemember N'db_datawriter', N'your-user-name'
If you need to be more granular, you can use the GRANT command:
GRANT SELECT, INSERT, UPDATE ON dbo.YourTable TO YourUserName
GRANT SELECT, INSERT ON dbo.YourTable2 TO YourUserName
GRANT SELECT, DELETE ON dbo.YourTable3 TO YourUserName
and so forth - you can granularly give SELECT, INSERT, UPDATE, DELETE permission on specific tables.
by me:
If you want to give your user permissions to execute a procedure, you could use:
GRANT EXECUTE ON OBJECT::dbo.your_procedure_name TO N'your-user-name';
below is a full step to create a user db_user, and give him permissions to execute a procedure to a table db_tableABC and a procedureproc_get_price_data; assuming the user's loginName is your_user_login_name
--add a db engine login
IF NOT EXISTS(SELECT name FROM sys.server_principals WHERE name='{your_domain\}your_user_login_name')
CREATE LOGIN [{your_domain\}your_user_login_name] WITH PASSWORD='your_user_password', DEFAULT_DATABASE = TestDB;
--add a user to current database
use TestDB;
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name='db_user')
CREATE USER [db_user] FOR LOGIN [{your_domain\}your_user_login_name];
--grant
GRANT INSERT, UPDATE, SELECT, DELETE ON dbo.db_tableABC TO your_user;
GRANT EXECUTE ON OBJECT::dbo.proc_get_price_data TO your_user;
e.g.
assuming a user named domain123\admin1 can access a database;
IF EXISTS(SELECT name FROM sys.server_principals WHERE name = '[domain123\admin1]')
BEGIN
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'admin1')
BEGIN
CREATE USER [admin1] FOR LOGIN [domain123\admin1];
END
GRANT INSERT, UPDATE, SELECT, DELETE ON dbo.db_tableABC TO admin1;
GRANT EXECUTE ON OBJECT::dbo.GET_PRICE_DATA TO admin1;
END