Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I encounter the following error:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

when I was trying these commands

USE ASPState
GO

GRANT EXECUTE ON CreateTempTables TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON DeleteExpiredSessions TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON GetMajorVersion TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON GetHashCode TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetAppID TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItem TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItem2 TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItem3 TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItemExclusive TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItemExclusive2 TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItemExclusive3 TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetVersion TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempInsertStateItemLong TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempInsertStateItemShort TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempInsertUninitializedItem TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempReleaseStateItemExclusive TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempRemoveStateItem TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempResetTimeout TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempUpdateStateItemLong TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempUpdateStateItemLongNullShort TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempUpdateStateItemShort TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempUpdateStateItemShortNullLong TO [R2Server\AAOUser]
GO

So, how do I know what type of permissions my account R2Server\AAOUser have for the database ASPState? and what are the ways to solve the error (so that I can grant these permissions to my account)?

share|improve this question
    
Is your user sysadmin on the server? Or db owner on that db? –  Marian Feb 22 '13 at 8:08
    
Actually none. (my user account is not a sysadmin nor db owner.) –  Jack Feb 22 '13 at 9:07
    
I think I find out that I am the db owner using the Microsoft SQL Server Management Studio. So, I can conclude that I do not need to grant myself any permission since I am the owner of the ASPState database. right? –  Jack Feb 22 '13 at 9:21
    
You're right. If you're the db owner you don't need any other database permissions. –  Marian Feb 22 '13 at 9:27
    
@Thanks Marian, probably you would like to put your answer and include what commands to be type into sqlcmd so that one know what permissions he has on the database. –  Jack Feb 22 '13 at 9:29

1 Answer 1

up vote 1 down vote accepted

As per the comments, if you're already the db owner of that database, than you don't need to grant any permission for the db.

Now, in order to find out what specific permissions you have, you can use the following queries:

  • find permissions on specific db:

    USE AdventureWorks2008R2;
    SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
    GO
    
  • find permissions on all the server:

    SELECT * FROM fn_my_permissions(NULL, 'SERVER');
    GO
    

See some other info here: Find out SQL Server Logins and associated Server Roles, on MSDN forum.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.