February 22, 2011 at 7:45 am
Hello folks,
I don't bump into this so often, and after extensive googling I haven't been able to put 2 and 2 together that equals something that could be useful.
I'd like for this to be a cursor. So that it would go through all the rows in a table. The logic would be as follows.
1. select ColumnValue that is not "sometext\"
2. add "sometext\" + ColumnValue
3. update the relevant column in the relevant row with this new value.
I following is to just further illustrate my intent.
declare @login_ varchar(255)
declare @sql varchar(255)
set @login_ = (select LOGIN from TABLE where LOGIN not like 'sometext\%')
set @sql = 'update WORKSTAT set LOGIN = @login_'
exec sp_excutesql @sql
I'm at a loss and would greatly appreciate any informative pointers.
Regards.
February 22, 2011 at 8:27 am
First I'm confused why you would want to use a cursor? SQL Server isn't very efficient with cursors and this seems like it could easily be a set based solution.
create table MyTable(
myID int
, myChar varchar(20)
)
go
insert MyTable select 1, 'A'
insert MyTable select 1, 'B'
insert MyTable select 1, 'C'
go
update MyTable
set MyChar = '1' + MyChar
February 22, 2011 at 8:46 am
I'm assuming you want a cursor to dynamically create Integrated Security Logins from existing SQL Logins by concatenating the domain name to the username.
If that's case, you should be able to query the sys.server_principals catalog view with something like this & then create any needed logins manually or using a cursor approach.
SELECT *
FROM sys.server_principals
WHERE type = 'S' -- SQL Logins
AND name != 'sa' -- 'sa' probably isn't a domain account too.
AND name NOT LIKE '##%'
AND is_disabled <> 1 -- Ignore disabled logins
AND principal_id NOT IN
(SELECT principal_id -- Exclude the existing matches
FROM sys.server_principals
WHERE (type = 'U' OR type = 'G') -- U = Windows Logins, G = Windows Groups
AND is_disabled <> 1)
Here's a quick mod I made to the orignal query, that should give you what I think you're looking for.
DECLARE @DomainName nvarchar(max);
SET @DomainName = 'Microsoft\'; -- Set your domain name here.
DECLARE @Logins TABLE
(
Id int IDENTITY(1,1) NOT NULL,
LoginName nvarchar(max) NOT NULL,
DefaultDatabase nvarchar(max) NOT NULL DEFAULT ('master')
)
INSERT INTO @Logins
SELECT @DomainName+name AS 'login_name_to_create'
,default_database_name
FROM sys.server_principals
WHERE type = 'S' -- SQL Logins
AND name != 'sa' -- 'sa' probably isn't a domain account too.
AND name NOT LIKE '##%'
AND is_disabled <> 1 -- Ignore disabled logins
AND principal_id NOT IN
(SELECT principal_id -- Exclude the existing matches
FROM sys.server_principals
WHERE (type = 'U' OR type = 'G') -- U = Windows Logins, G = Windows Groups
AND is_disabled <> 1)
DECLARE @_sql nvarchar(max);
DECLARE @_LoginName nvarchar(max);
DECLARE @_DefaultDatabase nvarchar(max);
WHILE (SELECT COUNT(*) FROM @Logins) > 0
BEGIN
SET @_LoginName = (SELECT TOP 1 LoginName FROM @Logins);
SET @_DefaultDatabase = (SELECT TOP 1 DefaultDatabase FROM @Logins);
SET @_sql = 'CREATE LOGIN ['+@_LoginName+'] FROM WINDOWS WITH DEFAULT_DATABASE=['+COALESCE(@_DefaultDatabase, 'master')+'];';
SET @_sql = @_sql+CHAR(13)+CHAR(10)+'GO';
--EXEC (@_sql); -- Uncomment this to execute the SQL.
PRINT (@_sql);
DELETE FROM @Logins WHERE LoginName = @_LoginName
END
Looks like the code highlighter is treating the slash as an escape character. This should run though.
February 22, 2011 at 9:03 am
Well the assumption was not so far from the truth. But it's not sql server internals related.
However you are correct in that I need to add a domain prefix to a column that contains usernames. It's just not related to sql server.
@steve-2 - A cursor was the only way I could think of to do this, it's not a requirement. My requirements are as follows.
1. To be able to schedule this in a job.
2. To take the content in all the rows, and add a domain suffix to the login column to those that do not have it.
And I cant blindly do a suffix + columnvalue either, as that would add it up twice for those that do not already have it. But the suffix is fixed, it does not change.
Really appreciate the replies!
February 22, 2011 at 9:11 am
You can modify the WHERE clause to avoid those rows that have the item already. So something like this:
update MyTable
set MyChar = '1' + MyChar
WHERE SUBSTRING( mychar, 1,1 ) <> '1'
Just use your domain value in place of my '1'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply