How could this have happened ??

Hi everybody!

Something strange happened to one of my SQL Databases.
One of my User Defined Funcitions has lost ALL its roles permissions!

I created this UDF a long time ago and I assigned permissions to some specific Roles to run the UDF. It worked fine until today. All of a sudden there were no permissions for any role for that UDF. I am the only Admin for the DB.

I reassigned the roles permissions and it seems to be working fine now.
How could this happen ? has anyone here experienced something like this before ?

Thanks!Basic questions - How often is it accessed ? Have you noticed any other settings modified - I am thinking of sql injection here - it is a remote possibility that someone could have breached your system. Have you done any replication/copy database/copy objects between other servers ?|||Originally posted by rnealejr
Basic questions - How often is it accessed ? Have you noticed any other settings modified - I am thinking of sql injection here - it is a remote possibility that someone could have breached your system. Have you done any replication/copy database/copy objects between other servers ?

It is accessed on a daily basis (about 25 users). Intranet users only (no external logins). Also, no replication/db copy between other servers.|||Why don't you post the udf...|||You are sure you are the only admin? Try running

sp_helpsrvrolemember sysadmin
and
sp_helpsrvrolemember securityadmin

If any other names show up in these, you may have a problem. Also check on sp_helpdb (your database) who is listed as the owner of the database? And check sp_helprolemember db_owner as well. Any of these folks could tamper with permissions. Some of them could drop/rebuild the function, which strips permissions.

To check if the procedure has been dropped/rebuilt. Run
select crdate
from sysobjects
where name = 'functionname'

If crdate (creation date) is recent, you may have your culprit there.|||Or it's the Miracle thing again....|||Is the server on 34th street, Brett? ;-) I would hate to hear that SQL security is as good as say...MS Word.

http://www.techworld.com/news/index.cfm?fuseaction=displaynews&NewsID=845|||Nice article - Just call me Kris ... Kringle that is.

Seriously, as already mentioned check to see if other admins exist.|||Originally posted by MCrowley
Is the server on 34th street, Brett? ;-) I would hate to hear that SQL security is as good as say...MS Word.

http://www.techworld.com/news/index.cfm?fuseaction=displaynews&NewsID=845

Cool...as or I like to say...Microsoft Swiss Cheese

and as far as miracles go...

I've known too many developers who don't realize it's their fault...

It's gotta be the computer...or It's a Miracle

How did all that data dissapear from the table...I didn't do ANYTHING...|||Oh and btw...I bet the udf was dropped and recompiled...

takers?|||That's about where I would start looking, Brett.|||"I didn't truncate that table" - oops :-)

Brett - you are correct about that one - I actually had a developer truncate a table - to only ask what happened... However, dropping permissions is more of a cognitive action - I least I hope ... Especially since this udf is accessed daily, you would have a shorter time span to recollect your thoughts about what you did that might have affected it. I still would not be surprised if it is a microsoft miracle because I have seen this behavior before (but only with replicated objects).

Dropping and recompiling would do it - however, the create date would have changed.|||[CTRL-E]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_MI]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_MI]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION udf_MI
(@.x varchar(256))
RETURNS char(1)
AS
BEGIN
DECLARE @.y char(1)
SELECT @.x = REPLACE(@.x,'.','')
SELECT @.y = CASE WHEN PATINDEX('% [a-zA-Z] %', @.x) > 0
THEN SUBSTRING(@.x,PATINDEX('% [a-zA-Z] %',@.x)+ 1,1)
ELSE NULL
END
RETURN @.y
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ooooooooooopppppppppppppppppppppssssssssssssssssss|||Originally posted by MCrowley
You are sure you are the only admin? Try running

sp_helpsrvrolemember sysadmin
and
sp_helpsrvrolemember securityadmin

If any other names show up in these, you may have a problem. Also check on sp_helpdb (your database) who is listed as the owner of the database? And check sp_helprolemember db_owner as well. Any of these folks could tamper with permissions. Some of them could drop/rebuild the function, which strips permissions.

To check if the procedure has been dropped/rebuilt. Run
select crdate
from sysobjects
where name = 'functionname'

If crdate (creation date) is recent, you may have your culprit there.

Yep! crdate is yesterday's date, that's when i found out that the permissions were dropped! (I originally created this function months ago!).

Is there a way to find out who dropped it, if any ?|||Since you are the only admin, YOU dropped it! Eh? ;)

