Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Now that does not look like the right SQL for a system sproc....

    Hi SQLers

    I'm in an unusual position of using a SQL box I'm not an admin of. To make it worse, it is SQL 2k. I haven't used this in anger for over a year. I don't even have the 2k tools installed and I'm just using SSMS.

    sp_adduser ain't working. I've nosed around in master and the reason why is somebody, for some reason, has created a sproc called (you guessed it) master.dbo.sp_adduser.

    I can't poke around as I would like and I don't have a 2k instance to bugger about with. Correct me if I am wrong but if there is some user defined master.dbo.sp_adduser sproc then there can't be a system sproc to add users? CREATE USER is SQL 2k5 too right? Users do get created by EM by the team managing the box so what command does this use? Anyone know or (flutters eyelashes) fancy running a profiler trace on EM to see?

    I want to convert my SQL 2k5 security objects script for SQL 2k. I can create the objects in SSMS so not critical.

    "Overriding" system sprocs is not something I would ever do and I have no equivalent testing environment so any help highly appreciated

    Cheers
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is this what you're looking for
    Code:
    create procedure sp_adduser
    	@loginame       sysname,	    -- user's login name in syslogins
    	@name_in_db     sysname = NULL, -- user's name to add to current db
    	@grpname		sysname = NULL  -- role to which user should be added.
    as
        -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    	set nocount on
    	declare @ret        int
    
        -- LIMIT TO SQL/NT USERS IN SYSLOGINS (BCKWRD COMPAT ONLY!)
    	if not exists (select * from master.dbo.syslogins where loginname = @loginame
    			and (isntuser = 1 or isntname = 0))
            and @loginame <> 'guest'
        begin
            raiserror(15007,-1,-1,@loginame)
            return (1)
        end
    
    	-- VALIDATE THE ROLENAME --
        if @grpname is not null and
    	   not exists (select * from sysusers where name = @grpname and issqlrole = 1)
        begin
    	    raiserror(15014,-1,-1,@grpname)
    	    return (1)
        end
    
        if @name_in_db is null
            select @name_in_db = @loginame
    
    	-- In Hydra only the user dbo can do this --
        if (not is_member('dbo') = 1)
    	begin
    	    -- AUDIT FAILED SECURITY CHECK --
            dbcc auditevent (109, 1, 0, @loginame, @name_in_db, @grpname , NULL)
    		raiserror(15247,-1,-1)
    		return (1)
    	end
        else
        begin
            -- AUDIT SUCCESSFUL SECURITY CHECK --
            dbcc auditevent (109, 1, 1, @loginame, @name_in_db, @grpname , NULL)
        end
    
        -- ADD THE USER TO THE DATABASE --
        execute @ret = sp_grantdbaccess @loginame, @name_in_db OUT
        if (@ret <> 0)
            return (1)
    
        -- ADD USER TO ROLE IF GIVEN. NOP FOR 'public' --
        if (@grpname is not null) and (@grpname <> 'public')
        begin
            execute @ret = sp_addrolemember @grpname, @name_in_db
            if @ret <> 0
    		begin
    			-- ROLL BACK THE ABOVE sp_grantdbaccess --
    			if @name_in_db = 'guest'
    				update sysusers set status = status & ~2, updatedate = getdate()
                                where name = 'guest'
    			else
    				delete from sysusers where name = @name_in_db
                return (1)
    		end
        end
    
        -- RETURN SUCCESS --
        return (0) -- sp_adduser
    
    GO
    ???
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I didn't think it was but actually that would probably do it. I didn't realise sp_adduser was a wrapper for (amongst other stuff) sp_grantdbaccess.

    Now I come to think on it, does EM use SMO to create objects? If so knowing what it did would be useless to me.

    Thanks George
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    http://msdn2.microsoft.com/en-us/lib...5(SQL.80).aspx

    (that's more for future readers, obviously, because I know that BoL is in your favourites )
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't need a SQL Server security 101 George

    I read through that sproc carefully. Not sure I have permissions or indeed want to find out if I can write to the sysusers table directly on a live SQL 2k box. I might just stick with the GUI for now. I shouldn't have too many deployments to make to it....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I updated my post with an explanation for the link dumping before you posted back!
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah.. yes... ahem in my favouri....

    I like to use BoL locally for performance reasons. I've got 2k5 installed but not 2k. Afterall - what kind of ludite still runs 2k?

    .....sorry George - you're still running what version? 6.5? Well obviously that version is still viable
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Low blow sir, low blow.

    One day (2015?) I'll be on 2005 and then nobody will stop me! bwahha
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Low blow sir, low blow.
    I strike no other kind
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    All's fair in love, war, and forum posting.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You are somewhat correct in your assumption, Pootle. In SQL 2000 and below (for anyone who may be sentenced to such lower versions by crimes in their past lives), a system stored procedure is defined as a procedure the name of which begins with "sp_", and exists in the master database. Stored procedures in user databases could be named with sp_, BUT, if they happened to be named the same as a system stored procedure, the system stored procedure would take precedence. So, if you created a procedure in the pubs database called 'sp_help', then ran 'exec sp_help' in the pubs database, you would get the output of the system stored procedure. You would need to fully qualify the procedure name with the three part name, in order to invoke the user stored procedure sp_help. In SQL 2005 these things have changed a bit. Now, the system stored procedure takes precedence no matter how you address your stored procedure.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Except the user defined sp_adduser is in the master database
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If it was created with a sysadmin account, it is now a system stored procedure. User stored procedures in the master database would have to begin with something other than "sp_".

Posting Permissions

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