• 歇后语大全| 百家姓| 丰胸美乳| 丰胸新闻| 明星美胸| 美胸图片| 丰胸按摩| 丰胸饮食| QQmi| 图片素材| 个性签名| QQ网名| QQ头像| 空间日志| 好友印象| QQ分组

    sql server2000 未能找到存储过程 sp_droplogin

    分享到

    发布新新网络 | 发布时间2019-03-20 | 点击


    create procedure sp_droplogin @loginame sysname as declare @exec_stmt nvarchar(890) -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES -- set nocount on declare @sid varbinary(85) /*Create temp tables before any DML to ensure dynamic*/ -- CREATE TEMPORARY TABLES FOR LATER USE -- create table #db_list (dbname sysname collate database_default not null, user_name sysname collate database_default not null) create table #retval (job_count int not null) -- CHECK PERMISSIONS -- IF (not is_srvrolemember('securityadmin') = 1) begin dbcc auditevent (104, 2, 0, @loginame, NULL, NULL, NULL) raiserror(15247,-1,-1) return (1) end ELSE begin dbcc auditevent (104, 2, 1, @loginame, NULL, NULL, NULL) end -- DISALLOW USER TRANSACTION -- set implicit_transactions off IF (@@trancount > 0) begin raiserror(15002,-1,-1,'sp_droplogin') return (1) end -- VALIDATE LOGIN NAME (SQL LOGIN) -- select @sid = sid from master.dbo.syslogins where loginname = @loginame and isntname = 0 if (@sid is null) begin raiserror(15007,10,-1,@loginame) return(1) end -- CANNOT CHANGE SA ROLES -- else if @sid = 0x1 -- 'sa' begin raiserror(15405, -1 ,-1, @loginame) return (1) end -- CHECK IF @sid IS CURRENTLY LOGGED IN (ignore cached remote connections) -- if exists(select * from master.dbo.sysprocesses where sid = @sid and status != 'dormant') begin raiserror(15434, -1, -1, @loginame) return(1) end -- CHECK IF ANY DATABASES ARE OWNED BY LOGIN -- if exists(select * from master.dbo.sysdatabases where sid = @sid) begin raiserror(15174, -1, -1, @loginame) select 'Databases owned by login:' = name from master.dbo.sysdatabases where sid = @sid return(1) end -- COLLECT ALL INSTANCES OF USE OF THIS LOGIN IN SYSUSERS -- declare @dbname sysname declare ms_crs_dbname cursor local keyset for select name from master.dbo.sysdatabases open ms_crs_dbname fetch ms_crs_dbname into @dbname while @@fetch_status >= 0 begin if (has_dbaccess(@dbname) = 1) begin select @exec_stmt = 'use ' + quotename( @dbname , '[') + ' insert into #db_list (dbname, user_name) select N'+ quotename( @dbname , '''')+', name from sysusers where sid = suser_sid(N' + quotename( @loginame , '''') + ') ' exec (@exec_stmt) end else raiserror(15622,-1,-1, @dbname) fetch ms_crs_dbname into @dbname end deallocate ms_crs_dbname -- ERROR IF LOGIN USED AS USER IN ANY DATABASE -- if (select count(*) from #db_list) <> 0 begin raiserror(15175,-1,-1,@loginame) select 'Database name:' = dbname, 'User name:' = user_name, 'Mapping type:' = 'user' from #db_list order by dbname return (1) end -- VERIFY NO JOBS IN MSDB OWNED BY THIS LOGIN -- if db_id('msdb') is not null and object_id('msdb.dbo.sp_check_for_owned_jobs') is not null begin exec msdb.dbo.sp_check_for_owned_jobs @loginame, '#retval' if exists (select job_count from #retval where job_count > 0) begin declare @job_count int select @job_count = job_count from #retval raiserror(14248, -1, -1, @job_count) return (1) end end -- DELETE THIS LOGIN (ALSO DELETES REMOTE LOGINS MAPPED TO IT) -- delete from master.dbo.sysxlogins where sid = @sid -- FINALIZATION: SUCCESS/FAILURE MESSAGE if @@rowcount > 0 begin -- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE -- exec('use master grant all to null') raiserror(15479,-1,-1) return (0) end else begin raiserror(15007,10,-1,@loginame) return (1) end -- sp_droplogin GO


    最新留言

    客户服务

    Bussiness


    网站地图| sitemap
    • 分享到

    备案编号粤ICP备13013056号-1
    Copyright © 2009-2013 新新网络(www.30870654.com) All Rights Reserved. 中华人民共和国增值电信业务经营许可证

    ·1本站转载文章及论坛发帖仅代表原作者观点和立场并不代表本网赞同其观点和立场也不对其真实性负责
    ·2如果本站转载的文章?#24515;?#23481;版权及其它问题请即与本站联系我们将尽快予以更正或删除

    201861911ѡ5