Search code examples
sqlsql-serverpermissionsrolesdatabase-administration

There is already an object named "Test" in the database while adding a role using SP_AddRole in SQL Server 2012

I am trying to add a role in my master database in SQL Server 2016 using below command:

SP_AddRole 'test'

I am getting this error:

There is already an object named "test" in the database.

I have checked expanding Roles in my master database, and I found that there is no Role with name "test" in my master database.

Still I have tried to Drop that Role using below query:

Drop Role 'Test'

I get this error:

Cannot drop the role because it does not exist or you do not have permissions

When I try to alter Role using below query:

Alter Role Add Member "Domain\userName"

I was getting same error as below:

Cannot alter the role because it does not exist or you do not have permissions.

Note: I have all permissions and privileges to SQL Server and master database.

Can someone please suggest what can be done in order to resolve this?

I can't change the name of the role as it is very important for my applications to create a role with this name.

Solution

  • There was an already created SCHEMA in that SQL Server database.

    I have deleted that schema

    DROP SCHEMA Test

    And then below SQL query worked.

    SP_AddRole 'Test'

    When I create Role, a default schema with same name gets created which was visible when I expand Schemas.

    When I try to Drop the Role, I will need to first Drop the schema and then the Role.

    Even if the schema with same name(But Role does not) present, SQL Server does not allow me to create new Role with same name.

    Reference here