How to Grant Read Access for a User to a Database in SQL Server

How do I grant read access for a user to a database in SQL Server?

This is a two-step process:

1. you need to create a login to SQL Server for that user, based on its Windows account

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

2. you need to grant this login permission to access a database:

   USE (your database)
   CREATE USER (username) FOR LOGIN (your login name)
   

Once you have that user in your database, you can give it any rights you want, e.g. you could assign it the db_datareader database role to read all tables.

USE (your database)
EXEC sp_addrolemember 'db_datareader', '(your user name)'

Granting Full SQL Server Permissions for a Database

If you literally want them to be able to do anything in that database, you can just add them to the db_owner role:

USE ContainedDatabase;
GO
ALTER ROLE db_owner ADD MEMBER [username];

If you want to be more granular, you can add them to lesser roles, like db_ddladmin, db_securityadmin, etc. You can see the list of built-in roles here:

• Database-Level Roles

The permissions inherent in each of those roles:

• Permissions of Fixed Database Roles

And if those don't suit, you can create your own roles, add your user to that role, and grant specific permissions to that role you created (and/or add them to other roles). The difference between applying the permissions to the role instead of directly to the user is simply reuse - if you add five more users that you want to apply the same permissions, you just add them to the custom role, rather than apply those granular permissions or roles to all 5 of the users.

SQL Server: grant all users read access to a database

One method is to enable the guest user and add it to the db_datareader fixed database role:

USE YourDatabase;
CREATE USER guest;
ALTER ROLE db_dataReader ADD MEMBER guest;

SQL Server - How to Grant Read Access to ALL databases to a Login?

One way would be to Set "Results to Text" on the query menu in SSMS then execute the below.

It doesn't actually make the change but generates a script for you to review and execute.

SET NOCOUNT ON;

DECLARE @user_name    SYSNAME
        , @login_name SYSNAME;

SELECT @user_name = 'user_name',
       @login_name = 'login_name'

SELECT '
    USE ' + QUOTENAME(NAME) + ';

    CREATE USER ' + QUOTENAME(@user_name)
       + ' FOR LOGIN ' + QUOTENAME(@login_name)
       + ' WITH DEFAULT_SCHEMA=[dbo];

    EXEC sys.sp_addrolemember
      ''db_datareader'',
      ''' + QUOTENAME(@user_name) + ''';

    EXEC sys.sp_addrolemember
      ''db_denydatawriter'',
      '''
       + QUOTENAME(@user_name) + '''; 

GO
'
FROM   sys.databases
WHERE  database_id > 4
       AND state_desc = 'ONLINE' 

Or you could look at sys.sp_MSforeachdb as here or Aaron Bertrand's improved version here

If you are not seeing all of the characters when you run this, open the Query Options for Text and check the setting for 'Maximum number of characters displayed in each column'. Make sure this is set to a value large enough to display all characters.

How to grant database user read/write access roles the new way?

If you are using sql server 2008 only sp_addrolemember will work.

The alter role syntax is only valid for the 2012 version. It is due to that you get an incorrect syntax error

What is the T-SQL To grant read and write access to tables in a database in SQL Server?

In SQL Server 2012, 2014:

USE mydb
GO

ALTER ROLE db_datareader ADD MEMBER MYUSER
GO
ALTER ROLE db_datawriter ADD MEMBER MYUSER
GO

In SQL Server 2008:

use mydb
go

exec sp_addrolemember db_datareader, MYUSER 
go
exec sp_addrolemember db_datawriter, MYUSER 
go

To also assign the ability to execute all Stored Procedures for a Database:

GRANT EXECUTE TO MYUSER;

To assign the ability to execute specific stored procedures:

GRANT EXECUTE ON dbo.sp_mystoredprocedure TO MYUSER;

Grant all permissions of a database to a user

You can use the following:

exec sp_addrolemember 'db_owner', 'user1';

When you are dealing with permissions you can refer the official documentation and download the poster sized chart of all Database Engine permissions in PDF format.

You can than search by role (for example db_onwer) and find what are the permissions that it has.

SQL Server - Give a Login Permission for Read Access to All Existing and Future Databases

For new databases, add the user in the model database. This is used as the template for all new databases.

USE model
CREATE USER ... FROM LOGIN...
EXEC sp_addrolemember 'db_datareader', '...'

For existing databases, use sp_MSForEachDb

EXEC sp_MSForEachDb '
 USE ?
 CREATE USER ... FROM LOGIN...  
 EXEC sp_addrolemember ''db_datareader'', ''...''
'

---

Related Topics