Share via

Grant access to the database

After the Create a login procedure, DA_Mary has access to the specified instance of SQL Server 2005, but does not have permission to access the staging databases. DA_Mary does not have access to the default database until you authorize DA_Mary as a database user.

To grant DA_Mary access, switch to the database, and then use the CREATE USER statement to map DA_Mary's login to a user name.

To create a user in a database and add the user as member to specific permissions

  1. Type and run the following statements (replacing domain_name with the name of your domain) to grant DA_Mary access to the TestData database:

    USE [Staging_TestData];
GO 
CREATE USER [DA_Mary] FOR LOGIN [domain_name\DA_Mary];
GO

  2. Use the following fixed database roles to grant permissions to the staging database.

    USE [Staging_TestData]
EXEC sp_addrolemember N'db_datareader', N'domain_name\DA_Mary
GO
USE [Staging_TestData]
EXEC sp_addrolemember N'db_datawriter', N'domain_name\DA_Mary
GO
USE [Staging_TestData]
EXEC sp_addrolemember N'db_owner', N'domain_name\DA_Mary
GO

    Note

    You must determine which permissions are required for sources tables.

    For more information about fixed database roles, such as db_datareader and corresponding database-level permissions, review SQL Server Books Online (https://go.microsoft.com/fwlink/?LinkId=93772).

See Also

  • Last updated on