Database checkbox in login mapping disappears

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Database checkbox in login mapping disappears...

Database checkbox in login mapping disappears

Rate Topic

Display Mode

Topic Options

Author

Message

webrunner

Posted Wednesday, January 09, 2013 1:14 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:05 PM
Points: 2,117, Visits: 2,209

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

-------------------
"The chemistry must be respected." - Walter White

"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

Mvrx

Posted Wednesday, January 09, 2013 7:24 PM

Forum 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

webrunner

Posted Thursday, January 10, 2013 7:28 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:05 PM
Points: 2,117, Visits: 2,209

Mvrx (1/9/2013)

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

- webrunner

-------------------
"The chemistry must be respected." - Walter White

"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

paul.knibbs

Posted Friday, January 11, 2013 8:02 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:56 AM
Points: 1,256, Visits: 4,253

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

« Prev Topic | Next Topic »

Permissions