none
Duplicate user permissions under "securables" in SQL Server 2012

    Question

  • Hi,

    I'm getting duplicate permissions under [user-permissions -> securables] each time I grant a specific permission and save the change. This occurs in each database and no matter which user I try to edit. It first occured after I deleted and recreated a single user several times for permission testing (by wizard and also per script). Deleting the user and restarting the server did not fix this. First I got an error regarding the dictionary stating "item with same key has already been added" (translated from german "ein element mit dem gleichen schlüssel wurde bereits hinzugefügt"). But no I dont even get this error anymore. Any Ideas?

    Thanks in advance!

    -----
    h t t p s ://dl.dropboxusercontent.com/u/27439415/error.png

    SQL Server 2012 (EN) - 11.0.3350



    • Edited by daRul3r Tuesday, April 23, 2013 7:11 AM
    Tuesday, April 23, 2013 6:47 AM

All replies

  • Can you post your script to reproduce the issue?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, April 23, 2013 6:55 AM
  • Hi, here is the script. First I set the permissions explicitly on all objects over different databases. Then I activated ownership chaining and reduced the permissions in the databases [core] and [staging] to just creating the public guest user.

    -- specify if sql login should be created DECLARE @sqlLogin bit = 1 DECLARE @loginname nvarchar(255) = N'reporting' -- only for sql login DECLARE @password nvarchar(25) = N'########' --- DECLARE @sql nvarchar(4000) USE [master]; -- create server login IF @sqlLogin = 0 BEGIN -- create windows login IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE [name] = @loginname) BEGIN SET @sql = 'CREATE LOGIN [' + @loginname + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master]' EXEC sp_executesql @sql END END ELSE BEGIN -- create sql login IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE [name] = @loginname) BEGIN SET @sql = 'CREATE LOGIN [' + @loginname + '] WITH PASSWORD=N''' + @password + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF' EXEC sp_executesql @sql END END --- USE [Interfaces]; -- create user in interfaces db IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE [name] = @loginname) BEGIN SET @sql = 'CREATE USER [' + @loginname + '] FOR LOGIN [' + @loginname + ']' EXEC sp_executesql @sql END -- grant permissions SET @sql = 'GRANT SELECT ON [dbo].[Core_FTE_Durchschnitt_pro_Monat_und_Mitarbeiter] TO [' + @loginname + ']' EXEC sp_executesql @sql SET @sql = 'GRANT SELECT ON [dbo].[Sage_FTE_Durchschnitt_pro_Monat_und_Mitarbeiter] TO [' + @loginname + ']' EXEC sp_executesql @sql --- USE [Core]; -- create user in core db IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE [name] = @loginname) BEGIN SET @sql = 'CREATE USER [' + @loginname + '] FOR LOGIN [' + @loginname + ']' EXEC sp_executesql @sql END --- USE [Staging];

    -- create user in staging db IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE [name] = @loginname) BEGIN SET @sql = 'CREATE USER [' + @loginname + '] FOR LOGIN [' + @loginname + ']' EXEC sp_executesql @sql END



    • Edited by daRul3r Tuesday, April 23, 2013 7:18 AM
    Tuesday, April 23, 2013 7:01 AM
  • I have ran your script on SS2012 with out problem.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, April 23, 2013 12:44 PM
  • I ran the script without problems, too. But after several deletions of users and recreation, the error occured and won't disappear.

    Until I set a permission, everything looks fine - i.e. showing the "Select" option only once:

    [Permission]	[Grantor]	[Grant]
    
    Alter  				[ ]
    Control 				[ ]
    Delete 				[ ]
    Insert  				[ ]
    References			[ ]
    Select  				[ ]
    Take Ownership 		[ ]
    Update  				[ ]

    ...

    But when granting the "Select" permission, saving the user and reopening it, the Select-Permission shows twice under the "Securables".

    [Permission]	[Grantor]	[Grant]
    
    Alter  				[ ]
    Control 				[ ]
    Delete 				[ ]
    Insert  				[ ]
    References			[ ]
    Select  				[ ]
    Select  	dbo			[x]
    Take Ownership 		[ ]
    Update  				[ ]


    The permission works as expected and grants the user the select-permission. When I now grant the user the second, duplicate select-permission, save it and reopen the user, I still see the situation above with the two "selects" and only one of them checked. So is this just a display-problem?

    I cannot even recreate the issue when looking at sys.database_permissions:

    select	* 
    from	sys.database_permissions dperm
    			
    			inner join sys.database_principals dprinc
    			on dperm.grantee_principal_id = dprinc.principal_id
    
    where dprinc.name = 'test'












    • Edited by daRul3r Tuesday, April 23, 2013 1:23 PM
    Tuesday, April 23, 2013 1:12 PM
  • I see something similar as well. Note the difference in the Grantor column. It's blank in once case, and dbo for the other.

    I'd say that this is a display issue. The screen has blank entries for all possible permissions, and then it the actual ones, so you will get duplicates in these cases. And with multiple grantors, you could get even more rows.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, April 23, 2013 9:45 PM
  • I should add that I did not get the error message you mentioned. That sounded like a client-side error to me.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, April 23, 2013 9:45 PM
  • When the error message first showed up, I wasn't able to grant any permission under securables because everything was crossed out in red. Although when opening a user for editing, everything looked fine and I saw the current permissions under securables. When I then switched to another object for granting further permissions, everything was crossed out, even the object I was able to edit before. So there seems to be a metadata error. The error message only showed on my local machine with the german SSMS. When I switched to the Management Studio on the db server (english SSMS), the error disappeared and the issue with the duplicate securables started to occur (in both versions of SSMS). So there seems to be a metadata error?!

    Wednesday, April 24, 2013 8:24 AM
  • We are experiencing a similar issue with our stored procedures:

    [Permission]	[Grantor]	[Grant]
    
    Control 				[ ]
    Execute 				[ ]
    Execute  	dbo			[x]
    Take Ownership 		[ ]
    View Definition			[ ]

    Have you been able to resolve your issue?  It's problematic for us as the role account associated with our procedures cannot execute them.  Any solution or workaround is appreciated.


    • Edited by SD Eric Friday, May 24, 2013 6:09 PM
    Friday, May 24, 2013 6:09 PM
  • As I noted in earlier in the thread, this is a display issue.

    If you have problems with permissions not working as expected, I suggest that you start a new thread and describe your problem from beginning to end, so that we can help you better.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 24, 2013 9:28 PM
  • This turned out to be a red herring for us.  The problem was elsewhere.  I did compare SSMS 2008 and 2012, and only the 2012 version displays securables in the way outlined above.  Perhaps this is expected behavior in SMSS 2012.  Can someone confirm this?

    Friday, May 24, 2013 9:29 PM