June 23, 2006 at 8:13 am
This script for some reason grants users dbo on masters database not on my designated database prod.
Not sure of how to troubleshoot this
--Script
--Purpose Grant all users Default database qw_production
--Grant all users db_owner database role
--Date Jun-06-05
use master
--Declare variables
declare @name varchar(10)
declare @counter integer
declare @strname varchar(20)
declare @sqlscript varchar(2000)
declare @sqlscript1 varchar(2000)
--Drop table if it exists
drop table #temp
create table #temp( num int identity(1,1),name varchar(500))
insert into #temp
select name from syslogins where name not in ('sa', 'BUILTIN\Administrators','guest','public')
set @counter=@@rowcount
while @counter>0
begin
SET @sqlscript =(select ' exec sp_defaultdb ''' + name + ''', ''redgateProd'''
from #temp where @counter=num)
SET @sqlscript1 =(select 'exec sp_addrolemember ''db_owner'', ''' + name + ''''
from #temp where @counter=num)
execute(@sqlscript)
execute(@sqlscript1)
set @counter=@counter-1
END
June 23, 2006 at 8:21 am
You are likely executing this from the master database. As a result, that's where you're encountering an issue. That also suggests, though, that the logins have users created in the master database, which is generally not a good practice.
K. Brian Kelley
@kbriankelley
June 23, 2006 at 8:29 am
Thanks Brain
But how do I query syslogins without being in the master database
June 23, 2006 at 8:50 am
Three part naming convention.
master.dbo.syslogins
K. Brian Kelley
@kbriankelley
June 23, 2006 at 9:05 am
Thank you.Seems trivial does'nt it
Mike
June 23, 2006 at 10:34 am
Common thing that bites us all though. I can't count how many times I've been in a user database and queried a table that's only in master to get the object not found error.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply