Duplicate user permissions under "securables" in SQL Server 2012

Tuesday, April 23, 2013 6:47 AM

0
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
- Reply
- Quote

All Replies

Tuesday, April 23, 2013 6:55 AM

0

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
- Reply
- Quote

Tuesday, April 23, 2013 7:01 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 12:44 PM

0

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
- Reply
- Quote
Tuesday, April 23, 2013 1:12 PM

0
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:13 PM
- Edited by daRul3r Tuesday, April 23, 2013 1:13 PM
- Edited by daRul3r Tuesday, April 23, 2013 1:13 PM
- Edited by daRul3r Tuesday, April 23, 2013 1:14 PM
- Edited by daRul3r Tuesday, April 23, 2013 1:16 PM bad formatting
- Edited by daRul3r Tuesday, April 23, 2013 1:17 PM
- Edited by daRul3r Tuesday, April 23, 2013 1:18 PM
- Edited by daRul3r Tuesday, April 23, 2013 1:19 PM
- Edited by daRul3r Tuesday, April 23, 2013 1:23 PM
- Reply
- Quote
Tuesday, April 23, 2013 9:45 PM

0

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 protected]
- Reply
- Quote
Tuesday, April 23, 2013 9:45 PM

0

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, [email protected]
- Reply
- Quote
Wednesday, April 24, 2013 8:24 AM

0

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?!
- Reply
- Quote