MS SQL Server security - Information_Shema as a user in Master database

Author

Information_Shema as a user in Master database

Yifei

2005-12-09, 3:23 am

Hi, All

Recently, I was running a script like:

declare @sql nvarchar(4000)
declare @db sysname ; set @db = DB_NAME()
declare @u sysname ; set @u = QUOTENAME('db_execut
or')

set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SC
HEMA) + ''.'' +
QUOTENAME(ROUTINE_NA
ME) + '' TO ' + @u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJEC
T_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'

exec master.dbo.xp_execresultset @sql,@db

to grant stored procedures execution permition to role, after running the
script, the privilige is grant it to that role, but I got very strange
things happened on sql server.

1. Master database get Information_Shema as a user. dbaccess "via group
membership"
2.Master database got system_function_sche
ma as a user , dbaccess "via group
membership"
3.guest user show in every database in the instance, dbaccess "via group
membership"
if I delete guest account, show me message "the user is not in
database", sp_helpuser did not show guest user, enterprice manager show it
as a user.
4. run sp_grantdbaccess guest. the guest get permitted to use database. I
delete it successful, however, a couple minutes later, it appears in the db
as dbaccess via group membership.

it is really painful for me. could anyone give me a hint to fix the problem?

any help is appreciated.

Thanks

Yifei

Scott

2005-12-11, 8:23 pm

Hi, All,

I think this is an issue for sql 2005, because the when I open em from db
server, the information_schema user does not show, but I connect it through
a remote machine that installed sql 2005, and the user showed.

same as another machine that did not run the script and did installed sql
2005.

Yifei

"Yifei" <yjiang@sdg.aust.com> wrote in message
news:uVLA%23HI$FHA.2420@TK2MSFTNGP12.phx.gbl...
> Hi, All
>
> Recently, I was running a script like:
>
> declare @sql nvarchar(4000)
> declare @db sysname ; set @db = DB_NAME()
> declare @u sysname ; set @u = QUOTENAME('db_execut
or')
>
> set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SC
HEMA) + ''.'' +
> QUOTENAME(ROUTINE_NA
ME) + '' TO ' + @u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJEC
T_ID(ROUTINE_NAME),'
'IsMSShipped'') = 0'
>
> exec master.dbo.xp_execresultset @sql,@db
>
> to grant stored procedures execution permition to role, after running the
> script, the privilige is grant it to that role, but I got very strange
> things happened on sql server.
>
> 1. Master database get Information_Shema as a user. dbaccess "via group
> membership"
> 2.Master database got system_function_sche
ma as a user , dbaccess "via
> group
> membership"
> 3.guest user show in every database in the instance, dbaccess "via group
> membership"
> if I delete guest account, show me message "the user is not in
> database", sp_helpuser did not show guest user, enterprice manager show it
> as a user.
> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> delete it successful, however, a couple minutes later, it appears in the
> db
> as dbaccess via group membership.
>
> it is really painful for me. could anyone give me a hint to fix the
> problem?
>
> any help is appreciated.
>
> Thanks
>
> Yifei
>
>
>
>

Sponsored Links