How to Grant Database Privileges in SQL Server 2008?

Granting Database Privileges in SQL Server 2008

To establish an ODBC connection using SQL Server authentication and grant a user comprehensive database permissions, follow the steps outlined below:

Granting All Read Permissions:

EXEC sp_addrolemember N'db_datareader', N'your-user-name'

This command grants the user all read permissions on all tables within the database.

Granting All Write Permissions:

EXEC sp_addrolemember N'db_datawriter', N'your-user-name'

This command grants the user all write permissions (INSERT, UPDATE, DELETE) on all tables within the database.

Granting Granular Permissions:

If desired, you can grant specific permissions on individual tables using 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

Graphical Method:

To grant permissions graphically in SSMS:

1. Navigate to the database in question.
2. Click on Security > Users.
3. Right-click on the user and select Properties.
4. Scroll down to "Database role memberships" and add the user to the desired role(s).

Refer to MSDN Books Online for SQL Server for comprehensive documentation on granting database privileges.

The above is the detailed content of How to Grant Database Privileges in SQL Server 2008?. For more information, please follow other related articles on the PHP Chinese website!