Prev: Time when automatic file growth occurs
Next: Authentication failure for perfectly good server
From: tolcis on 2 Jul 2008 12:12
Hi,
I have the following cursor is running on my server and revoking
access for all users.

However, two of my databases are named: MY Mas and MY San. There is a
space in the name (don't ask - it was named before I got here). We
can't change the name of the database but when I run the code below it
give me the error message:

Could not locate entry in sysdatabases for database 'MY'. The code
never takes the full db name like MY Mas - it only takes the first
part (MY). How can I modify the code below so it takes the above two
databases and runs the cursor on them as well?

Thank you,


DECLARE @dbname varchar(30)
declare @username varchar (100)
DECLARE @viewactionmsg varchar (1000)
DECLARE dbnames_cursor CURSOR FOR SELECT name
FROM master..sysdatabases where name not IN
('tempdb','model')

OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor into @dbname
/* Do all databases, checking for 'EOF' on Cursor. */
WHILE (@@FETCH_STATUS <> -1)
BEGIN

execute ('DECLARE drop_users CURSOR FOR
SELECT u.[name] from ['+ @dbname + ']..sysusers u ' + 'where u.sid is
not NULL and u.[name] not in (''dbo'', ''sa'',''guest'')')

OPEN drop_users

FETCH NEXT FROM drop_users INTO @username

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
Begin
--PRINT @username
SELECT @viewactionmsg = 'USE '+ @dbname + ' EXEC sp_revokedbaccess
[' + @username +']'
exec (@viewactionmsg)
End

FETCH NEXT FROM drop_users
INTO @username
End --

-- CLOSE drop_users
DEALLOCATE drop_users
Fetch next from dbnames_cursor into @dbname
end
--CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
go
From: Alex Kuznetsov on 2 Jul 2008 12:24
On Jul 2, 11:12 am, tolcis <nytolly...(a)gmail.com> wrote:
> Hi,
> I have the following cursor is running on my server and revoking
> access for all users.
>
> However, two of my databases are named: MY Mas and MY San. There is a
> space in the name (don't ask - it was named before I got here). We
> can't change the name of the database but when I run the code below it
> give me the error message:
>
> Could not locate entry in sysdatabases for database 'MY'. The code
> never takes the full db name like MY Mas - it only takes the first
> part (MY). How can I modify the code below so it takes the above two
> databases and runs the cursor on them as well?
>
> Thank you,
>
> DECLARE @dbname varchar(30)
> declare @username varchar (100)
> DECLARE @viewactionmsg varchar (1000)
> DECLARE dbnames_cursor CURSOR FOR SELECT name
> FROM master..sysdatabases where name not IN
> ('tempdb','model')
>
> OPEN dbnames_cursor
> FETCH NEXT FROM dbnames_cursor into @dbname
> /* Do all databases, checking for 'EOF' on Cursor. */
> WHILE (@@FETCH_STATUS <> -1)
> BEGIN
>
> execute ('DECLARE drop_users CURSOR FOR
> SELECT u.[name] from ['+ @dbname + ']..sysusers u ' + 'where u.sid is
> not NULL and u.[name] not in (''dbo'', ''sa'',''guest'')')
>
> OPEN drop_users
>
> FETCH NEXT FROM drop_users INTO @username
>
> WHILE (@@FETCH_STATUS <> -1)
> BEGIN
> IF (@@FETCH_STATUS <> -2)
> Begin
> --PRINT @username
> SELECT @viewactionmsg = 'USE '+ @dbname + ' EXEC sp_revokedbaccess
> [' + @username +']'
> exec (@viewactionmsg)
> End
>
> FETCH NEXT FROM drop_users
> INTO @username
> End --
>
> -- CLOSE drop_users
> DEALLOCATE drop_users
> Fetch next from dbnames_cursor into @dbname
> end
> --CLOSE dbnames_cursor
> DEALLOCATE dbnames_cursor
> go

use square brackets:

SELECT @viewactionmsg = 'USE ['+ @dbname + '] EXEC
sp_revokedbaccess
From: Plamen Ratchev on 2 Jul 2008 12:33
You can use QUOTENAME:

SELECT @viewactionmsg = 'USE '+ QUOTENAME(@dbname) + ...

HTH,

Plamen Ratchev
http://www.SQLStudio.com
From: tolcis on 2 Jul 2008 12:32
On Jul 2, 12:24 pm, Alex Kuznetsov <alk...(a)gmail.com> wrote:
> On Jul 2, 11:12 am, tolcis <nytolly...(a)gmail.com> wrote:
>
>
>
> > Hi,
> > I have the following cursor is running on my server and revoking
> > access for all users.
>
> > However, two of my databases are named: MY Mas and MY San. There is a
> > space in the name (don't ask - it was named before I got here). We
> > can't change the name of the database but when I run the code below it
> > give me the error message:
>
> > Could not locate entry in sysdatabases for database 'MY'. The code
> > never takes the full db name like MY Mas - it only takes the first
> > part (MY). How can I modify the code below so it takes the above two
> > databases and runs the cursor on them as well?
>
> > Thank you,
>
> > DECLARE @dbname varchar(30)
> > declare @username varchar (100)
> > DECLARE @viewactionmsg varchar (1000)
> > DECLARE dbnames_cursor CURSOR FOR SELECT name
> > FROM master..sysdatabases where name not IN
> > ('tempdb','model')
>
> > OPEN dbnames_cursor
> > FETCH NEXT FROM dbnames_cursor into @dbname
> > /* Do all databases, checking for 'EOF' on Cursor. */
> > WHILE (@@FETCH_STATUS <> -1)
> > BEGIN
>
> > execute ('DECLARE drop_users CURSOR FOR
> > SELECT u.[name] from ['+ @dbname + ']..sysusers u ' + 'where u.sid is
> > not NULL and u.[name] not in (''dbo'', ''sa'',''guest'')')
>
> > OPEN drop_users
>
> > FETCH NEXT FROM drop_users INTO @username
>
> > WHILE (@@FETCH_STATUS <> -1)
> > BEGIN
> > IF (@@FETCH_STATUS <> -2)
> > Begin
> > --PRINT @username
> > SELECT @viewactionmsg = 'USE '+ @dbname + ' EXEC sp_revokedbaccess
> > [' + @username +']'
> > exec (@viewactionmsg)
> > End
>
> > FETCH NEXT FROM drop_users
> > INTO @username
> > End --
>
> > -- CLOSE drop_users
> > DEALLOCATE drop_users
> > Fetch next from dbnames_cursor into @dbname
> > end
> > --CLOSE dbnames_cursor
> > DEALLOCATE dbnames_cursor
> > go
>
> use square brackets:
>
> SELECT @viewactionmsg = 'USE ['+ @dbname + '] EXEC
> sp_revokedbaccess

Thank you, that did it.
 | 
Pages: 1
Prev: Time when automatic file growth occurs
Next: Authentication failure for perfectly good server