Is setting
EXEC sp_addrolemember N'db_datareader', N'User'
EXEC sp_addrolemember N'db_datawriter', N'User'
Is the same as granting SELECT/INSERT/DELETE/UPDATE on all tables to User
GRANT SELECT ON [Schema].[Table] TO [User]
GRANT INSERT ON [Schema].[Table] TO [User]
GRANT DELETE ON [Schema].[Table] TO [User]
GRANT UPDATE ON [Schema].[Table] TO [User]
Launch SQL Server Management Studio and connect with credentials that have been granted the 'sa' role. Expand Security, right-click on Logins and select New Login. Enter a descriptive Login name, select SQL Server authentication, and enter a secure password.
Expand Stored Procedures, right-click the procedure to grant permissions on, and then select Properties. From Stored Procedure Properties, select the Permissions page. To grant permissions to a user, database role, or application role, select Search.
From MSDN (db_datawriter, db_datareader) :
Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
So, the effect is the same, only easier to administer in the first case.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With