I was investigating and I have a question. I know that to create a session is:
CREATE LOGIN user WITH PASSWORD = 'pass';
And my question is: How do I give read and write privileges and assign a table to that user with SQL code?
I was investigating and I have a question. I know that to create a session is:
CREATE LOGIN user WITH PASSWORD = 'pass';
And my question is: How do I give read and write privileges and assign a table to that user with SQL code?
in SQL Server 2012 or higher, if you want to assign a server level role , for example sysadmin , which gives you administrator privileges, you can do it with:
create login [MiUsuario] with password = 'MiPassword';
alter server role [sysadmin] add member [MiUsuario];
If you want to assign a role to it in a database, create the user for the DB and then assign it the permission, for example with db_datareader you give it read permissions:
use MiBase;
create user [MiUsuario] for login [MiUsuario];
alter role [db_datareader] add member [MiUsuario];
Finally, you can give it permissions at an object level, for example, tables, using the grant :
grant select on MiTabla to [MiUsuario];
grant execute on MiStoredProc to [MiUsuario];