Server的安全机制及DBA如何管理.docVIP

Server的安全机制及DBA如何管理 ) --WITH ENCRYPTION AS DECLARE@result INT DECLARE@user_exists INT DECLARE@execute_sp_role NVARCHAR(100) DECLARE@create_table_role NVARCHAR(100) DECLARE@object_name NVARCHAR(120) DECLARE@object_type NVARCHAR(10) DECLARE@sql VARCHAR(200) DECLARE@error_msg VARCHAR(100) BEGIN --check input paramters. IF(@login_name IS NULL)OR(LTRIM(@login_name)=)OR(lower(@login_name)=sa) BEGIN RAISERROR(The login_name can not be null or sa.,16,1)WITH NOWAIT RETURN 1 END IF NOT EXISTS(SELECT name FROM master.dbo.sysdatabases WHERE name=@db_name) BEGIN SET@error_msg=The database+@db_name+does not exits. RAISERROR(@error_msg,16,1)WITH NOWAIT RETURN 1 END --if user does not exits,create it IF NOT EXISTS(SELECT*FROM master.dbo.syslogins WHERE loginname=@login_name) BEGIN SET@user_exists=0 IF(@password IS NULL)OR(@password=) BEGIN RAISERROR(For new application user,the password can not be null.,16,1)WITH NOWAIT RETURN 1 END EXEC sp_addlogin@loginame=@login_na me,@passwd=@password,@defdb=@db_name,@deflanguage=@language IF@ERROR 0 RETURN 1 END ELSE BEGIN SET@user_exists=1 IF(@password IS NOT NULL)AND(LTRIM(@password)) BEGIN EXEC sp_password@old=null,@new=@password,@loginame=@login_name IF@ERROR 0 RETURN 1 END END --create the execute sp role if it does not exists SET@execute_sp_role=Ndb_procexecutor IF NOT EXISTS(SELECT*FROM dbo.sysusers WHERE name=@execute_sp_role AND issqlrole=1) BEGIN EXEC sp_addrole@rolename=@execute_sp_role IF@ERROR 0 RETURN 1 END --create the create table role if it does not exists SET@create_table_role=Ndb_createtable IF NOT EXISTS(SELECT*FROM dbo.sysusers WHERE name=@create_table_role AND issqlrole=1) BEGIN EXEC sp_addrole@rolename=@create_table_role IF@ERROR 0 RETURN 1 END --grant privilege to the role DECLARE cur_sp_fun CURSOR FAST_FORWARD FOR SELECT name,xtype FROM sysobjects WHERE xtype in(NP,NFN,NIF,NTF) AND uid=(select uid from sysusers where name=USER_NAME()) OPEN cur_sp_fun FETCH NEXT FROM cur_sp_fun INTO@object_name,@object_type WHILE@FETCH