How to add role member for existing database

Question

I want to add role member to my databases dynamically with the help of below query:

use MyExistingDatabase
GO
EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'A38'
GO 
execute as user='A38'
GO
CREATE TABLE [A38].[ABC] ( 
[Id]             int              IDENTITY(1,1)   NOT NULL,
[Name]  nvarchar(100)                             NULL,

Here A38 is my username in MyExistingDatabase and wants to execute the further script with default schema A38. The above script is working fine in case if I create a new database but if I used my any existing database, it throws the following error:

User does not have permission to perform this action

CREATE TABLE permission denied in database 'MyExistingDatabase'

Please suggest

Answer 1

You are creating the table with a schema called A38, so you need to make sure that the schema exists, like this:

use MyExistingDatabase
GO
EXEC sp_addrolemember 'db_owner', 'A38'
GO 
CREATE SCHEMA A38 AUTHORIZATION A38
GO 
CREATE TABLE [A38].[ABC] ( 
[Id]             int              IDENTITY(1,1)   NOT NULL,
[Name]  nvarchar(100)                             NULL)

Otherwise see this link for an alternate method.