SQL Server 2008: how do I grant privileges to a username?

Question

I need to be able to establish an ODBC connection through SQL Server authentication.

In SSMS how do I grant permission for a user to be able to have ALL RIGHTS on a specific database?

is there a way to do this graphically with SSMS?

Answer 1

If you want to give your user all read permissions, you could use:

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

That adds the default db_datareader role (read permission on all tables) to that user.

There's also a db_datawriter role - which gives your user all WRITE permissions (INSERT, UPDATE, DELETE) on all tables:

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

If you need to be more granular, you can 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

and so forth - you can granularly give SELECT, INSERT, UPDATE, DELETE permission on specific tables.

This is all very well documented in the MSDN Books Online for SQL Server.

And yes, you can also do it graphically - in SSMS, go to your database, then Security > Users, right-click on that user you want to give permissions to, then Properties adn at the bottom you see "Database role memberships" where you can add the user to db roles.