SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 no permission to grant select?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

784 Posts

Posted - 05/02/2013 :  12:18:53  Show Profile  Reply with Quote
Hi,

I got this following message that I couldn't figure out.

use SP_Logging;
go
grant select on SP_Logging to sharepointuser;
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'SP_Logging', because it does not exist or you do not have permission.

The same error happens on other sp DBs as well.

Here are related background information:
This is the step 3) of the task of adding a new sql user read only for connecting Sharepoint Web part to the SharePoint 2013 databases.

I am the sysadmin on the server instance(sql 2012), running ssms from the box through remote desktop.

Both step 1) create login with password and step 2) create user sharepointuser for login completed.

I could see/select from the db. Also, I run next with success.

EXEC sp_addrolemember db_datareader, sharepointuser;
go

By the way, I am still trying to figure out which db(s) I need to grant the read access to. I am guessing wss content, profile, social, or mysites. Of cause it depends what these web parts do.

Thanks!

Edited by - Hommer on 05/02/2013 12:19:33

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 05/02/2013 :  12:34:38  Show Profile  Reply with Quote
What kind of object is is SP_Logging? If it is a view or table, you should be able to grant select permission, assuming you have the privileges to grant such permissions. If it is a stored procedure (which it may be - many people prefix stored procedure names with a "sp" (which is not a good practice - but that is for another day), then grant execute rather than grant select.

Another possibility is that SP_Logging might be in a schema different from the default schema. If so, you will need to prefix the schema name. You can find the schema name (among other things) from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.VIEWS
Go to Top of Page

Hommer
Aged Yak Warrior

784 Posts

Posted - 05/02/2013 :  12:44:01  Show Profile  Reply with Quote
It is a database. Am I working on the wrong object?

sp stands for SharePoint in this case. :)

Is where a way to grant permission to every securable in a given db?

Edited by - Hommer on 05/02/2013 12:46:48
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 05/02/2013 :  14:42:16  Show Profile  Reply with Quote
I thought I wrote a reply a while ago, but I think the dog must have eaten it. You don't need to grant select permissions explicitly if you have added the user to the db_datareader role. You can see the permissions that can be granted on each type of object here: http://msdn.microsoft.com/en-us/library/ms191291.aspx.

If you want to give access to every object I don't know of a way to grant that other than grant on each object, or give db_owner database role or sysadmin server role.
Go to Top of Page

Hommer
Aged Yak Warrior

784 Posts

Posted - 05/05/2013 :  10:26:58  Show Profile  Reply with Quote
Thanks!

I understand "EXEC sp_addrolemember db_datareader, sharepointuser" served the same purpose. Now I know where went wrong with my grant.

SharePoint 2013 has dozen of databases. I found next link helpful. http://technet.microsoft.com/en-us/library/cc678868.aspx#Sec2

quote:
Originally posted by James K

I thought I wrote a reply a while ago, but I think the dog must have eaten it. You don't need to grant select permissions explicitly if you have added the user to the db_datareader role. You can see the permissions that can be granted on each type of object here: http://msdn.microsoft.com/en-us/library/ms191291.aspx.

If you want to give access to every object I don't know of a way to grant that other than grant on each object, or give db_owner database role or sysadmin server role.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000