Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database checkbox in login mapping disappears Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 1:14 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 2,351, Visits: 2,700
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


-------------------
"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
Post #1404969
Posted Wednesday, January 9, 2013 7:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 12, 2013 11:44 AM
Points: 7, 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.
Post #1405125
Posted Thursday, January 10, 2013 7:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 2,351, Visits: 2,700
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


-------------------
"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
Post #1405436
Posted Friday, January 11, 2013 8:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 1,631, Visits: 5,578
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.
Post #1406049
Posted Friday, January 17, 2014 11:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 2,351, Visits: 2,700
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


-------------------
"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
Post #1532211
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse