• 歇后语大全| 百家姓| 丰胸美乳| 丰胸新闻| 明星美胸| 美胸图片| 丰胸按摩| 丰胸饮食| 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;、版权及其它问题,请即与本站联系,我们将尽快予以更正或删除。

    河南2018619体彩11选5
  • pk10直播 彩票书籍 十一运夺金开奖视频选号技巧 福建十一选五历史开奖 西甲录像在线观看 双色球蓝球大小区间 中国十大惜真钱 广西11选5电视直播 青海11选5电子走势图 福彩3d开机号和试机号今天晚上有 湖南彩票报 4月12日竟彩推荐 网球王子2 九龙六合彩 中国福彩3d开奖结果