Cannot find the object ERROR while running grant

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Cannot find the object ERROR while running grant

Author

Topic

chikku6
Starting Member

9 Posts

Posted - 2014-07-11 : 04:32:01

Hello,

I have program which the end user runs and creates tables on the fly as temporary ones. We run the following query

CREATE TABLE temp_Data_extmama1168829562 (
d0 [int] NOT NULL,
d1 [int] NOT NULL,
d2 [int] NOT NULL,
d3 [int] NOT NULL,
d4 [int] NOT NULL,
d5 [int] NOT NULL,
d6 [int] NOT NULL,
d7 [int] NOT NULL,
data [image] NOT NULL,
state [int] NOT NULL DEFAULT 0,
last_modified [timestamp] NOT NULL,
unique_field [uniqueidentifier] NOT NULL DEFAULT newid(),
CONSTRAINT ID_PK_temp_Data_extmama1168829562 PRIMARY KEY (state, d0, d1, d2, d3, d4, d5, d6, d7 )
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GRANT SELECT ON temp_Data_extmama1168829562 TO DataReaders
GRANT INSERT, UPDATE, DELETE ON temp_Data_extmama1168829562 TO DataWriters.

the table seems to be created when i check in sql server studio and i suspect we get "Cannot find the object 'temp_Data_extmama1168829562', because it does not exist or you do not have permission" error when the grant statements are run.

Can anyone help me ?

ps:- the query seems to work when run as db_owner ( what permissions am I missing here).

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-11 : 08:59:15

Probably the users have different default schemas, so the tables are getting created in a differnt schema. You can check by running this query:

SELECT
	s.NAME AS SchemaName,
	t.NAME AS TableName
FROM
	sys.tables t
	INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE
	t.NAME = 'temp_Data_extmama1168829562' -- Your table name

Assuming that the schemaname is ABCD, then you should change the grant statements to

GRANT SELECT ON ABCD.temp_Data_extmama1168829562 TO DataReaders

Alternatively, create the table with explicit schema name specified (which is always a good idea anyway)

CREATE TABLE dbo.temp_Data_extmama1168829562 ( 
d0 [int] NOT NULL,
....

chikku6
Starting Member

9 Posts

Posted - 2014-07-11 : 10:03:19

I can see the table created properly under the correct schema in sql server management studio. so i don think they are getting created in different schemas :)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-11 : 14:09:50

Assuming you have granted minimal permissions to the user - i.e., they have only the required permissions to create the table - you will also need to "grant select with grant" on that table in order for the user to be able to grant select permissions. But that poses a problem - because you are creating the table and then trying to grant permissions all in the same batch. And, you cannot grant yourself the permission to select.

So two options:
1. Wrap all of this in a stored procedure and use "EXECUTE AS". This way, when your less privileged user calls the stored procedure, the stored procedure will be executed in the context of a user who has permissions to create and grant permissions on the table. Here is documentation and examples of how to use EXECUTE AS in a stored proc: http://msdn.microsoft.com/en-us/library/ms188354.aspx

2. Grant the user permission on the next higher level of securables - i.e., on the schema. You can do this using the following query:

GRANT CONTROL ON SCHEMA::[dbo] TO [YourLessPrivilegedUser] WITH GRANT OPTION

. But that will give them permission to do a lot of things - e.g., they will be able to grant permissions to select from ANY table in the dbo schema.

So my recommendation would be option 1, unless someone else can post a brilliant solution to your problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources