User mapping changes dissappear

Question

Post reply

User mapping changes dissappear

JimiHaze

Mr or Mrs. 500

Points: 559
More actions
April 12, 2016 at 7:05 am

#306361

Hi All,
Whenever I change a server login's database user mapping, everything seems to work fine, but then when I reopen the login's properties none of the changes that I made persist. I've tried this through the GUI, T-SQL, at the server level - login - properties - user mapping, and at the database level - role - properties - membership. No matter what I try (GUI or scripting) nothing errors, everything completes successfully, but the changes are not made. Some online research points to not being able to drop a user from a role if that user is the dbo, but this is not the dbo user.
Any thoughts on what might be happening would be greatly appreciated.
Thanks,
JimiHaze

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 233910 More actions · Answer 1

its sounds very much like the database user already exists and is orphaned.

Please post the results of the following

USE [yourdb]

select sid AS LoginSID from master.sys.server_principals

where name = 'theuser'

select sid AS DBUserSID from sys.database_principals

where name = 'theuser'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

JimiHaze Mr or Mrs. 500 Points: 559 More actions · Answer 2

LoginSID : 0x010500000000000515...

DBUserSID: returned nothing

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

In that case, please will you post the exact script you ran to get that result, along with the script you ran when attempting to change the mapping. You can obfuscate login names and any other details if you need to, as long as you're consistent in what you post.

Thanks

John

JimiHaze Mr or Mrs. 500 Points: 559 More actions · Answer 4

Certainly. Here's one that I've tried running a few times:

USE [myDB]

GO

ALTER ROLE [DBRole] DROP MEMBER [DOMAIN\serviceAcct]

GO

[DOMAIN\serviceAcct] is what I used for 'where name = ' in the post above.

Thanks,

JimiHaze

Perry Whittle SSC Guru Points: 233910 More actions · Answer 5

JimiHaze (4/12/2016)
LoginSID : 0x010500000000000515...
DBUserSID: returned nothing

you set [yourdb] and the user name string before executing?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

JimiHaze Mr or Mrs. 500 Points: 559 More actions · Answer 6

Yes, I changed all of your examples into what I needed.

Perry Whittle SSC Guru Points: 233910 More actions · Answer 7

JimiHaze (4/12/2016)
Yes, I changed all of your examples into what I needed.

does the login own the database by any chance

select suser_sname(owner_sid), name

from sys.databases

where database_id = db_id('yourdb')

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

JimiHaze Mr or Mrs. 500 Points: 559 More actions · Answer 8

JimiHaze

Mr or Mrs. 500

Points: 559

April 12, 2016 at 11:33 am

#1871480

The owner of the database is [sa].

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Perhaps your login is mapped to a database user with a different name? Please run this:

USE MyDB

SELECT u.name

FROM sys.server_principals l

JOIN sys.database_principals u ON l.sid = u.sid

WHERE l.name = 'DOMAIN\serviceAcct' -- don't include the square brackets here

John

JimiHaze Mr or Mrs. 500 Points: 559 More actions · Answer 10

Running that query returns the same name as the login that I put in for l.name

Thanks,

JimiHaze

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

In that case, Perry's script should return the same SID twice. Something strange is going on. You'll need to talk us through exactly what you're doing, please, perhaps with screenshots or queries and results that show role membership and so on.

John

JimiHaze Mr or Mrs. 500 Points: 559 More actions · Answer 12

I did another test by dropping the user and re-creating them in the database. This will - of course - remove them from the role. However, when I add them back into the other DB roles that they are supposed to be in, they get automatically added to the role that I do NOT want them in. Should I be looking for some sort of trigger that automatically adds them if they are in a certain role?

Thanks,

JimiHaze

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 13

Yes, check for triggers. If you don't find anything, try an extended events session to capture user creation, role membership changes and so on. That might give you a clue to what's going on.

John

JimiHaze Mr or Mrs. 500 Points: 559 More actions · Answer 14

There are no database triggers present that would cause this to happen. Also, I'm not quite sure what events I should be capturing in the Extended Events Session to be able to see the changes taking place. I've done some research, but nothing is leading me to the answer there.

Thanks,

JimiHaze