1 2 3 /*
4 ** Description
5 ** Finds if the user executing the procedure is the
6 ** database owner.
7 **
8 ** Parameters
9 ** @currdbname - database name
10 **
11 ** Returns
12 ** 1 - User is DBO or alias to DBO
13 ** 0 - Otherwise
14 **
15 ** Notes
16 ** It must be called as @currdbname.sp_is_dbo @currdbname
17 */18 createproceduresp_is_dbo(@currdbname varchar(256))19 as20 begin21 declare@dbo int
22 23 -- Initialize as non-DBO to handle all exit conditions.24 25 select@dbo= 0
26 27 -- Check for caller's error. We should always call this sproc28 -- using @currdbname.dbo. so that we are looking up the29 -- correct sysusers. In case of an error in caller's calling30 -- convention, flag that by returning that user is not DBO in db.31 32 if(@currdbname!= db_name())33 begin34 select@dbo= 0
35 end36 37 -- Is this login the dbo of the current db?38 -- If a dbo has 'setuser' to some other user, we do not recognize39 -- him/her as DBO any longer. This is a minor restriction that we40 -- only allow a real DBO to perform any command, and not under setuser.41 42 elseifexists(select 1 fromsysusers43 whereuid= 1 andsuid= suser_id())44 and(user_id()= 1)45 begin46 select@dbo= 1
47 end48 else49 begin50 -- Is this login the 'dbo' alias of the db, but w/o sa_role?51 52 ifexists(select 1 fromsysalternates53 wheresuid= suser_id()54 andaltsuid=(selectsuidfromsysusers55 whereuid= 1))56 begin57 select@dbo= 1
58 end59 end60 61 return@dbo62 end63
exec sp_procxmode 'sp_is_dbo', 'AnyMode'
go
Grant Execute on sp_is_dbo to public
go