November 2, 2015 at 12:36 pm
set nocount on
DECLARE
@AlterAuthorizationStatement nvarchar(MAX)
, @Statement nvarchar(MAX)
, @SchemaName sysname
, @db_user nvarchar(10)
set @db_user='test'
SET @Statement =
N'SELECT name
FROM sys.schemas
WHERE principal_id = USER_ID('''+@db_user+''');';
create table #temp123(name varchar(20))
insert into #temp123(name) exec sp_executesql @statement;
DECLARE SchemaList CURSOR LOCAL FAST_FORWARD FOR
select name from #temp123
OPEN SchemaList;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM SchemaList INTO @SchemaName;
IF @@FETCH_STATUS = -1 BREAK;
SET @AlterAuthorizationStatement =
N'ALTER AUTHORIZATION ON SCHEMA::' + @SchemaName + N' TO dbo;';
-- RAISERROR ('Executing %s', 0, 0, @AlterAuthorizationStatement) WITH NOWAIT;
EXEC sp_executesql @AlterAuthorizationStatement;
END;
CLOSE SchemaList;
DEALLOCATE SchemaList;
declare @sql Nvarchar(max)
SET @sql= N'drop user [' +@db_user+ N']';
Exec sp_executesql @sql;
drop table #temp123
November 2, 2015 at 12:38 pm
How are you trying to pass the values to the query?
Gerald Britton, Pluralsight courses
November 2, 2015 at 1:16 pm
i have tried passing this variable using a stored procedure, as it didn't work i have manually passed the variable once i declared the variable.
Either way it's not recognizing the variable.
November 2, 2015 at 1:33 pm
vamsi.341 (11/2/2015)
i have tried passing this variable using a stored procedure, as it didn't work i have manually passed the variable once i declared the variable.Either way it's not recognizing the variable.
do you mean you wrote:
declare @myvar sometype;
set @myvar = somevalue;
select ...
where mycol = @myvar;
and you got no results? It's not because SQL didn't read your variable, it's because there are no matching rows.
Gerald Britton, Pluralsight courses
November 2, 2015 at 2:08 pm
This is what i was trying to do
GO
/****** Object: StoredProcedure [dbo].[sp_delete_user_v1] Script Date: 11/02/2015 16:07:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_delete_user_v1] (@ID VARCHAR(25)) AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
--select * from IncidentList where RecordID=@ID
----condition to change schema owner to DBO
exec sp_MSforeachdb
'
use [?]
print @ID
/*IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @ID)
begin
----condition to change schema owner to DBO
DECLARE
@AlterAuthorizationStatement nvarchar(MAX)
, @SchemaName sysname;
DECLARE SchemaList CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.schemas
WHERE principal_id = USER_ID(@ID);
OPEN SchemaList;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM SchemaList INTO @SchemaName;
IF @@FETCH_STATUS = -1 BREAK;
SET @AlterAuthorizationStatement =
N''ALTER AUTHORIZATION ON SCHEMA::'' + @SchemaName + N'' TO dbo;'';
EXEC sp_executesql @AlterAuthorizationStatement;
END;
CLOSE SchemaList;
DEALLOCATE SchemaList;
Drop schema @ID
DROP USER @ID
End*/
'
DROP LOGIN [@ID]
END
November 2, 2015 at 2:14 pm
Why are you doing additional work?
set nocount on
DECLARE
@AlterAuthorizationStatement nvarchar(MAX) = N''
, @db_user nvarchar(10)
set @db_user='test'
SELECT @AlterAuthorizationStatement = @AlterAuthorizationStatement + N'ALTER AUTHORIZATION ON SCHEMA::' + name + N' TO dbo;' + CHAR(10)
FROM sys.schemas
WHERE principal_id = USER_ID(@db_user);
EXEC sp_executesql @AlterAuthorizationStatement;
declare @sql Nvarchar(max)
SET @sql= N'drop user [' +@db_user+ N']';
Exec sp_executesql @sql;
I'm not sure if you're getting errors because sys.schemas doesn't exist in SQL Server 2000
November 2, 2015 at 2:37 pm
I am trying to implement on SQL 2008, The code is not accepting the variable so was trying different options, it was accepting if i was running against single db but if i run against multiple DB's it not accepting at conditions where i am bringing the data related to that user
SELECT name
FROM sys.schemas
WHERE principal_id = USER_ID('''+@db_user+''');
the actual code is below which i want to execute.
USE [incidents_new]
GO
/****** Object: StoredProcedure [dbo].[sp_delete_user_v1] Script Date: 11/02/2015 16:07:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_delete_user_v1] (@ID VARCHAR(25)) AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
--select * from IncidentList where RecordID=@ID
----condition to change schema owner to DBO
exec sp_MSforeachdb
'
use [?]
print @ID
/*IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @ID)
begin
----condition to change schema owner to DBO
DECLARE
@AlterAuthorizationStatement nvarchar(MAX)
, @SchemaName sysname;
DECLARE SchemaList CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.schemas
WHERE principal_id = USER_ID(@ID);
OPEN SchemaList;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM SchemaList INTO @SchemaName;
IF @@FETCH_STATUS = -1 BREAK;
SET @AlterAuthorizationStatement =
N''ALTER AUTHORIZATION ON SCHEMA::'' + @SchemaName + N'' TO dbo;'';
EXEC sp_executesql @AlterAuthorizationStatement;
END;
CLOSE SchemaList;
DEALLOCATE SchemaList;
Drop schema @ID
DROP USER @ID
End*/
'
DROP LOGIN [@ID]
END
November 2, 2015 at 2:50 pm
Of course it's not accepting the variable as you don't send it as a parameter, nor you declare it inside your dynamic code.
My suggestion is to avoid the undocumented procedure and create a cursor by yourself to traverse the databases as needed.
November 3, 2015 at 7:56 am
Hi Luis,
If you see the storedprocedure parameters, i am passing the USER as a parameter to the storedprocedure..
I tried using cursor instead of SP_MSForeachDB, same issue it's not accepting the variable which i am passing to the SP.
November 3, 2015 at 10:36 am
vamsi.341 (11/3/2015)
Hi Luis,If you see the storedprocedure parameters, i am passing the USER as a parameter to the storedprocedure..
I tried using cursor instead of SP_MSForeachDB, same issue it's not accepting the variable which i am passing to the SP.
You're passing it to the sp_delete_user_v1 stored procedure, but you're not passing it to the SP_MSForeachDB, and probably not to the execution of the dynamic sql in the cursor.
November 3, 2015 at 11:20 am
Thank you Luis,
Can you please let me know how to send the parameter dynamically.
November 3, 2015 at 12:17 pm
I'm not at my computer right now, but you should check sp_executesql to use parameters with dynamic sql
November 3, 2015 at 12:44 pm
Thanks Luis for your help..
But how do we pass the variables dynamically. can you please help me in rewriting the below code
/****** Object: StoredProcedure [dbo].[sp_delete_user_v1] Script Date: 11/02/2015 16:07:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_delete_user_v1] (@ID VARCHAR(25)) AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
--select * from IncidentList where RecordID=@ID
----condition to change schema owner to DBO
exec sp_MSforeachdb
'
use [?]
print @ID
/*IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @ID)
begin
----condition to change schema owner to DBO
DECLARE
@AlterAuthorizationStatement nvarchar(MAX)
, @SchemaName sysname;
DECLARE SchemaList CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.schemas
WHERE principal_id = USER_ID(@ID);
OPEN SchemaList;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM SchemaList INTO @SchemaName;
IF @@FETCH_STATUS = -1 BREAK;
SET @AlterAuthorizationStatement =
N''ALTER AUTHORIZATION ON SCHEMA::'' + @SchemaName + N'' TO dbo;'';
EXEC sp_executesql @AlterAuthorizationStatement;
END;
CLOSE SchemaList;
DEALLOCATE SchemaList;
Drop schema @ID
DROP USER @ID
End*/
'
DROP LOGIN [@ID]
END
November 3, 2015 at 4:17 pm
Thank you Luis for the help..i tweaked the query, now it is working fine.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply