CODEWITHSUNDEEP
sqlsql-serverdatabase
Anu
Anu

Reputation: 1423

"ALTER ROLE db_owner ADD MEMBER A" doesn't work

When I execute ALTER ROLE db_owner ADD MEMBER A it gives out following error.

Msg 15151, Level 16, State 1, Line 4
Cannot alter the role 'A', because it does not exist or you do not have permission.

Here ALTER ROLE it mentions that;

Limitations and restrictions

You cannot change the name of a fixed database role.

But I can't find any relationship to this with the error. What I'm trying to do is adding a member not changing the fixed role name.

Any support in resolving this matter is highly appreciated

Upvotes: 15

Views: 54194

Answers (2)

R. Richards
R. Richards

Reputation: 25161

I think that you are missing a step. You have a login, but you are not adding the login as a user to the database. All the steps below are what you need. The CREATE USER step (a database level call) seems to be missing from your work.

I don't think you need the CREATE LOGIN, I just wanted to include that so one could see all it takes to do this.

USE [master]
GO
CREATE LOGIN [A] WITH PASSWORD=N'<password>', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [YourDatabase]
GO
CREATE USER [A] FOR LOGIN [A]
GO 
ALTER ROLE db_owner ADD MEMBER [A]
GO

Change YourDatabase to the proper value before you try this.

Upvotes: 33

Singh Kailash
Singh Kailash

Reputation: 674

First you drop existing user role then use below command: 

USE Databasename
GO
-- create new role for your s to belong to
CREATE ROLE s
GO
-- add s Role to db_owner
EXEC sys.sp_addrolemember 
    @rolename = N'db_owner',
    @membername = N's';
GO
GO

Upvotes: -1

Related Questions