Hey
Har lidt problemer med at få dynamsik SQL til at fungere med en linked server.
Der hvor jeg har markeret med fed vil jeg gerne bruge en variable for servernavnet.
Men kan squ ikke få det til fungere.
Er der nogen som har forstand på det ? :)
Og ja koden er ikke pæn - den skal rettes til, men mit issue er at jeg ikke kan få "lortet" til at fungere med server variable.
-- Set server properties
DECLARE @SERVER_NAME VARCHAR(100)
CREATE TABLE ##Databases(
ServerName varchar(100),
DatabaseName varchar(100)
)
CREATE TABLE ##TableGrants(
name varchar(100),
type_desc varchar(100),
permission_name varchar(100),
state_desc varchar(100),
class_desc varchar(100),
objectName varchar(100),
DatabaseName varchar(100),
ServerName varchar(100)
)
CREATE TABLE ##Servers(
Name varchar(100)
)
DECLARE SERVER_CURSOR CURSOR FOR
SELECT s.data_source FROM sys.servers s WHERE s.is_linked = 1
OPEN SERVER_CURSOR
FETCH NEXT FROM SERVER_CURSOR INTO @SERVER_NAME
BEGIN
exec sp_serveroption @server=@SERVER_NAME, @optname='rpc', @optvalue='true'
exec sp_serveroption @server=@SERVER_NAME, @optname='rpc out', @optvalue='true'
END
DEALLOCATE SERVER_CURSOR
-- Get data
DECLARE @DBName varchar(100)
DECLARE Server_CURSOR CURSOR FOR
SELECT s.data_source FROM sys.servers s WHERE s.is_linked = 1
OPEN SERVER_CURSOR
FETCH NEXT FROM SERVER_CURSOR INTO @SERVER_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('INSERT INTO ##Databases(DatabaseName) SELECT name from ['+@Server_name+'].master.sys.databases')
UPDATE ##Databases
SET
ServerName =@SERVER_NAME
WHERE ServerName IS NULL
FETCH NEXT FROM SERVER_CURSOR INTO @SERVER_NAME
END
DEALLOCATE SERVER_CURSOR
DECLARE @Database_Name varchar(100)
DECLARE Database_CURSOR CURSOR FOR
SELECT DatabaseName from ##Databases
OPEN Database_CURSOR
FETCH NEXT FROM Database_CURSOR INTO @Database_Name
While @@FETCH_STATUS = 0
BEGIN
INSERT INTO ##TableGrants(name,type_desc, permission_name,state_desc, class_desc , objectName)
EXEC('USE ' + @Database_Name + ' ' +
'select princ.name, ' +
'princ.type_desc, '+
'perm.permission_name, '+
'perm.state_desc, '+
'perm.class_desc, ' +
'object_name(perm.major_id) ' +
' from sys.database_principals princ '+
' left join ' +
' sys.database_permissions perm ' +
'on perm.grantee_principal_id = princ.principal_id ') AT
[DESKTOP-N76O6I4\TAKSATORSQL]
UPDATE ##TableGrants
SET
DatabaseName = @Database_Name,
ServerName = @SERVER_NAME
WHERE DatabaseName IS NULL AND ServerName IS NULL
FETCH NEXT FROM Database_CURSOR INTO @Database_Name
END
DEALLOCATE Database_CURSOR
SELECT * from ##TableGrants order by name, objectName
DROP TABLE ##Databases
DROP TABLE ##Servers
DROP TABLE ##TableGrants
--
Sidst redigeret 07-09-2021 21:06