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 |
|
im1dermike
Posting Yak Master
222 Posts |
 Posted - 2008-10-16 : 14:13:54
|
| Hi. I created a login specifically for read access to my database. I just want to use the login for query results. Some queries, though, use functions and it's throwing an error in my program. I currently have the database role membership set to db_datareader and public. What other membership do I need to check to allow functions to be accessed?Thanks! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-16 : 14:17:24
|
| You need to give select or Execute permission depending on type of user-defined-functions . Rightclick functions - properties-permissionoruse Grant select or execute on schema.function to User |
 |
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2008-10-16 : 14:31:53
|
| I'm trying grant execute on schema.Fiscal_Year_Begin to MGSread, but it says "Cannot find the schema 'Convert_SSN', because it does not exist or you do not have permission." |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-16 : 14:40:37
|
quote:
Originally posted by sodeep
You need to give select or Execute permission depending on type of user-defined-functions . Rightclick functions - properties-permissionoruse Grant select or execute on schema.function to User
Can you right click that function and give permission? Do you need give for all functions? |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2008-10-16 : 14:44:26
|
| Well I thought it best to do it via a query because there are two logins I have to do it to and 6 functions each. If it's easier to do it to all functions, that's fine and probably better. |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2008-10-16 : 14:50:12
|
| I also have to run it on 4 databases. This is why I wanted to do it in a query b/c I can just replace the user and switch databases quickly. |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2008-10-16 : 14:52:41
|
| I just took out "schema" from the query i pasted above and it works. Thanks. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-16 : 18:52:59
|
| You can give permissions to all functions like this:Table-Value Function and Inline-Functionselect 'Grant select on ' + name + ' to' + ' username(SQL Login)' --domain/login(Windows account)from sys.sysobjects where type in ('TF','IF')Scalar-Functionselect 'Grant Execute on ' + name + ' to' + ' username(SQL Login)'--domain/login(Windows account)from sys.sysobjects where type in ('FN')and run the output |
|
|
|
|
|
|
Quick database role question