仓酷云

标题: MSSQL网站制作之怎样取得SQL SERVER2000数据库指定工具... [打印本页]
作者: 若相依    时间: 2015-1-16 22:32
标题: MSSQL网站制作之怎样取得SQL SERVER2000数据库指定工具...
限制,如果WHERE子句的查询条件里有不等号（WHEREcoloum!=），MySQL将无法使用索引。类似地，如果WHERE子句的查询条件里使用了函数（WHEREDAY（column）=），MySQL也将无法使用索引。server|工具|数据|数据库头几天看到有人问是不是能够便利的取得SQLSERVER2000指定工具的权限和指定USER的权
限。我写了一个存储历程，能够取得用户和脚色的权限。请人人协助测试一下。看看是
否另有BUG:-)

IFOBJECTPROPERTY(OBJECT_ID(usp_getObjectAuthor),IsProcedure)=1
DROPPROCusp_getObjectAuthor
GO
/***************************************************************************
*****/
/*CreatedBy:leimin*/
/*CreatedOn:29May2004*/
/*Description:Thisstoredprocedurereturnstheobjectpermissionwhich
you*/
/*GRANT,DENYandREVOKE.
*/
/***************************************************************************
*****/
Createprocusp_getObjectAuthor
@objectnamesysname=null,
@usernamesysname=null
as
setnocounton
begin
/***************************************************************************
*****/
/*definedtheinitilizationvariable*/
/***************************************************************************
*****/
Declare@rcint
Declare@rowcountint
Declare@groupidint

Set@rc=0
Set@rowcount=0

/***************************************************************************
*****/
/*Judgetheinputparameters,if@objectnameisnulland@usernameis
null*/
/*thenreturnallobjectsauthorization.*/
/***************************************************************************
*****/
if@objectnameisnulland@usernameisnull
begin
selectobject_name(a.id)asobjectname,
user_name(a.uid)asusename,
caseb.issqlrolewhen1thenGroup
elseUser
endasRole,
casea.protecttypewhen205thenGrant
when204thenGrant
when206thenDeny
elseRevoke
endasProtectType,
casea.[action]when26thenREFERENCES
when178thenCREATEFUNCTION
when193thenSELECT
when195thenINSERT
when196thenDELETE
when197thenUPDATE
when198thenCREATETABLE
when203thenCREATEDATABASE
when207thenCREATEVIEW
when222thenCREATEPROCEDURE
when224thenEXECUTE
when228thenBACKUPDATABASE
when233thenCREATEDEFAULT
when235thenBACKUPLOG
when236thenCREATERULE
else0
endas[Action],
user_name(a.grantor)asGrantor
fromsysprotectsainnerjoinsysusersbona.uid=b.uid
whereexists(select1fromsysobjects
where[name]=object_name(a.id)andxtypeS)
orderbyobject_name(a.id)

select@rowcount=@@rowcount
if@rowcount=0
begin
select@rc=-1
printThereanouserobjectsindatabase!
return@rc
end
end
/***************************************************************************
*****/
/*Judgetheinputparameters,if@objectnameisnulland@usernameisnot
null*/
/*thenreturnallobjectsauthorizationwhererelation@username*/
/*iftheuserbelongtoagroup,sowemustaddthegroupauthorization*/
/***************************************************************************
*****/
if@rc=0and@usernameisnotnulland@objectnameisnull
begin
ifnotexists(select*fromsysuserswhere[uid]=user_id(@username)and
status0)
begin
select@rc=-2
printTheusernameisnotincludeinsysuserstable.
return@rc
end

ifexists(select1fromsysmemberswhere[memberuid]=user_id(@username))
begin
selectobject_name(a.id)asobjectname,
user_name(a.uid)asusename,
caseb.issqlrolewhen1thenGroup
elseUser
endasRole,
casea.protecttypewhen205thenGrant
when204thenGrant
when206thenDeny
elseRevoke
endasProtectType,
casea.[action]when26thenREFERENCES
when178thenCREATEFUNCTION
when193thenSELECT
when195thenINSERT
when196thenDELETE
when197thenUPDATE
when198thenCREATETABLE
when203thenCREATEDATABASE
when207thenCREATEVIEW
when222thenCREATEPROCEDURE
when224thenEXECUTE
when228thenBACKUPDATABASE
when233thenCREATEDEFAULT
when235thenBACKUPLOG
when236thenCREATERULE
else0
endas[Action],
user_name(a.grantor)asGrantor
fromsysprotectsainnerjoinsysusersbona.uid=b.uid
whereexists(select1fromsysobjects
where[name]=object_name(a.id)andxtypeS)
and(exists(select1fromsysmembers
wheregroupuid=a.uidandmemberuid=user_id(@username))
ora.uid=user_id(@username))
orderbyobject_name(a.id)

