June 6, 2012 at 9:56 pm
Hi ,
I have SQL Server 2008 r2 ent edition 64 bit RTM on windows server 2008 r2 sp1 64 bit enterprise edition.
I had a database called TEST on a named Instance called TESTINSTANCE
I had created a login ( domain user ) called TESTDOMAIN\TestUser on the instance named TESTINSTANCE.
TESTINSTANCE is in the TESTDOMAIN.
I had given permissions to execute two stored procedures (dbo.usp_sptest and dbo.usp_sptest1) to the user called TESTDOMAIN\TestUser in the TEST Database on the server TESTINSTANCE.
when i logged in using the user account TESTDOMAIN\TestUser into the server named TESTINSTANCE.
i was not able to view the stored procedures(dbo.usp_sptest and dbo.usp_sptest1) in the test database.
i want to execute the stored procedures named (dbo.usp_sptest and dbo.usp_sptest1) in the test database through the user account TESTDOMAIN\TestUser.
I have given read access on the objects which are inside the stored procedures(dbo.usp_sptest and dbo.usp_sptest1) to the user named TESTDOMAIN\TestUser in the test database
when i tried to access the stored procedures named (dbo.usp_sptest and dbo.usp_sptest1) in the test database it gives me the below error message.
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server,
Error: 229)
i had checked the web and applied a couple of options but none of them work.
i had given db_datareader permission , read permission on sys.extended_properties,GRANT VIEW DEFINITION on two stored procedures , read permission on the tables inside the stored procedures to the user named TESTDOMAIN\TestUser in the test database but that did not work
I had added TESTDOMAIN\TestUser to the public role in the test database.
After assigning public role to the user TESTDOMAIN\TestUser in the test database. when i went and check i could not find the user TESTDOMAIN\TestUser in the public role under the test database.
when i went and check the user under the security of the TEST Database by using the user named TESTDOMAIN\TestUser in the test database. i cant find him when i was logged in as the user named TESTDOMAIN\TestUser.
if i touch the tables , stored procedure or users under the TEST database. i was getting the same error mesage.
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server,
Error: 229)
when i logged in using sa . i can see that the user named TESTDOMAIN\TestUser exists in the TEST Database and i can execute the two stored procedures(dbo.usp_sptest and dbo.usp_sptest1) in the test database.
when i did a google search. it tells to me to grant database owner permission or sa permission to the user TESTDOMAIN\TestUser.
I don't want to give those permissions to the user named TESTDOMAIN\TestUser in the TEST database.
I check for the user named TESTDOMAIN\TestUser in the deny_datareader. i was not able to find the user named TESTDOMAIN\TestUser under the deny_datareader role.
when i was logged in as TESTDOMAIN\TestUser to the instance TESTINSTANCE when i check if have access to the test database . i dont see that access.
can you please advice how to fix this issue.
Thank You,
June 6, 2012 at 10:11 pm
I count not find any information relating to this in event viewer or SQL Server Logs
June 7, 2012 at 8:22 am
Hi ,
I have not dropped or altered the certificates in the master database. I have not altered any object in the resource database.
Thank You
June 7, 2012 at 8:23 am
Hi ,
Even when i had granted the database owner permissions to the user TESTDOMAIN\TestUser in the test database.. The user is till not able to see the stored procedures.
Thank You,
June 7, 2012 at 11:51 am
Hi ,
View definition on the schema also did not work for the user TESTDOMAIN\TestUser in the test database.
Thank You
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply