Posted By: Anonymous
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?
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 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.