select@rowcount=@@rowcount
if@rowcount=0
begin
select@rc=-3
print@username+havenotanyobjectsauthorization.
return@rc
end
end
else
begin
selectobject_name(a.id)asobjectname,
user_name(a.uid)asusename,
caseb.issqlrolewhen1thenGroup
elseUser
endasRole,
casea.protecttypewhen205thenGrant
when204thenGrant
when206thenDeny
elseRevoke
endasProtectType,
casea.[action]when26thenREFERENCES
when178thenCREATEFUNCTION
when193thenSELECT
when195thenINSERT
when196thenDELETE
when197thenUPDATE
when198thenCREATETABLE
when203thenCREATEDATABASE
when207thenCREATEVIEW
when222thenCREATEPROCEDURE
when224thenEXECUTE
when228thenBACKUPDATABASE
when233thenCREATEDEFAULT
when235thenBACKUPLOG
when236thenCREATERULE
else0
endas[Action],
user_name(a.grantor)asGrantor
fromsysprotectsainnerjoinsysusersbona.uid=b.uid
whereexists(select1fromsysobjects
where[name]=object_name(a.id)andxtypeS)
anda.uid=user_id(@username)
orderbyobject_name(a.id)

select@rowcount=@@rowcount
if@rowcount=0
begin
select@rc=-4
print@username+havenotanyobjectsauthorization.
return@rc
end
end

end
/***************************************************************************
*****/
/*Judgetheinputparameters,if@objectnameisnotnulland@usernameis
null*/
/*thenreturnoneobjectsauthorization*/
/***************************************************************************
*****/
if@rc=0and@objectnameisnotnulland@usernameisnull
begin
ifnotexists(select*fromsysobjectswhere[id]=object_id(@objectname)
andxtypeS)
begin
select@rc=-5
return@rc
end
if@rc=0
begin
selectobject_name(a.id)asobjectname,
user_name(a.uid)asusename,
caseb.issqlrolewhen1thenGroup
elseUser
endasRole,
casea.protecttypewhen205thenGrant
when204thenGrant
when206thenDeny
elseRevoke
endasProtectType,
casea.[action]when26thenREFERENCES
when178thenCREATEFUNCTION
when193thenSELECT
when195thenINSERT
when196thenDELETE
when197thenUPDATE
when198thenCREATETABLE
when203thenCREATEDATABASE
when207thenCREATEVIEW
when222thenCREATEPROCEDURE
when224thenEXECUTE
when228thenBACKUPDATABASE
when233thenCREATEDEFAULT
when235thenBACKUPLOG
when236thenCREATERULE
else0
endas[Action],
user_name(a.grantor)asGrantor
fromsysprotectsainnerjoinsysusersbona.uid=b.uid
whereexists(select1fromsysobjects
where[name]=object_name(a.id)andxtypeS)
and[id]=object_id(@objectname)
orderbyobject_name(a.id)

select@rowcount=@@rowcount
if@rowcount=0
begin
select@rc=-6
print@objectname+havenotgrantauthorizationtoanyuser
return@rc
end
end
end
/***************************************************************************
*****/
/*Judgetheinputparameters,if@objectnameisnotnulland@usernameis
notnull*/
/*thenreturnoneobjectsauthorizationbyoneuser*/
/***************************************************************************
*****/
if@rc=0and@objectnameisnotnulland@usernameisnotnull
begin
ifnotexists(select*fromsysobjectswhere[id]=object_id(@objectname)
andxtypeS)
begin
select@rc=-7
printTheobjectnameisnotincludeinsysobjectstable.
return@rc
end

ifnotexists(select*fromsysuserswhere[uid]=user_id(@username)and
status0)
begin
select@rc=-8
printTheusernameisnotincludeinsysuserstable.
return@rc
end

