Re: sp_revokedbaccess
From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 01/01/04
- Next message: Bronek Kozicki: "Re: SQL Server and Visual Source Safe"
- Previous message: zahid: "ptotect against salmmers and worms"
- In reply to: Lisa: "Re: sp_revokedbaccess"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Thu, 1 Jan 2004 10:29:28 -0600
This proc isn't the version installed with SQL Server so it looks line
someone with sysadmin rights on your test system changed it. You can find
the source for this system proc in the procsyst.sql file in your SQL Server
installation folder (e;g. C:\Program Files\Microsoft SQL
Server\MSSQL\Install).
To install the original version, copy the sp_revokedbaccess CREATE statement
from procsyst.sql into the script below and run from Query Analyzer.
USE master
EXEC sp_configure 'allow',1
RECONFIGURE WITH OVERRIDE
GO
if object_id('sp_revokedbaccess','P') IS NOT NULL
drop procedure sp_revokedbaccess
GO
-- insert sp_revokedbaccess CREATE statement here
GO
EXEC sp_MS_MarkSystemObject N'sp_revokedbaccess'
EXEC sp_configure 'allow',0
RECONFIGURE WITH OVERRIDE
GO
--
Happy Holidays.
Dan Guzman
SQL Server MVP
"Lisa" <anonymous@discussions.microsoft.com> wrote in message
news:09f601c3cfb9$c41bee10$a401280a@phx.gbl...
> Here is the code, the primary difference being the
> existence of @error:
>
> CREATE procedure sp_RevokeDBAccess
> @name_in_db sysname,
> @error int OUT
> as
> Set @error = 0
> -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
> set nocount on
> declare @uid smallint,
> @ret int
> -- CHECK PERMISSIONS --
> if (not is_member('db_accessadmin') = 1) and
> (not is_member('db_owner') = 1)
> begin
> Set @error = 15000
> --print '@error = ' + convert ( varchar (6), @error )
> return (1)
> end
> -- DISALLOW USER TRANSACTION --
> set implicit_transactions off
> IF (@@trancount > 0)
> begin
> Set @error = 15002
> --print '@error = ' + convert ( varchar (6), @error )
> return (1)
> end
> -- CHECK IF SYSUSER EXISTS --
> select @uid = uid from sysusers where name =
> @name_in_db
> and (issqluser = 1 or isntname =
> 1) -- is droppable entity
> and (name <> 'guest' or hasdbaccess =
> 1) -- special case guest
> if @uid is null
> begin
> Set @error = 15008
> -- print '@error = ' + convert ( varchar (6),
> @error )
> return (1)
> end
>
> if @error = 0
> begin
> exec @error = sp_revokedbaccess @name_in_db
> end
> GO
- Next message: Bronek Kozicki: "Re: SQL Server and Visual Source Safe"
- Previous message: zahid: "ptotect against salmmers and worms"
- In reply to: Lisa: "Re: sp_revokedbaccess"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
