| Author |
Topic |
|
clanglais
Starting Member
5 Posts |
 Posted - 2008-07-24 : 08:08:10
|
| I have run into this instance on two databases now. The users had the ability to execute a UDF or SP that did not have a group or user listed under the permissions for that UDF or SP. Then suddenly the UDF or SP will no longer execute for the user. I just had this happen where in the morning the user could execute and then by the afternoon could no longer unless the user was added to the permissions.Now in the time between being able to execute and not, nothing had been changed in the SQL servers settings and nothing changed in active directory either. No patches had been installed. Why would a udf or sp exec for months and then suddenly change in the span of hrs?I know it is correct to have permissions/roles setup for executing and these were just setup for now for some basic functions of like get first day of month, but I am disturbed by the server just suddenly doing this without warning or changes being made. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-24 : 08:54:12
|
| "The users had the ability to execute a UDF or SP that did not have a group or user listed under the permissions for that UDF or SP."Didn't get this part? Can you elaborate? |
 |
|
|
clanglais
Starting Member
5 Posts |
Posted - 2008-07-24 : 12:50:20
|
| When you open the properties for the UDF and go to the Permissions section, the Users or roles section was empty. A user was able to have a query from a VB application execute that the function was part of a the select query. Later that day no user except for developers could run the same query. There were no changes to the app or SQL during that time interval. Once we added a group to the Users and roles section that is based on the AD group containing the users of the application they could execute the query. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-24 : 13:45:03
|
| Tyen may get permission at schema level before, did you check that? |
|
|
|
clanglais
Starting Member
5 Posts |
Posted - 2008-07-24 : 14:21:07
|
| No permissions were setup through any of the schemas. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-24 : 14:27:09
|
| How did user get permission then? Was in db_owner role or sysadmin role before? |
|
|
|
clanglais
Starting Member
5 Posts |
Posted - 2008-07-24 : 14:58:42
|
| A server security login for the AD group was setup on the server. It was given the server role of public and mapped to the corresponding database that would the users were querying. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-07-24 : 15:46:44
|
| Check the Created or Last modified date on the procedure. I have seen in SQL 2000 that some functions and procedures would be dropped and recreated under less than intuitive circumstances in Enterprise Mangler. |
|
|
|
clanglais
Starting Member
5 Posts |
Posted - 2008-07-25 : 07:34:12
|
| Create date on one affected is still January 08 and this happened 2 days ago :-/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-26 : 21:20:53
|
| >> mapped to the corresponding database that would the users were querying.That will not grant any permission unless you put it in proper db schema or role. |
|
|
|
|
Permissions on UDF and SP suddenly required