User Mapped but are not checked in user mappings

Question

Hi there,

QUick question on user permissions. I need to grant (reader/writer) permissions to a specific user in a DB. Ofcourse I can grant the permissions via the security>logins, But I tried to execute via script (sp_addrolemember N'db_datareader/db_datawriter', N'User) successfully. My question is although the script ran successfully, when I go to Security>Logins, User box is not checked, however when I select the user, I could see the permissions (reader/writer) were selected. Could you or anyone tell me why is that the user appears as NOT checked and also how can I make it appear as checked ?

Thanks,

P

Answer 1

No, I can say that. If I wanted to know which roles a user is a member of, I would run this query:

SELECT r.name
FROM   sys.database_principals r
JOIN   sys.database_role_members rm ON r.principal_id = rm.role_principal_id
JOIN   sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE  m.name = 'thisuser'

To be honest, I don't know even know how the UI you are talking about looks like.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Thank you for your reply...

UI, I was referring to ..

Security > Logins > Login Properties (User I want to grant permissions to..) > User Mappings

As said earlier, Upon selecting the DB, read/write permissions appear to be checked. But the DB itself is not checked. (Check box under 'MAP' column is not checked)

I ran the following to grant permissions.

/* Add permissions */

EXEC sp_addrolemember N'db_datareader', N'username
EXEC sp_addrolemember N'db_datawriter', N'username

Answer 3

You seem to have orphaned users. See the "logins" section in below for some more info: http://www.karaszi.com/SQLServer/info_moving_database.asp

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 4

Hi prasap,

You need to create a login mapped the specific user.

The following example adds the Windows login CCC to the AdventureWorks database as user AAA. The user AAA is then added to the db_datareader role. Before it, you need to create a login CCC.
If the login CCC exists, right-click the user AAA and select Properties, map the user AAA to Login CCC.

USE AdventureWorks;
GO
CREATE USER AAA FOR LOGIN CCC;
GO
EXEC sp_addrolemember 'db_datareader', 'AAA'

After this, you would see Map is checked in Users mapped to this login when you go to Security>Logins.

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 5

Hi prasap,

I’m writing to follow up with you on this post. Was the problem resolved after performing our action plan steps?

If you are satisfied with our solution, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.  

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.