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
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
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
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, email@example.com
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?!
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
In case someone else stumbles upon this, I'm seeing the same behavior. I am dbo. If I have execute in the dbo row and then deny in the other row, save, and then re-open, now the dbo row shows deny and the non-dbo row is empty. So it's just a strange display issue. It's not intuitive, but I imagine if you had a different grantor change permissions and opened up you could see a grant and deny on the same permission. But, if you're the only one doing it, basically ignore the blank row.