Why does a user get a permissions error and how do I fix it?

norgrenit
norgrenit used Ask the Experts™
on
I am trying to give permissions to a contractor so that he can create tables, views, and stored procedures.  I assigned the user to the role db_owner, and he can do all that I mentioned above.  

The one weird thing is that when the user right-clicks on a view, and selects Script View As --> Create To --> New Query Editor Window, he gets the error, "Select permissions denied on object 'trigger_events', database 'mssqlsystemresource', schema 'sys' (Microsoft SQL Server, Error: 229)"

I have tried "grant select on sys.trigger_events to user" (user's username), and while the statement executes successfully, he still get's the same error.

He does not get this error when he uses the Alter To option.

Can you please help me so that he can use the Create To option?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
from here
http://social.msdn.microsoft.com/forums/en-US/vstsdb/thread/c6943d07-9467-46f5-9c6f-76b8d1b3e162/

This is caused by the fact you do not have the VIEW SERVER STATE permission. Despite the fact the SQL Server grants public access to the view syscomments in SQL Server 2005 does not mean you can read information from the meta data. Because syscomments is just a view over the new system catalog tables and functions, the access is checked down the line.

Please GRANT the user GRANT VIEW SERVER STATE TO [DOMAIN\user]
GO


msssqlsystemresource is the name of the resource database, which holds the definition of the of shared objects that used to live in master, this is decoupled in SQL Server 2005, so when a user adds objects to master, we can simply replace the resource database in for example a service pack and not remove your objects. This database is not visibile under normal operations, but if you go look in your data directory you will find the file names for the database

 mssqlsystemresource.ldf
 mssqlsystemresource.mdf

If you want to see what is in there, stop the server, copy the two files to something with a different name and attach the database with some name like resdb, now it is a normal database and you can look at it :)

Author

Commented:
Hello aneeshattingal:

Unfotaunately that did not work.  I ran the query :

use master
GRANT VIEW SERVER STATE TO [domain\user]

The statement was successful, but the user is still experiencing the problem

I do appreciate the explanation fo the mssqlsystemresource database.
CREATE PROC dbo.GrantPermission
   @user1 varchar(50)
AS

EXECUTE ('GRANT CREATE TABLE TO [' + @user1 + ']')
EXECUTE ('GRANT CREATE VIEW TO [' + @user1 + ']')
EXECUTE ('Grant create Procedure to [' + @user1 + ']')

RETURN
GO

Author

Commented:
I ended up deleting the user and recreating the user, and it worked.  I don't know what originally went wrong, but since you provided that excellent explanation on the msssqlsystemresource database, you get the points.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial