Re: find out a user/account permission...

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Mark Allison (marka_at_no.tinned.meat.mvps.org)
Date: 04/22/04 

Date: Thu, 22 Apr 2004 11:41:27 +0100

The attached query should work for you, it is based around sysprotects. 

-- 
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
>
>             select delete insert exec
> table1     x        x
> table2     x
> stored proc                        x
>
>
begin 666 permissions.sql
M<V5L96-T#0H)<75O=&5N86UE*&\N;F%M92D@05,@3V)J96-T3F%M90T*+ EC
M87-E(&\N='EP92 -"@D)=VAE;B G<"<@=&AE;B G4')O8V5D=7)E)PT*"0EW
M:&5N("=U)R!T:&5N("=486)L92<-"@D)=VAE;B G='(G('1H96X@)U1R:6=G
M97(G#0H)"7=H96X@)V,G('1H96X@)T-O;G-T<F%I;G0G#0H)"7=H96X@)W1F
M)R!T:&5N("=&=6YC=&EO;B<-"@D)=VAE;B G=B<@=&AE;B G5FEE=R<-"@EE
M;'-E("=5;FMN;W=N)R!E;F0@87,@3V)J96-T5'EP90T*+ EQ=6]T96YA;64H
M=2YN86UE*2!!4R!$0E5S97).86UE#0HL"7-U;2AC87-E('=H96X@86-T:6]N
M(#T@,3DU(&%N9"!P<F]T96-T='EP92!I;B H,C T+#(P-2D@=&AE;B Q("TM
M($=204Y4140-"@D)=VAE;B!A8W1I;VX@/2 Q.34@86YD('!R;W1E8W1T>7!E
M(#T@,C V('1H96X@+3$@(" @(" @("TM(%)%5D]+140-"@D)96QS92 P(&5N
M9"D@87,@)TE.4T525"<@(" @(" @(" @(" @(" @(" @(" @(" @(" @(" @
M("TM(&YE:71H97(-"BP)<W5M*&-A<V4@=VAE;B!A8W1I;VX@/2 Q.3,@86YD
M('!R;W1E8W1T>7!E(&EN("@R,#0L,C U*2!T:&5N(#$@( T*"0EW:&5N(&%C
M=&EO;B ](#$Y,R!A;F0@<')O=&5C='1Y<&4@/2 R,#8@=&AE;B M,2 @(" @
M(" @( T*"0EE;'-E(# @96YD*2!A<R G4T5,14-4)R @(" @(" @(" @(" @
M(" @(" @(" @(" @(" @(" @( T*+ ES=6TH8V%S92!W:&5N(&%C=&EO;B ]
M(#$Y-R!A;F0@<')O=&5C='1Y<&4@:6X@*#(P-"PR,#4I('1H96X@,2 -"@D)
M=VAE;B!A8W1I;VX@/2 Q.3<@86YD('!R;W1E8W1T>7!E(#T@,C V('1H96X@
M+3$@#0H)"65L<V4@,"!E;F0I(&%S("=54$1!5$4G#0HL"7-U;2AC87-E('=H
M96X@86-T:6]N(#T@,3DV(&%N9"!P<F]T96-T='EP92!I;B H,C T+#(P-2D@
M=&AE;B Q( T*"0EW:&5N(&%C=&EO;B ](#$Y-B!A;F0@<')O=&5C='1Y<&4@
M/2 R,#8@=&AE;B M,2 -"@D)96QS92 P(&5N9"D@87,@)T1%3$5412<-"BP)
M<W5M*&-A<V4@=VAE;B!A8W1I;VX@/2 R,C0@86YD('!R;W1E8W1T>7!E(&EN
M("@R,#0L,C U*2!T:&5N(#$@#0H)"7=H96X@86-T:6]N(#T@,C(T(&%N9"!P
M<F]T96-T='EP92 ](#(P-B!T:&5N("TQ( T*"0EE;'-E(# @96YD*2!A<R G
M15A%0U5412<-"BP)<W5M*&-A<V4@=VAE;B!A8W1I;VX@/2 R-B!A;F0@<')O
M=&5C='1Y<&4@:6X@*#(P-"PR,#4I('1H96X@,2 -"@D)=VAE;B!A8W1I;VX@
M/2 R-B!A;F0@<')O=&5C='1Y<&4@/2 R,#8@=&AE;B M,2 -"@D)96QS92 P
M(&5N9"D@87,@)U)%1D5214Y#15,G#0HL"7-U;2AC87-E('=H96X@86-T:6]N
M(#T@,3DX(&%N9"!P<F]T96-T='EP92!I;B H,C T+#(P-2D@=&AE;B Q( T*
M"0EW:&5N(&%C=&EO;B ](#$Y."!A;F0@<')O=&5C='1Y<&4@/2 R,#8@=&AE
M;B M,2 -"@D)96QS92 P(&5N9"D@87,@)T-214%412!404),12<-"BP)<W5M
M*&-A<V4@=VAE;B!A8W1I;VX@/2 R,#,@86YD('!R;W1E8W1T>7!E(&EN("@R
M,#0L,C U*2!T:&5N(#$@#0H)"7=H96X@86-T:6]N(#T@,C S(&%N9"!P<F]T
M96-T='EP92 ](#(P-B!T:&5N("TQ( T*"0EE;'-E(# @96YD*2!A<R G0U)%
M051%($1!5$%"05-%)PT*+ ES=6TH8V%S92!W:&5N(&%C=&EO;B ](#(P-R!A
M;F0@<')O=&5C='1Y<&4@:6X@*#(P-"PR,#4I('1H96X@,2 -"@D)=VAE;B!A
M8W1I;VX@/2 R,#<@86YD('!R;W1E8W1T>7!E(#T@,C V('1H96X@+3$@#0H)
M"65L<V4@,"!E;F0I(&%S("=#4D5!5$4@5DE%5R<-"BP)<W5M*&-A<V4@=VAE
M;B!A8W1I;VX@/2 R,C(@86YD('!R;W1E8W1T>7!E(&EN("@R,#0L,C U*2!T
M:&5N(#$@#0H)"7=H96X@86-T:6]N(#T@,C(R(&%N9"!P<F]T96-T='EP92 ]
M(#(P-B!T:&5N("TQ( T*"0EE;'-E(# @96YD*2!A<R G0U)%051%(%!23T-%
M1%5212<-"BP)<W5M*&-A<V4@=VAE;B!A8W1I;VX@/2 R,C@@86YD('!R;W1E
M8W1T>7!E(&EN("@R,#0L,C U*2!T:&5N(#$@#0H)"7=H96X@86-T:6]N(#T@
M,C(X(&%N9"!P<F]T96-T='EP92 ](#(P-B!T:&5N("TQ( T*"0EE;'-E(# @
M96YD*2!A<R G0D%#2U50($1!5$%"05-%)PT*+ ES=6TH8V%S92!W:&5N(&%C
M=&EO;B ](#(S,R!A;F0@<')O=&5C='1Y<&4@:6X@*#(P-"PR,#4I('1H96X@
M,2 -"@D)=VAE;B!A8W1I;VX@/2 R,S,@86YD('!R;W1E8W1T>7!E(#T@,C V
M('1H96X@+3$@#0H)"65L<V4@,"!E;F0I(&%S("=#4D5!5$4@1$5&055,5"<-
M"BP)<W5M*&-A<V4@=VAE;B!A8W1I;VX@/2 R,S4@86YD('!R;W1E8W1T>7!E
M(&EN("@R,#0L,C U*2!T:&5N(#$@#0H)"7=H96X@86-T:6]N(#T@,C,U(&%N
M9"!P<F]T96-T='EP92 ](#(P-B!T:&5N("TQ( T*"0EE;'-E(# @96YD*2!A
M<R G0D%#2U50($Q/1R<-"BP)<W5M*&-A<V4@=VAE;B!A8W1I;VX@/2 R,S8@
M86YD('!R;W1E8W1T>7!E(&EN("@R,#0L,C U*2!T:&5N(#$@#0H)"7=H96X@
M86-T:6]N(#T@,C,V(&%N9"!P<F]T96-T='EP92 ](#(P-B!T:&5N("TQ( T*
M"0EE;'-E(# @96YD*2!A<R G0U)%051%(%)53$4G#0IF<F]M#0IS>7-P<F]T
M96-T<R!P#0II;FYE<B!J;VEN('-Y<V]B:F5C=',@;R!O;B!P+FED(#T@;RYI
M9 T*:6YN97(@:F]I;B!S>7-U<V5R<R!U(&]N(' N=6ED(#T@=2YU:60-"G=H
M97)E(&]B:F5C='!R;W!E<G1Y("AO+FED+" G27--<U-H:7!P960G*2 ](# -
M"F=R;W5P(&)Y(&\N;F%M92P@;RYT>7!E+"!U+FYA;64-"F]R9&5R(&)Y(&\N
6='EP92P@;RYN86UE+"!U+FYA;64-"@``
`
end

Relevant Pages

  • Re: Can i start a MSSQLServer service remotely?
    ... Administrative Tools-->Computer Management, then expand Services. ... Mark Allison, SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Re: Protecting database from administrators
    ... Regular users of the database can be secured by only allowing them access through an application, using stored procs, etc.. ... Mark Allison, SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • RE: multiple instances system requirement
    ... Sorry for the vague answer - this situation requires lots of planning and testing. ... Mark Allison, SQL Server MVP ...
    (microsoft.public.sqlserver.setup)
  • Re: Column name with period
    ... Go and visit your optician! ... not collation! ... Mark Allison, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • RE: Agent
    ... I know this may sound silly but is it a simple case of the SQLAgent service is not set to Automatic? ... Perhaps it is set to Manual so that you manually have to start it after the server boots up. ... Mark Allison, SQL Server MVP ...
    (microsoft.public.sqlserver.server)