June 6, 2013 at 12:56 am
There is a list of servers and databases kept in the catalog DB ie the DB name and connection details. This information resides in one server. I have to iterate through each server/database and connection details, establish a connection and create a login and a user in that specified database with db_datareader role. I tired using openrowset but I get the following error
declare @nameout nvarchar(max)
SELECT @NameOut = name
FROM OPENROWSET ('SQLNCLI','Server=Servername;TRUSTED_CONNECTION=YES;',
'SELECT name FROM sys.server_principals
WHERE name = ''Domain\user''')
IF ISNULL(@Nameout,'')=''
select @Nameout as ifname
BEGIN
SELECT @NameOut = name
FROM OPENROWSET ('SQLNCLI','Server=Servername;TRUSTED_CONNECTION=YES;',
'CREATE LOGIN [Domain\user] FROM WINDOWS')
select @NameOut
END
Cannot process the object "CREATE LOGIN [username] FROM WINDOWS". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
The user i have logged in definitely has permission to create login in the target server. I have also tried to link the server but still it throws me the same error. The select with openrowset seem to return the @nameout but I am unable to create a login. Please help me with this issue.
June 6, 2013 at 5:51 am
Do you need to create the same login on each server? If so you can try opening a query window against a registered server group. That way you just write the syntax like you would for a local server and then run it against the group.
Joie Andrew
"Since 1982"
June 6, 2013 at 6:52 am
i think the issue is your second statement; you only select if your variable is empty string, but you already created it?
i would do it all in a single command, like this, using if exists:
declare @NameOut nvarchar(128)
SELECT @NameOut = name
FROM OPENROWSET ('SQLNCLI','Server=DEV223\SQL2005;TRUSTED_CONNECTION=YES;',
'IF NOT EXISTS(SELECT name from sys.server_principals WHERE name = ''disney\lizaguirre'')
BEGIN
CREATE LOGIN [disney\lizaguirre] FROM WINDOWS;
END
SELECT name from sys.server_principals WHERE name = ''disney\lizaguirre'' ')
PRINT @NameOut
Lowell
June 6, 2013 at 7:21 am
I am sorry but I forgot to mention that the servername has to be passed as a parameter to the openrowset query
DECLARE @SQLString1 NVARCHAR(MAX) = ''
DECLARE @SqlCreateLogin nvarchar(max)
/*
I assign the @serverinstancename,@DBname variables values using a query. Not hard coded values
*/
SET @SQLString1 = N'SELECT @NameOut = name
FROM OPENROWSET (''SQLNCLI'',''Server=' + @ServerInstanceName + ';TRUSTED_CONNECTION=YES;'',
''DECLARE @SqlCreateLogin nvarchar(max)
Select @SqlCreateLogin = ''''IF NOT EXISTS (
SELECT 1
FROM sys.server_principals
WHERE name = ''''''''domain\user''''''''
)
CREATE LOGIN [domain\user] FROM WINDOWS;'''''')'
EXEC sp_executesql
@SQLString1 ,N'@ServerInstanceName SYSNAME,@DBName SYSNAME,@NameOut SYSNAME OUTPUT',@ServerInstanceName,@DBName,@NameOut = @Name OUTPUT
I get the following error
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "DECLARE @SqlCreateLogin nvarchar(max)
Select @SqlCreateLogin = 'IF NOT EXISTS (
SELECT 1
FROM sys.server_principals
WHERE name = ''Domain\user''
)
CREATE LOGIN [domain\user] FROM WINDOWS;'". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
June 6, 2013 at 7:52 am
Jerome it seems to me you are leaving a lot out.
your example must be coming inside a cursor, right? why don't you show that part too?
your code(so far) is adding a login, but
your title says you want to add a user with db_datareader, which is an extra step and requires a database name; i'm expecting your final cursor is going to have a command like this in it?
did you leave out something else, where you are getting the list of all the databases on the server?
USE DBName;
If NOT EXISTS(SELECT name from sys.database_principals WHERE name = 'disney\lizaguirre')
CREATE USER disney\lizaguirre from disney\lizaguirre
EXEC sp_addrolemember N'db_datareader', N'disney\lizaguirre'
typically, i would get all the commands together as a stack of scripts, test them, and then if they work, parameterize them into dynamic sql;
it looks to me like you didn't get the original script working yet, so your fighting syntax issues inside dynamic SQL;
i would just build a stack of queries, and execute the command;
Lowell
June 6, 2013 at 8:07 am
Sorry again. Here is the whole script.
DECLARE @DbData AS TABLE
(
id INT IDENTITY(1,1)
,SQlServername NVARCHAR(MAX)
,SQLInstanceName NVARCHAR(MAX)
,TCPPortNum INT
,DatabaseName NVARCHAR(MAX)
)
DECLARE @ServerInstanceName NVARCHAR(MAX)
DECLARE @DBName NVARCHAR(MAX)
DECLARE @SQLString NVARCHAR(MAX) = ''
DECLARE @SQLString1 NVARCHAR(MAX) = ''
DECLARE @SQLString2 NVARCHAR(MAX) = ''
INSERT INTO @DbData(SQlServername, SQLInstanceName, TCPPortNum,DatabaseName)
SELECT
db.SQLServerName
,si.SQLInstanceName
,si.TCPPortNum
,ed.DatabaseName FROM schema.EngagementDatabase ed
inner join schema.DatabaseServerSQLInstance SI ON SI.DatabaseServerSQLInstanceID =ed.DatabaseServerSQLInstanceID
inner join schema.DatabaseServer db ON db.DatabaseServerID =SI.DatabaseServerID
DECLARE @i INT = 0
WHILE(@i < (SELECT COUNT(*) FROM @DbData))
BEGIN
SET @i = @i + 1
SELECT @ServerInstanceName = db.SQlServername + '\' + SQLInstanceName
,@DBName= DatabaseName FROM @DbData db WHERE db.id = @i
--select @DBName as dbname
SET @SQLString = N'SELECT @NameOut = name
FROM OPENROWSET (''SQLNCLI'',''Server=' + @ServerInstanceName + ';TRUSTED_CONNECTION=YES;'',
''SELECT name FROM sys.databases WHERE name = '''''+ @DBName +''''''') AS tbl'
-- Check Database existance
DECLARE @NameOut NVARCHAR(MAX)
DECLARE @Name SYSNAME = ''
EXEC sp_executesql
@SQLString ,
N'@ServerInstanceName SYSNAME,@DBName SYSNAME,@NameOut SYSNAME OUTPUT'
,@ServerInstanceName,@DBName,@NameOut = @Name OUTPUT
IF NOT ISNULL(@Name,'')=''
BEGIN
DECLARE @SqlCreateLogin nvarchar(max)
DECLARE @SqlCreateUser nvarchar(max)
--Create SQL Instance Login
SET @SQLString1 = N'SELECT @NameOut = name
FROM OPENROWSET (''SQLNCLI'',''Server=' + @ServerInstanceName + ';TRUSTED_CONNECTION=YES;'',
''DECLARE @SqlCreateLogin nvarchar(max)
Select @SqlCreateLogin = ''''IF NOT EXISTS (
SELECT 1
FROM sys.server_principals
WHERE name = ''''''''domain\user''''''''
)
CREATE LOGIN [domain\user] FROM WINDOWS;'''''')'
print @SQLString1
EXEC sp_executesql
@SQLString1 ,N'@ServerInstanceName SYSNAME,@DBName SYSNAME,@NameOut SYSNAME OUTPUT',@ServerInstanceName,@DBName,@NameOut = @Name OUTPUT
--Create schema database user login
SET @SQLString2 = N'SELECT @NameOut = name
FROM OPENROWSET (''SQLNCLI'',''Server=' + @ServerInstanceName + ';TRUSTED_CONNECTION=YES;'',
''USE ['+ @DBName +']
DECLARE @SqlCreateUser nvarchar(max)
Select @SqlCreateUser = ''''IF NOT EXISTS (
SELECT 1
FROM sys.database_principals
WHERE name = ''''eADiagUser''''
)
CREATE USER eADiagUser FOR LOGIN [domain\user];
EXEC sp_addrolemember db_datareader, [eADiagUser];'''''')'
EXEC sp_executesql
@SQLString2 ,N'@ServerInstanceName SYSNAME,@DBName SYSNAME,@NameOut SYSNAME OUTPUT',@ServerInstanceName,@DBName,@NameOut = @Name OUTPUT
-- Add domain/user login to SQL instance
EXEC sp_executesql @SqlCreateLogin
-- Add user to Database and assign db_datareder permission
EXEC sp_executesql @SqlCreateUser , N'@DBName SYSNAME',@DBName
END
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply