Home Dashboard Directory Help
Search

Creating and adding permissions to new SQL users by John Humenik


Status: 

Closed
 as Not Reproducible Help for as Not Reproducible


1
0
Sign in
to vote
Type: Bug
ID: 424845
Opened: 3/18/2009 9:21:42 AM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

Description

When creating a SQL User using SSMS, I try and grant db_owner access to multiple databases in the 'User Mapping' section. It will show when I click on the various databases that I've selected that I'm db_owner, but after saving and closing it. My user will not in fact have owner access.
Details
Sign in to post a comment.
Posted by Alessandro Bernardi on 10/5/2011 at 3:58 AM
Mapping the user again once it has been created this works as expected.
Posted by Alessandro Bernardi on 10/5/2011 at 3:56 AM
I'm able to reproduce this behaviour as well, after restoring a database from a different server.

Try following this steps:
0. Restore a database backup overwriting an existing database
1. Open SQL Server Management Studio (both standard and express version)

My version is:
Microsoft SQL Server Management Studio 10.50.1617.0
Strumenti client di Microsoft Analysis Services 10.50.1617.0
Microsoft Data Access Components (MDAC)    6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 2.0.50727.5446
Sistema operativo 6.1.7601

2. Open the SQL Server, and right click the Security folder
3. New -> User account
4. Fill the form and go to the Mapping section
5. Map the new user as db_owner or any other right to a database that has been restored from a different server (you should have the db_owner checkbox already checked, this won't happen with other database)
6. Click ok
7. Open the user again and check its user mapping

In my case the user is mapped to the database, but does not have the db_owner role.
Posted by John Humenik on 10/3/2011 at 9:28 AM
They only way I've seen this being reproduced are by using these steps below.

1. Backup a database with a SQL User as db_owner
2. Restore the db on another server without the SQL user
3. When you go to add the user to the server without first deleting the user from the DB, you see that it has the new user as already setup as db_owner, but in fact it's not.
Posted by Microsoft on 6/12/2011 at 11:16 PM
We tried to reproduce the error on both SQL Server 2008 R2 and SQL Server code-named 'Denali' - Community Technology Preview 1 (CTP1), and the error did not reproduce. Please use SQL Server 2008 R2 and SQL Server code-named 'Denali' and if you see the error then please activate this bug. It will be of great help if you can also attach a screenshot of the issue.

Regards,
Posted by Ambrish Mishra on 6/10/2011 at 9:53 AM
We tried to reproduce the error on both SQL Server 2008 R2 and SQL Server code-named 'Denali' - Community Technology Preview 1 (CTP1), and the error did not reproduce. Please use SQL Server 2008 R2 and SQL Server code-named 'Denali' and if you see the error then please activate this bug. It will be of great help if you can also attach a screenshot of the issu

Regards,

Ambrish
Sign in to post a workaround.