Sql Server adding another user

Question

I have a Database say TestDatabase.I have already added an user under NTAuthority\SYSTEM(Role) Db_owner.

Again i wish to add another user to the same Database under NTAuthority\System with different role.

I received the following error :

The login already has an accoind under different username.

SQL Server Error :15063

How can i add different users to the same database with different role.

I would appreciate if i receive elobarate explanation.

Answer 1

You can't. You add the same user to multiple roles.

db_owner already has all rights anyway withing the database so any other permissions are superfluous.

CREATE USER [NTAuthority\SYSTEM] FROM LOGIN [NTAuthority\SYSTEM];
EXEC sp_addrolemember 'db_owner', 'NTAuthority\SYSTEM'
-- ...and fails
CREATE USER [bob] FROM LOGIN [NTAuthority\SYSTEM];

--Normally, for one user in multiple roles
EXEC sp_addrolemember 'DoStuff', 'NTAuthority\SYSTEM'
EXEC sp_addrolemember 'DoSpecialStuff', 'NTAuthority\SYSTEM'
--... and for another user
EXEC sp_addrolemember 'DoStuff', 'AnotherUser'
--... and for yet another user
EXEC sp_addrolemember 'DoLimitedStuff', 'TheThirdUser'

Edit:

Look at the MS documentation: Database-Level Roles and the parent Identity and Access Control topic

Answer 2

1. if is db_owner there is no point in adding him to any other role.

2. to add an existing user to a new role you run sp_roleaddmember