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.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2013-05-02 : 12:18:53
|
| Hi,I got this following message that I couldn't figure out. use SP_Logging;gogrant select on SP_Logging to sharepointuser; Msg 15151, Level 16, State 1, Line 1Cannot 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; goBy 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! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-02 : 12:34:38
|
| 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 |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2013-05-02 : 12:44:01
|
| 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? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-02 : 14:42:16
|
| 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. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2013-05-05 : 10:26:58
|
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#Sec2quote:
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.
|
|
|
|
|
|
|
|
|
no permission to grant select?