Page 1 of 4 123 ... LastLast
Results 1 to 15 of 51
  1. #1
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103

    Unanswered: 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!

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    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 ?

  3. #3
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why don't you post the udf...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Or it's the Miracle thing again.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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....ews&NewsID=845

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Nice article - Just call me Kris ... Kringle that is.

    Seriously, as already mentioned check to see if other admins exist.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....ews&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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh and btw...I bet the udf was dropped and recompiled....

    takers?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    That's about where I would start looking, Brett.

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    "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.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [CTRL-E]

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103
    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 ?

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •