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

by

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.

alt text

More Related Contents:

  1. sql server 2008 management studio not checking the syntax of my query
  2. Converting Select results into Insert script – SQL Server
  3. I dont know sql syntax for my table [closed]
  4. CROSS JOIN vs INNER JOIN in SQL
  5. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  6. Split function equivalent in T-SQL?
  7. Querying data by joining two tables in two database on different servers
  8. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  9. Where value in column containing comma delimited values
  10. How to find all connected subgraphs of an undirected graph
  11. How to use table variable in a dynamic sql statement?
  12. SQL query to split column data into rows
  13. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  14. How do I find duplicates across multiple columns?
  15. Detect consecutive dates ranges using SQL
  16. Split values over multiple rows [duplicate]
  17. Incorrect syntax near ”
  18. How to do an inner join on row number in sql server
  19. sql varchar(max) vs varchar(fix)
  20. How do I prevent a database trigger from recursing?
  21. SQL Server: should I use information_schema tables over sys tables?
  22. store arabic in SQL database
  23. Convert Date format into DD/MMM/YYYY format in SQL Server
  24. TSQL – How to use GO inside of a BEGIN .. END block?
  25. How to combine results of two queries into a single dataset
  26. Executing SQL Server Agent Job from a stored procedure and returning job result
  27. Backup a single table with its data from a database in sql server 2008
  28. COALESCE Function in TSQL
  29. Script entire database SQL-Server
  30. Editing Record issues in Access / SQL (Write Conflict)

Leave a Comment