Pb with login name's under sql server 2000

Hello,
I am searching a way to use login names like
SQLAccessGroup{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}
in sql scripts.
Standard sp like sp-changedbowner refuses them.
Thanks for help.
CSHi CS,
As for the "SQLAccessGroup{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}" you
mentioned, do you mean the SID of a certain sqlserver or windows login name
in the SQL Server instance?
If this is the case, I think you need to translate sid into its
corresponding loginname (sql login name or windows account/group name)
first before calling the sp_changedbowner stored procedure. This is because
the "login" parameter of the sq_changedbowner SP must be an already
existing Microsoft SQL Server login or Microsoft Windows NT user(rather
than the raw SID).
#sp_changedbowner
http://msdn.microsoft.com/library/e...0s2.asp?frame=t
rue
If you will only get the SID first in your scenario, I'd suggest you
consider the following options to translate the SID to login name and pass
the login name to the sp_changedbowner:
1. use the "SUSER_SNAME" T-SQL function to translate login sid into login
name
#SUSER_SNAME (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms174427.aspx
2. Since all the logins of a SQL server instance is stored in the
syslogins system view, you can manually query it and find the loginname
associated with the certain SID (not quite recommended )
for example:
=======================
declare @.sid nvarchar(100)
set @.sid =
suser_sname(0x0105000000000005150000005D
28F57FD53AD8354354E02A18B20200)
EXEC sp_changedbowner @.sid
=======================
In addition, the format of the SID stored in SQL Server is abit different
from the {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}, you'll need to do some
modification before passing them into the certain translate function.
Hope this helps.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||In fact, it is MS CRM that has created this login on its installation, and
our dba& complains that it is a non generic way to name login.
Whe have discovered thta it is normal that changedbowner fails because this
lotgin is an ad group and not an AD user.
Thanks anyway.CS
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> a crit dans le mess
age
de news: k56sX1CvGHA.5976@.TK2MSFTNGXA01.phx.gbl...
> Hi CS,
> As for the "SQLAccessGroup{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}" you
> mentioned, do you mean the SID of a certain sqlserver or windows login
> name
> in the SQL Server instance?
> If this is the case, I think you need to translate sid into its
> corresponding loginname (sql login name or windows account/group name)
> first before calling the sp_changedbowner stored procedure. This is
> because
> the "login" parameter of the sq_changedbowner SP must be an already
> existing Microsoft SQL Server login or Microsoft Windows NT user(rather
> than the raw SID).
> #sp_changedbowner
> [url]http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_ca-cz_30s2.asp?frame=t[/ur
l]
> rue
> If you will only get the SID first in your scenario, I'd suggest you
> consider the following options to translate the SID to login name and pass
> the login name to the sp_changedbowner:
> 1. use the "SUSER_SNAME" T-SQL function to translate login sid into login
> name
> #SUSER_SNAME (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms174427.aspx
> 2. Since all the logins of a SQL server instance is stored in the
> syslogins system view, you can manually query it and find the loginname
> associated with the certain SID (not quite recommended )
>
> for example:
> =======================
> declare @.sid nvarchar(100)
> set @.sid =
> suser_sname(0x0105000000000005150000005D
28F57FD53AD8354354E02A18B20200)
> EXEC sp_changedbowner @.sid
> =======================
> In addition, the format of the SID stored in SQL Server is abit different
> from the {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}, you'll need to do so
me
> modification before passing them into the certain translate function.
> Hope this helps.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Cs,
Thanks for the followup and let me know how everything is going on.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Pattern Matching - Searching for Numeric or Alpha or Alpha-Numeric characters in a string

Hi,

I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.

Wildcard

Meaning

%

Any string of zero or more characters.

_

Any single character.

[ ]

Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).

Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).

Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

using single quotes and the % wildcard instead of Access' double quotes and * wildcard.

Just putting this out there for anybody else that is new to SQL, like me.

Regards,

Patrick Briggs,
Pasadena, CA

Patrick Briggs wrote:

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

It is not quite the same. The LIKE pattern in TSQL will also match values like '0123A' and '1248mkfaliw' whereas the one in Access doesn't. The correct way to specify it is to do below:

WHERE Gift_Date NOT LIKE replicate('[0-9]', 4)

The replicate function just simplifies the repetition of the same pattern multiple times.

sql