Seriously, you may be the only admin, but does anybody else have a login with dbo priveleges?

blindman|||Originally posted by blindman
Since you are the only admin, YOU dropped it! Eh? ;)

Seriously, you may be the only admin, but does anybody else have a login with dbo priveleges?

blindman

I am the only Admin .. and UNLESS I was drunk (doubtful since I don't drink) or I was walking while asleep (which is also doubtful :o) .. )

Seriously, I am sure I have not dropped the function. I am still puzzled!|||Well, maybe it is time you have a drink :-). Is it identical to your original script ... Is it stored in a script ? If you did not drop/recreate the udf, then I would expect differences between the two. Also, as blindman already mentioned start to look for users that would have access to drop/recreate the udf.|||Originally posted by rnealejr
Well, maybe it is time you have a drink :-). Is it identical to your original script ... Is it stored in a script ? As blindman already mentioned start to look for users that would have access to drop/recreate the udf.

END OF THE DILEMMA!

This might sound obvious to some of you. But it sure is new to me (with my very limited experience with SQL Server).
What happened is that I modified the User Defined function yesterday. And that (for some reason that I still don't understand) deleted all the permissions. Or more accurately it dropped and recreated the function.

Now to make sure that was the case, I created a test function and test stored procedure. When I modified the stored procedure nothing was lost (the permissions were still there) but when I modified the function, all permissions disappeared (function dropped/recreated automatically).

Is this something that SQL server does by definition or is it strange to you guys too ?

Thanks!|||Holy-moly !!! That was critical information you left out - that you were modified the function !!! Anytime you modify something and that thing is altered from its normal behavior means that what you did caused it. Kudos to mcrowley for mentioning it for investigation.

How were you modifying the udf ?|||You must have been modifying the create script version of the udf that included a drop as illustrated by brett. alter function perserves the permissions.|||Originally posted by rnealejr
You must have been modifying the create script version of the udf that included a drop as illustrated by brett. alter function perserves the permissions.

Yes, I am using the create script version of the udf. But it does not include a drop. Here is what my udf looks like:

---
CREATE FUNCTION UFName (@.SomeVar int)
RETURNS @.SomeTab TABLE (--some fields)

AS
BEGIN
-- some code ..

RETURN
END
----



--|||If you did not first drop the udf then sql server would raise an error that this object name already existed in the database. If your scripting options are set to remove the referenced object, then you will be dropping/creating the udf. Anyway, in the future use alter function.|||"If you did not first drop the udf then sql server would raise an error that this object name already existed in the database."...unless the modifed script was created by a different login. Check to make sure that you don't have multiple copies of the function.

Also, if you modify the code by opening the UDF directly through Enterprise manager, you will see the code as you presented it, and Enterprise Manager will execute the create statement when you exit and save, but it will also execute a drop beforehand as well as record and reapply any permissions assigned to the function. If you modify the procedure through, say, query analyzer, you have to perform these tasks yourself.

Next time you need to modify it, script the UDF through Enterprise Manager's scripting tool, making sure to check the boxed to include code for dropping and for permissions. Then modify the scripted code and execute it, and then you are back in business.

blindman|||The problem is that multistatement table-valued functions do not behave properly when altered. If you alter a multistatement table-valued function, you will lose the permissions assigned to that udf. When editing a udf in em, you are performing an alter function not create - so the same behavior will occur. For all other udfs, table and scalar - the permissions are not affected.|||That's a new one on me! Gotta try that out.

Any idea why the UDF's would be handled differently than stored procedures?

blindman|||I just ran a test for the situation described, and sure enough, - rnealejr is right!|||Originally posted by Brett Kaiser
Or it's the Miracle thing again....

A sign from Edgar!

I always DROP

Then I get off the floor and order another one...|||rneal,

How did you find this? Is it mentioned in any documentation? It's strange enough the it doesn't apply to scalar functions, but if it only applies to multi-statement table functions and not simple select table functions then that is really odd.

blindman|||I don't think it matters how he found it, the point is it's there and no more ALTER should be done against them until M$ fixes it :)|||It matters to me, how he found it, because if there's some SQL Server documentation from Microsoft that I'm not aware of and that my have information that can help me prevent future problems, then I'd like to see it.

But hey, I'm just like that...|||Originally posted by blindman
It matters to me, how he found it, because if there's some SQL Server documentation from Microsoft that I'm not aware of and that my have information that can help me prevent future problems, then I'd like to see it.

