Creating and adding permissions to new SQL users - by John Humenik

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 424845 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 3/18/2009 9:21:42 AM
Access Restriction Public

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.  
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