Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft SQL Server, Error: 15517)

I got the below error while trying to modify the database properties (example changing recovery model), and I have db_owner privilege on the database. db_owner is sufficient to update the properties but we got the below error while trying to update it.

Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft SQL Server, Error: 15517)

Try the below steps to resolve the issue

1. Verify database owner is set to database or not, If database owner is null then assign "sa" or any other user to the database

2. If the user is already a member of database then it wont allow us to assign the same user as owner, so assign any other user as owner (but dont leave it as null)

3. Run the command "Alter authorization database::db_name to login_name"

4. Verify view any database option is selected for that user or not, if not assign that securable to the login.

I tried all the above options, later I realized we are using SSMS 2012 Tools RTM after upgrading the RTM tools with SP1 we are able to do view / modify the database properties.

 

Groupwise Sequence Number Generation , Rank and Row Number Functions

Rank and Row_Number Functions

In the below table I have couple of duplicate image numbers, I need to assign sequence number for duplicate image numbers. For example if there are three duplicate image numbers then sequence should be 1, 2, 3 for those three and again the sequence will start from 1 for next image number.

ROW_NUMBER : Returns a unique number for each row starting with 1. For rows that have duplicate values,numbers are arbitarily assigned.

Rank : Assigns a unique number for each row starting with 1,except for rows that have duplicate values,in which case the same ranking is assigned and a gap appears in the sequence for each duplicate ranking.

CREATE TABLE #tmp(IdNo INT, Image_No VARCHAR(10), Seq_No INT)
GO
INSERT #tmp (IdNo, Image_No) VALUES(1,'FS1')
INSERT #tmp (IdNo, Image_No) VALUES(2,'FS1')
INSERT #tmp (IdNo, Image_No) VALUES(3,'FS2')
INSERT #tmp (IdNo, Image_No) VALUES(4,'FS3')
INSERT #tmp (IdNo, Image_No) VALUES(5,'FS3')
INSERT #tmp (IdNo, Image_No) VALUES(6,'FS3')
INSERT #tmp (IdNo, Image_No) VALUES(7,'FS2')
GO
Below query used to return sequence number without using Row_Number and Rank Functions

SELECT IdNo, Image_No, (select count(*)+1 from #tmp b where IdNo < a.IdNo and a.Image_No = b.Image_No) FROM #tmp a order by Image_No

Rank Function

SELECT IdNo, Image_No,  RANK() OVER(PARTITION BY Image_No  ORDER BY idno) as 'Rank'  FROM #tmp

Row_Number

SELECT IdNo, Image_No,  ROW_NUMBER() OVER(PARTITION BY Image_No  ORDER BY idno) as 'RowNumber'  FROM #tmp