How to Grant Database Privileges in SQL Server 2008 Using SSMS and T-SQL?

Granting Database Privileges in SQL Server 2008 Using Graphical and Command Tools

To establish an ODBC connection using SQL Server authentication, users require specific database privileges. This article demonstrates how to grant these privileges using both graphical and command-line tools in SQL Server 2008.

Graphical Method (SSMS)

1. In SQL Server Management Studio (SSMS), navigate to the desired database.
2. Expand the "Security" folder and select "Users".
3. Right-click on the user you want to grant privileges to and choose "Properties".
4. Click on the "Database Role Memberships" tab and select the appropriate roles to add the user to.

Command-Line Method

To grant specific privileges on a per-table basis, 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

To grant broader read permissions, add the user to the default db_datareader role:

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

For write permissions, add the user to the db_datawriter role:

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

Additional Notes

• Granular permissions allow you to grant specific privileges on individual tables.
• The MSDN Books Online for SQL Server provides detailed documentation on granting privileges.
• SSMS also offers a graphical way to manage database role memberships for increased ease of use.

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