January 9, 2013 at 1:14 pm
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 9, 2013 at 7:24 pm
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.
January 10, 2013 at 7:28 am
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 11, 2013 at 8:02 am
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.
January 17, 2014 at 11:47 am
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply