Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a (SS2005) database with a user named MY_APPLICATION_USER. I'm trying to create a role in that database named MY_APPLICATION_USER.

When I try to do that using this script:

/****** Object:  Role [MY_APPLICATION_USER] ******/
CREATE ROLE [MY_APPLICATION_USER] AUTHORIZATION [dbo]
GO

I get the error message:

Msg 15023, Level 16, State 1, Line 2
User, group, or role 'MY_APPLICATION_USER' already exists in the current database.

Does SQL Server have a restriction where it is not allowed to have a user and a role with the same name? I googled various ways of asking that question and did not find anything which addresses this, but it's certainly possible that I missed something.

share|improve this question
1  
The error states it rather well: You cannot have a user and role with the same name. They all fall under sys.database_principals. –  Thomas Stringer Feb 28 '12 at 16:59
    
I figured that was it but I couldn't find any documentation confirming it. Please put your comment as an answer and I'll accept it. I didn't know where it stores that information and I just assumed roles would be stored separate from the users but now I know better. –  Shane Wealti Feb 28 '12 at 17:08
    
Note this makes perfect sense: otherwise, how can you resolve SomePrincipal in this GRANT .. TO SomePrincipal –  gbn Mar 2 '12 at 15:37
add comment

migrated from stackoverflow.com Feb 28 '12 at 17:09

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote accepted

No, you cannot have a User and a Role with the same name. These objects are all stored and referenced in sys.database_principals, requiring they are unique.

sys.database_principals MSDN Reference

Quoted from the name parameter:

name : Name of principal, unique within the database.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.