ifexists(select1fromsysmemberswhere[memberuid]=user_id(@username))
begin
selectobject_name(a.id)asobjectname,
user_name(a.uid)asusename,
caseb.issqlrolewhen1thenGroup
elseUser
endasRole,
casea.protecttypewhen205thenGrant
when204thenGrant
when206thenDeny
elseRevoke
endasProtectType,
casea.[action]when26thenREFERENCES
when178thenCREATEFUNCTION
when193thenSELECT
when195thenINSERT
when196thenDELETE
when197thenUPDATE
when198thenCREATETABLE
when203thenCREATEDATABASE
when207thenCREATEVIEW
when222thenCREATEPROCEDURE
when224thenEXECUTE
when228thenBACKUPDATABASE
when233thenCREATEDEFAULT
when235thenBACKUPLOG
when236thenCREATERULE
else0
endas[Action],
user_name(a.grantor)asGrantor
fromsysprotectsainnerjoinsysusersbona.uid=b.uid
whereexists(select1fromsysobjects
where[name]=object_name(a.[id])andxtypeS)
and(exists(select1fromsysmembers
wheregroupuid=a.uidandmemberuid=user_id(@username))
ora.uid=user_id(@username))
and[id]=object_id(@objectname)
orderbyobject_name(a.id)

select@rowcount=@@rowcount
if@rowcount=0
begin
select@rc=-9
print@username+havenotanyobjectsauthorization.
return@rc
end
end
else
begin
selectobject_name(a.id)asobjectname,
user_name(a.uid)asusename,
caseb.issqlrolewhen1thenGroup
elseUser
endasRole,
casea.protecttypewhen205thenGrant
when204thenGrant
when206thenDeny
elseRevoke
endasProtectType,
casea.[action]when26thenREFERENCES
when178thenCREATEFUNCTION
when193thenSELECT
when195thenINSERT
when196thenDELETE
when197thenUPDATE
when198thenCREATETABLE
when203thenCREATEDATABASE
when207thenCREATEVIEW
when222thenCREATEPROCEDURE
when224thenEXECUTE
when228thenBACKUPDATABASE
when233thenCREATEDEFAULT
when235thenBACKUPLOG
when236thenCREATERULE
else0
endas[Action],
user_name(a.grantor)asGrantor
fromsysprotectsainnerjoinsysusersbona.uid=b.uid
whereexists(select1fromsysobjects
where[name]=object_name(a.[id])andxtypeS)
anda.uid=user_id(@username)
and[id]=object_id(@objectname)
orderbyobject_name(a.id)

select@rowcount=@@rowcount
if@rowcount=0
begin
select@rc=-10
print@username+havenotanyobjectsauthorization.
return@rc
end
end
end
end
go
execusp_getObjectAuthor

导致了一个使用几乎和mSQL一样的API接口的用于他们的数据库的新的SQL接口的产生，这样，这个API被设计成允许为用于mSQL而写的第三方代码更容易移植到MySQL。
作者: 分手快乐    时间: 2015-1-19 16:17
其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQLServer2005的row_number比Oracle的更先进。因为它把Orderby集成到了一起，不用像Oracle那样还要用子查询进行封装。
作者: 若相依    时间: 2015-1-26 15:03
SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。）
作者: 只想知道    时间: 2015-2-4 17:00
习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动SQLServerManagementStudio的朋友使用。
作者: 飘灵儿    时间: 2015-2-10 04:32
原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。
作者: admin    时间: 2015-2-28 19:46
总感觉自己还是不会SQL
作者: 若天明    时间: 2015-3-10 07:33
备份方面可能还是一个老大难的问题。不能单独备份几个表总是感觉不爽。灵活备份的问题不知道什么时候才能解决。
作者: 再现理想    时间: 2015-3-17 05:10
光写几个SQL实在叫无知。
作者: 不帅    时间: 2015-3-23 21:58
如安全管理、备份恢复、性能监控和调优等，SQL只要熟悉基本操作就可以，只要程序设计部分只要稍加了解即可（如存储过程、触发器等）。



欢迎光临 仓酷云 (http://ckuyun.com/) Powered by Discuz! X3.2