I observed a strange issue with SQL Server user mapping while creating a login in SQL Server 2012 SP1.
While creating a new login…
After entering some required info…
When I click on user mapping and select a database…
The db_owner role gets selected automatically. This is not how it is supposed to be. Infact, when I click OK the login gets created but actually the role is not applied for the user. When you open the login again and verify from the same page…
… the roles is actually not applied. Very misleading !
6 Comments on “SQL Server user mapping – a strange issue while creating a login”
One of a thousand reasons NOT to use the GUI tools, but instead to explicitly script everything.
Old administrator rule come back again. Never use new features in production servers, include services packs 🙂 There’s no first time, when Microsoft lost or in this case add some functionality.
I just tried to duplicate your issue. I’m not able to. It’s working as expected. Are you running the base release of SP1?
There are several nasty bugs introduced with SP1, you need to be running CU3 with hotfix KB 2832017 (http://support.microsoft.com/kb/2832017). CU3 with this hotfix is the first “useable” build of SP1.
You may need to check if the user who is creating this user and mapping with the login has required privileges to make DBO user role effective. And yes, the best way is to go through CUI which we are re;uctant when easier GUI tempts us 🙂
I have seen this exact same thing. It’s almost like it doesn’t update the roles, it may take a minute to clear out, sometimes I’ve seen it update and go away, other times it did not.
I Think you have issues of permisson… do you try it with a query with the permissions exactly? and did you look at the database if was the user right there? the account has the right permission to delegate permissions to other accounts.