But hey, I'm just like that...

Are you kidding? MS publishing APARS?

Please every "undocumented feature" is found by discovery...there's got to be thousands...|||You contradict yourself again. But if it really matters, follow your own advice, - experiment!

[blinds off]
create function...
go
grant select on...to guest
go
alter function...
go
setuser 'guest'
go
select * from <function>
go
setuser
go
[blinds on]|||You are so clueless, rdjabarov.

[jabbers on]

The idea is to see if there is anything ELSE in the documentation that might be important to know. It's a concept called "being prepared" that persons involved in IT administration have to be aware of. If you want to advance in this field, you need will need to actively seek out new sources of knowledge.

[jabbers off]|||Not again...

It's neither...

MS just doesn't do a good enough job in this area...|||FYI

This is a known bug within sql server and is expected to be corrected with the next release. More microsoft miracles.

Also, as a reminder, lets keep the forum civil - I don't want to have to remove my belt ... :-)|||Originally posted by rnealejr
FYI

This is a known bug within sql server and is expected to be corrected with the next release. More microsoft miracles.

Also, as a reminder, lets keep the forum civil - I don't want to have to remove my belt ... :-)

Well...

if you're gonna play moderator...then who is JonathanB...and why is a web developer moderating a SQL Sever forum?|||And how the H_LL did the Eagles pull that off?|||This is not the first time blindmand is playing ugly. But we're all used to it by now. So we'll just ignore it ... again :)|||As in life, if you think someone is being inappropriate - just ignore it. That will normally take care of it, otherwise affirming the response will just aggravate the situation.

Anyway, more information about the real issue for this post. I just talked to ms and it will be fixed in yukon (not the next sp). Also, I requested that a kb article be created for this bug. If a kb article is entered I will notify dbforums. Also, this bug was reported after sp3 was released.

One item of note is that if you run profiler, you will notice the error:

Error: 208, Severity: 16, State: 1

When you alter a multi-statement udf (this does not appear for the other udfs).|||For the Eagles ... Every game this weekend was awesome. I can not remember a time when so many teams have been so evenly matched.|||Brett - Each forum has moderators. But there are a couple of moderators who span all forums. We have recently had a couple of advertising/spam issues - if you see it, please send me a message. Developer, dba, sa ... Am there, doing it.|||That's sad, especially considering the fact that Beta 1 of Yukon barely has any functional modules, and BOL can hardly be called finished.|||I was disappointed as well since another sp release will probably beat the yukon release. I am more bothered by the fact that they did not submit it as a kb article.|||Originally posted by rnealejr
Brett - Each forum has moderators. But there are a couple of moderators who span all forums. We have recently had a couple of advertising/spam issues - if you see it, please send me a message. Developer, dba, sa ... Am there, doing it.

Hey thanks...

All good games...AND history...

first ever playoff game w/out a punt...

I was hoping for an Indy Packer SB...I still can't believe the Pacl blew it...

should never have gone in to OT...|||When Favre threw that interception in OT, I could have just kicked my tv ... That was the worst decision I have ever seen him make - he might as well just punted the darn ball.|||Originally posted by rnealejr
When Favre threw that interception in OT, I could have just kicked my tv ... That was the worst decision I have ever seen him make - he might as well just punted the darn ball.

You a cheese head?

Where are you from?

Being a Giant Fan (all sympathy cards and wishes welcome) I wanted ANYONE to knock off philly...

Bussy of mine is from cheesland though...looked like he wanted to cry last night...

Hey some good football on tap next weekend...|||No - seeing a great quarterback like Favre throw a ball so recklessly in the air had me befuddled.|||Originally posted by rnealejr
No - seeing a great quarterback like Favre throw a ball so recklessly in the air had me befuddled.

I think the receiver didn't read the blitz...

he was suppose to come off his route...

Still...they were running it down their throat...stick with what works...

But come on 4th and 26?

http://www.nj.com/eagles/times/index.ssf?/base/sports-0/10739020319570.xml|||Playoffs are full of miracles - the immaculate reception, hail mary, the catch ...|||wow, football, men are talking...where the margarita mixer?|||Originally posted by ms_sql_dba
wow, football, men are talking...where the margarita mixer?

Why do I get a specific gender slant?

I know...let's talk about Ice dancing...