System table permission

Question

System table permission

aabideen.z

SSC-Addicted

Points: 446
More actions
December 21, 2009 at 12:09 am

#214914

I have created table in SQL Server 2008. But, there is no entry I've found in SYSPROTECTS table for my newly created table.
For this, i have tried to give permission for this table. I'm getting error message:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information schema, sys, or yourself.
Even I tried as a Windows Authentication user.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

How exactly are you trying to grant permission? What's the script?

You don't need to explicitly grant permissions to a database owner or sysadmin. By default they have full rights to everything in the database (db owner) or server (sysadmin)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aabideen.z SSC-Addicted Points: 446 More actions · Answer 2

I have done the following steps in SQL server 2005 by using Microsoft SQL Server Management Studio Express:

create table temp_ins(column1 varchar(20))

(table created) (but not shown in the sysprotects table)

GRANT SELECT, INSERT, DELETE, UPDATE ON temp_ins TO User

(Permission Granted)

SELECT object_name(id) tbname FROM sysprotects

(Found the entry of newley created table)

------------------------------------------------------------------

In the same way, i have created table in SQL server 2008 by using Microsoft SQL Server Management Studio Express.

While giving Grant permission, getting the following error:

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

Please give me the solutio ASAP

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

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

Think this is self explanatory. What are u logging in as while doing this ?

"Keep Trying"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Are you currently logged in as 'User'? Is the table owned by 'User'?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aabideen.z SSC-Addicted Points: 446 More actions · Answer 5

yeah i'm logged in as "users" and the table owned "users".

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

The error message explicitly states that you cannot assign a permission to yourself. If you're logged in as a particular user you cannot grant a permission to that user. Furthermore, if the table is owned by a particular user, you cannot grant permission on that table to that user. It's not necessary, the owner owns the table.

I don't understand why you're trying to assign permissions in the first place. If you're logged in as a particular user and created the table as that user then by default you have permission on that table. So why try and assign permissions?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aabideen.z SSC-Addicted Points: 446 More actions · Answer 7

In Sql server 2008 , I have just created the table.As you told , It will automatically assign the default permissions. okay....accepted

But I have not yet get the entry in the SYSPROTECTS table.

In sql server 2005 I have to create the table and give all the permissions , then only I can get the entry in the SYSPROTECTS table.

Now Please help me out....

aabideen.z SSC-Addicted Points: 446 More actions · Answer 8

Problem resolved...

Error in User level creation..

Thanks for your answers...

I've created new user and i can give permission for my tables.