Give users permission to SQL Server database

Give users permission to SQL Server database

If not installed, download SQL Server Management Studio from here:  https://www.protracker.com/Download/Tools/SQLServer2005_SSMSEE.msi

Give domain users permission to access database using Windows Authentication

1. Load SQL Server Management Studio

2. Connect to target SQL Server

3. Expand Security folder

4. Expand Logins folder

5. Look for DOMAIN_NAME\Users   (ex Server1\John). If it's not in the list, add it by right-clicking and select "New Login"

6. On the Login Properties dialog, General section, choose "Windows Authentication"

7. On the Login Properties dialog, Server Roles section, select "public" and "sysadmin"

8. On the Login Properties dialog, Status section, "grant" permission to connect

9. On the Login Properties dialog, Status section, set Login to "enabled"

 

Give a specific user permission to access the database using Windows Authentication

1. Load SQL Server Management Studio

2. Connect to target SQL Server

3. Expand Security folder

4. Expand Logins folder

5. Look for DOMAIN_NAME\USER_NAME   (ex Server1\John). If it's not in the list, add it by right-clicking and select "New Login"

6. On the Login Properties dialog, General section, choose "Windows Authentication"

7. On the Login Properties dialog, Server Roles section, select "public" and "sysadmin"

8. On the Login Properties dialog, Status section, "grant" permission to connect

9. On the Login Properties dialog, Status section, set Login to "enabled"

 

If the user or user group is already in the list, it may be that they don't have adequate permissions or hte login is disabled.  The above settings should work.

 

You could also try give the user specific permission to the database as follows:

1. Load SQL Server Management Studio

2. Connect to target SQL Server

3. Expand the target database

4. Expand Security folder

5. Expand Users folder

6. Look for DOMAIN_NAME\USER_NAME  (ex Server1\John). If it's not in the list, add it by right-clicking and select "New User"

7. Enter the users full name

8. Enter the user's full login name (ex: Server1\John)

9. In Database role membership, select "db_owner", then Click OK

Have more questions? Submit a request

0 Comments

Article is closed for comments.