How to determine the domain for sp_grantlogin?

I want to EXEC sp_grantlogin from within a stored procedure. I'm passed a
username, but also need the domain - as per the BOL
=====================
Syntax
sp_grantlogin [@.loginame =] 'login'
Arguments
[@.loginame =] 'login'
Is the name of the Windows NT user or group to be added. The Windows NT user
or group must be qualified with a Windows NT domain name in the form
Domain\User, for example London\Joeb. login is sysname, with no default.
=====================
I've been using xp_loginconfig 'default domain', but need an alternative
because I no longer have EXEC permission to xp_loginconfig.
Will SERVERPROPERTY('MachineName') give me the domain? If not, what will?
Thanks in advance for your help,
Hal Heinrich
VP Technology
Aralan Solutions Inc.
--
VP Technology
Aralan Solutions Inc.Hi
Possibly depending on if you have been granted a direct login
select loginame from sysprocesses
where spid = @.@.SPID
or parse the output of:
exec master..xp_cmdshell 'SET USERDOMAIN'
John
"Hal Heinrich" wrote:

> I want to EXEC sp_grantlogin from within a stored procedure. I'm passed a
> username, but also need the domain - as per the BOL
> =====================
> Syntax
> sp_grantlogin [@.loginame =] 'login'
> Arguments
> [@.loginame =] 'login'
> Is the name of the Windows NT user or group to be added. The Windows NT us
er
> or group must be qualified with a Windows NT domain name in the form
> Domain\User, for example London\Joeb. login is sysname, with no default.
> =====================
> I've been using xp_loginconfig 'default domain', but need an alternative
> because I no longer have EXEC permission to xp_loginconfig.
> Will SERVERPROPERTY('MachineName') give me the domain? If not, what will?
> Thanks in advance for your help,
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.
> --
> VP Technology
> Aralan Solutions Inc.|||xp_sqlagent_proxy_account 'GET' would return similar information if your
agent were setup to run under an account on the default domain name you're
after... but that is also an extended stored procedure so your permissions
won't allow for that either.
Assuming you already have accounts added to your SQL server on this default
domain name you're after - you could poll the master..syslogins table for th
e
first / top example with a loginname column value like '%\%' and then use
substring to derive it. (yes, it's a hack).
I take it you are using this as a generic procedure to run on multiple
disparate SQL servers residing in more than one domain? If not, I question
why you'd even need to derive the name since it's typically going to be a
fixed value that you could hard-code (unless your box keeps getting shuffled
from domain to domain during those pesky corporate IT consolidation efforts)
=)
"Hal Heinrich" wrote:

> I want to EXEC sp_grantlogin from within a stored procedure. I'm passed a
> username, but also need the domain - as per the BOL
> =====================
> Syntax
> sp_grantlogin [@.loginame =] 'login'
> Arguments
> [@.loginame =] 'login'
> Is the name of the Windows NT user or group to be added. The Windows NT us
er
> or group must be qualified with a Windows NT domain name in the form
> Domain\User, for example London\Joeb. login is sysname, with no default.
> =====================
> I've been using xp_loginconfig 'default domain', but need an alternative
> because I no longer have EXEC permission to xp_loginconfig.
> Will SERVERPROPERTY('MachineName') give me the domain? If not, what will?
> Thanks in advance for your help,
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.
> --
> VP Technology
> Aralan Solutions Inc.

How to determine sp caller current database?

When executing a stored procedure that is defined in another database, as:

USE db1;

EXEC db2.dbo.sproc;

Is there a way in the stored procedure "sproc" to determine that the caller made the call from db1?

If nothing else, you can use CONTEXT_INFO to retain that information.

To call the stored procedure you can do something like:

Code Snippet

USE db1;

declare @.binVar varbinary(128)
set @.binVar = convert(varbinary(128), 'db1')
set context_info @.binVar

EXEC db2.dbo.sproc;

and to fetch the information from within the stored procedure you can use something like:

Code Snippet

convert(varchar(128), context_info())

( This is assuming that db_name() is not working for you. )

sql

How to determine EXEC permission to an extended stored procedure?

The following proc indicates whether you have EXEC permission to a proc -
however it fails for extended procs. I'd be grateful for a fix!
A good test is @.SPNM = 'xp_sprintf'. Note that the proc is getting a valid
object id for the extended procs.
PROCEDURE procHasExecutePermission
( @.SPNM sysname,
@.HAS bit OUTPUT
) AS
BEGIN
SET NOCOUNT ON
DECLARE @.OID int
SET @.OID = OBJECT_ID(@.SPNM)
IF @.OID IS NULL
IF SUBSTRING(@.SPNM, 1, 3) = 'sp_' OR SUBSTRING(@.SPNM, 1, 3) = 'xp_'
SET @.OID = OBJECT_ID('master..' + @.SPNM)
IF @.OID IS NULL
SET @.HAS = 0
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
END
Thanks in advance for your help,
Hal Heinrich
VP Technology
Aralan Solutions Inc.It doesn't fail (only) for extended procs, it fails for the objects
that begin with 'sp_' or 'xp_', because you are getting the OBJECT_ID
for the object from the master database, but the PERMISSIONS function
accepts only object id-s for objects from the current database. In some
cases, it may look like it's working for some objects from master, but
that's only because the same object id is allocated in the current
database for another object.
If you need to check for objects that may be in master, I would use
another procedure like this:
USE master
GO
CREATE PROCEDURE procHasExecutePermission1
(@.SPNM sysname, @.HAS int OUTPUT) AS
SET NOCOUNT ON
DECLARE @.OID int
SELECT @.OID = id FROM sysobjects
WHERE name=@.SPNM AND xtype IN ('P','X','FN')
IF @.OID IS NULL
SET @.HAS = NULL
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
GO
USE YourDatabase
GO
CREATE PROCEDURE procHasExecutePermission2
(@.SPNM sysname, @.HAS int OUTPUT) AS
SET NOCOUNT ON
DECLARE @.OID int
SELECT @.OID = id FROM sysobjects
WHERE name=@.SPNM AND xtype IN ('P','X','FN')
IF @.OID IS NULL
SET @.HAS = NULL
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
GO
CREATE PROCEDURE procHasExecutePermission3
(@.SPNM sysname, @.HAS int OUTPUT) AS
IF LEFT(@.SPNM,3)='sp_'
EXEC master..procHasExecutePermission1 @.SPNM, @.HAS OUTPUT
IF @.HAS IS NOT NULL RETURN
EXEC procHasExecutePermission2 @.SPNM, @.HAS OUTPUT
I have used sysobjects to check the object type, so if you pass a table
name it will return NULL instead of 0.
You may want to improve these procedures using PARSENAME if you need to
allow procedure names prefixed with the database name. If the database
name may also be other than the current database or the master
database, then it gets complicated... there may be a solution by
calling the PERMISSIONS function from Dynamic SQL.
Razvan