How to Grant Database Privileges to a SQL Server 2008 User?

Granting Privileges to a Username in SQL Server 2008

To establish an ODBC connection using SQL Server authentication, you require the appropriate user permissions. This article will guide you on granting specific database privileges to a user, ensuring they possess the necessary rights for accessing data.

Graphical Method Using SSMS

Within SQL Server Management Studio (SSMS), navigate to the database you wish to modify. Under the Security tab, locate Users. Right-click on the target user and select Properties. Scroll down to the Database role memberships section and add the user to relevant roles.

Command-Line Method Using Stored Procedures

To grant read permissions to all tables in a database, execute the following stored procedure:

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

Similarly, to grant write permissions, use:

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

For granular control, 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

Detailed documentation on user permissions in SQL Server is provided in MSDN Books Online.

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