SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database checkbox in login mapping disappears


Database checkbox in login mapping disappears

Author
Message
webrunner
webrunner
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4215 Visits: 3887
Hi,

I'm seeing a strange issue. I want to use SSMS to assign a login to a database with db_datareader and db_datawriter roles. Everything looks like it works, but when I go back to view the login's properties, the checkbox next to the database is gone, as if I hadn't checked it.

However, the db_datareader and db_datawriter checkboxes ARE checked, and they stay checked when I (again) check the box next to the database name. Two other databases for the same login don't show that issue.

These databases were migrated from a SQL Server 2000 Standard Edition server to a SQL 2008 R2 Enterprise Edition server - but they are still in SQL 2000 (80) compatibility mode. Not sure if that is relevant.

Has anyone else seen this behavior? If so, does anyone know why it happens and how I can resolve it? It is annoying, but even worse may be a factor in some permissions errors some clients are reporting.

Also, I have run the query below to see if I have somehow orphaned this user, but that query returns 0 rows.

EXEC sp_change_users_login 'Report';
GO

Thanks for any help!

- webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Mvrx
Mvrx
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 12
I have seen that when there is a database, and the user has been created but login has not yet been created.
(Not sure if that also happens when login exists, but instead of being mapped, a user is directly created in the database)

Can you check if the user in the database is disabled?
If it is - you may have to drop the user, I think it will also require changing owner of the schema the user is associated to - to dbo.
Then map the login to the database. It could be something else too, but sounded like what I'd seen.
webrunner
webrunner
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4215 Visits: 3887
Mvrx (1/9/2013)
I have seen that when there is a database, and the user has been created but login has not yet been created.
(Not sure if that also happens when login exists, but instead of being mapped, a user is directly created in the database)

Can you check if the user in the database is disabled?
If it is - you may have to drop the user, I think it will also require changing owner of the schema the user is associated to - to dbo.
Then map the login to the database. It could be something else too, but sounded like what I'd seen.


Thanks for your help. I will look into these items and report back as soon as I can.

- webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2605 Visits: 6232
I wonder if this is a bug in SQL Management Studio, because I've seen the same behaviour myself--it always seems to be the bottom-most database in the list that "forgets" its checkbox state the first time you do it, and you then have to do it again.
webrunner
webrunner
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4215 Visits: 3887
Thanks, everyone.

So sorry for the super-late reply. But the issue was a disabled user as suggested. I noticed it again today, which is how I came back to this thread. Dropping the db user and re-mapping the login fixed the issue.

About the possible bug - this database was not the last checked db in the list, so I didn't see that pattern. But it's possible that it is a bug nevertheless, and that in some cases affects the last db in a list. I'd love to see whether anyone else sees this problem and has any clues about what causes it. My best guess right now is that it may be a side-effect of restoring a db on a different server, but that's just a hunch.

Thanks again!

- webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search