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.when26thenREFERENCES
when178thenCREATEFUNCTION
when193thenSELECT
when195thenINSERT
when196thenDELETE
when197thenUPDATE
when198thenCREATETABLE
when203thenCREATEDATABASE
when207thenCREATEVIEW
when222thenCREATEPROCEDURE
when224thenEXECUTE
when228thenBACKUPDATABASE
when233thenCREATEDEFAULT
when235thenBACKUPLOG
when236thenCREATERULE
else0
endas,
user_name(a.grantor)asGrantor
fromsysprotectsainnerjoinsysusersbona.uid=b.uid
whereexists(select1fromsysobjects
where=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=user_id(@username)and
status0)
begin
select@rc=-2
printTheusernameisnotincludeinsysuserstable.
return@rc
end
ifexists(select1fromsysmemberswhere=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.when26thenREFERENCES
when178thenCREATEFUNCTION
when193thenSELECT
when195thenINSERT
when196thenDELETE
when197thenUPDATE
when198thenCREATETABLE
when203thenCREATEDATABASE
when207thenCREATEVIEW
when222thenCREATEPROCEDURE
when224thenEXECUTE
when228thenBACKUPDATABASE
when233thenCREATEDEFAULT
when235thenBACKUPLOG
when236thenCREATERULE
else0
endas,
user_name(a.grantor)asGrantor
fromsysprotectsainnerjoinsysusersbona.uid=b.uid
whereexists(select1fromsysobjects
where=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.when26thenREFERENCES
when178thenCREATEFUNCTION
when193thenSELECT
when195thenINSERT
when196thenDELETE
when197thenUPDATE
when198thenCREATETABLE
when203thenCREATEDATABASE
when207thenCREATEVIEW
when222thenCREATEPROCEDURE
when224thenEXECUTE
when228thenBACKUPDATABASE
when233thenCREATEDEFAULT
when235thenBACKUPLOG
when236thenCREATERULE
else0
endas,
user_name(a.grantor)asGrantor
fromsysprotectsainnerjoinsysusersbona.uid=b.uid
whereexists(select1fromsysobjects
where=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=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.when26thenREFERENCES
when178thenCREATEFUNCTION
when193thenSELECT
when195thenINSERT
when196thenDELETE
when197thenUPDATE
when198thenCREATETABLE
when203thenCREATEDATABASE
when207thenCREATEVIEW
when222thenCREATEPROCEDURE
when224thenEXECUTE
when228thenBACKUPDATABASE
when233thenCREATEDEFAULT
when235thenBACKUPLOG
when236thenCREATERULE
else0
endas,
user_name(a.grantor)asGrantor
fromsysprotectsainnerjoinsysusersbona.uid=b.uid
whereexists(select1fromsysobjects
where=object_name(a.id)andxtypeS)
and=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=object_id(@objectname)
andxtypeS)
begin
select@rc=-7
printTheobjectnameisnotincludeinsysobjectstable.
return@rc
end
ifnotexists(select*fromsysuserswhere=user_id(@username)and
status0)
begin
select@rc=-8
printTheusernameisnotincludeinsysuserstable.
return@rc
end
ifexists(select1fromsysmemberswhere=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.when26thenREFERENCES
when178thenCREATEFUNCTION
when193thenSELECT
when195thenINSERT
when196thenDELETE
when197thenUPDATE
when198thenCREATETABLE
when203thenCREATEDATABASE
when207thenCREATEVIEW
when222thenCREATEPROCEDURE
when224thenEXECUTE
when228thenBACKUPDATABASE
when233thenCREATEDEFAULT
when235thenBACKUPLOG
when236thenCREATERULE
else0
endas,
user_name(a.grantor)asGrantor
fromsysprotectsainnerjoinsysusersbona.uid=b.uid
whereexists(select1fromsysobjects
where=object_name(a.)andxtypeS)
and(exists(select1fromsysmembers
wheregroupuid=a.uidandmemberuid=user_id(@username))
ora.uid=user_id(@username))
and=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.when26thenREFERENCES
when178thenCREATEFUNCTION
when193thenSELECT
when195thenINSERT
when196thenDELETE
when197thenUPDATE
when198thenCREATETABLE
when203thenCREATEDATABASE
when207thenCREATEVIEW
when222thenCREATEPROCEDURE
when224thenEXECUTE
when228thenBACKUPDATABASE
when233thenCREATEDEFAULT
when235thenBACKUPLOG
when236thenCREATERULE
else0
endas,
user_name(a.grantor)asGrantor
fromsysprotectsainnerjoinsysusersbona.uid=b.uid
whereexists(select1fromsysobjects
where=object_name(a.)andxtypeS)
anda.uid=user_id(@username)
and=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。 其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQLServer2005的row_number比Oracle的更先进。因为它把Orderby集成到了一起，不用像Oracle那样还要用子查询进行封装。 SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。） 习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动SQLServerManagementStudio的朋友使用。 原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。 总感觉自己还是不会SQL 备份方面可能还是一个老大难的问题。不能单独备份几个表总是感觉不爽。灵活备份的问题不知道什么时候才能解决。 光写几个SQL实在叫无知。 如安全管理、备份恢复、性能监控和调优等，SQL只要熟悉基本操作就可以，只要程序设计部分只要稍加了解即可（如存储过程、触发器等）。
页:
[1]