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.
