SQL Server 2008 Express Grant User Permission

Question

What is the appropriate SQL commands (not through the GUI) to add a windows user account to permissions on a SQL Server 2008 Database? ie.. I want to give someone read access and another person read/write..

Answer 1

It's a two-step process:

• first, you need to create a login for that user, based on its Windows credentials

  CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS
  

  This sets up the basic permission to even connect to your SQL Server

• once you have a login, you can create a user in each database where you want to give that login rights to do something:

  CREATE USER Johnnie FOR LOGIN Domain\Johnnie;
  

• to grant read permissions on every table in your database, assign the db_datareader role to that user

  sp_addrolemember @rolename = 'db_datareader', @membername = 'Johnnie'
  

• to grant read and write permissions on every table in your database, assign both the db_datareader as well as the db_datawriter role to that user

• if you need more fine grained control over what tables can be read from or written to, you need to manage permissions manually, e.g. by creating your own database roles and assigning grants to those roles, and then adding your users to those app-specific roles you created