Re: sp_MSdbuseraccess

From: Jean Davis (jean_davis_200_at_hotmail.com)
Date: 07/15/05 

Date: Fri, 15 Jul 2005 12:25:12 +0100

Thanks Tim - that's done the trick!!

<stahta01@juno.com> wrote in message
news:1121389883.622340.106130@g49g2000cwa.googlegroups.com...
> AS-IS NO WARRENTY USE AT OWN RISK
> Change this line
> "select @accessbit = has_dbaccess(@dbname)"
> To Below and it seems to work using code at URL
> http://support.microsoft.com/d efault.aspx/kb/889696
>
> Tim S
>
> /* Determine whether the current user has access to the
> database. */
> -- select @accessbit = has_dbaccess(@dbname)
> select @accessbit =
> CASE
> WHEN DB_ID(@dbname) < 5
> THEN
> (
> CASE
> WHEN 1 = IS_SRVROLEMEMBER('sysadmin') OR
> 1 = IS_SRVROLEMEMBER('dbcreator') OR
> 1 = IS_SRVROLEMEMBER('diskadmin') OR
> 1 = IS_SRVROLEMEMBER('processadmin') OR
> 1 = IS_SRVROLEMEMBER('serveradmin') OR
> 1 = IS_SRVROLEMEMBER('setupadmin') OR
> 1 = IS_SRVROLEMEMBER('securityadmin')
> THEN has_dbaccess(@dbname)
> ELSE 0
> END
> )
> ELSE has_dbaccess(@dbname)
> END
>