Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL granting read and write permissions to all tables

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]
like image 692
Sergej Andrejev Avatar asked Jun 23 '10 10:06

Sergej Andrejev


People also ask

How do I grant permission to all tables in SQL?

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.

How do I grant a read only access to all stored procedures in SQL Server?

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.


1 Answers

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.

like image 66
Oded Avatar answered Nov 16 '22 01:11

Oded