Query users in a Security Group with LDAP

I have a linked server set up and working correctly. I can create a query to get all the users from active directory with something like this:

SELECT [name], [samaccountname] from OpenQuery( ADSI,
'SELECT name, samaccountname FROM ''LDAP://DC=domain,DC=com'' WHERE objectClass = ''user'' and objectCategory=''Person''')

Now I am trying to select all the users in a specifed security group, but I am not having much luck. What is the best way to get this?

Thanks much.If that can't be done, is there anyway to check if a user is a member of a group or not through a linked server?sql

query to run to find SQL SP upgrade successful

I want to run a query in QA om each server to ensure that I have upgraded
all my SQL Servers to SP3a along with the MS03-031 SQL security patch
Thanks
Hassan wrote:
> I want to run a query in QA om each server to ensure that I have
> upgraded all my SQL Servers to SP3a along with the MS03-031 SQL
> security patch
> Thanks
From T-SQL just execute @.@.version (SP3 is version 760). Since that
hotfix does not modify the SQL Server version (as it can be applied to
both SQL 7 and 2000), you need to follow this article to see if the
patch is applied:
http://support.microsoft.com/?kbid=815495
David Gugick
Imceda Software
www.imceda.com
|||On Sat, 9 Apr 2005 20:57:37 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:

>I want to run a query in QA om each server to ensure that I have upgraded
>all my SQL Servers to SP3a along with the MS03-031 SQL security patch
>Thanks
SELECT ServerProperty('ProductLevel')
will give you a very succinct answer as to which service pack has been
applied.
Andrew Watt
MVP - InfoPath
|||Hi
MS03-031 is build 818.
SELECT SERVERPROPERTY('ProductVersion') will return for the minor version,
760 for SP3 and 818 for MS03-31
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e23LCVZPFHA.3076@.tk2msftngp13.phx.gbl...
> Hassan wrote:
> From T-SQL just execute @.@.version (SP3 is version 760). Since that hotfix
> does not modify the SQL Server version (as it can be applied to both SQL 7
> and 2000), you need to follow this article to see if the patch is applied:
> http://support.microsoft.com/?kbid=815495
> --
> David Gugick
> Imceda Software
> www.imceda.com

query to run to find SQL SP upgrade successful

I want to run a query in QA om each server to ensure that I have upgraded
all my SQL Servers to SP3a along with the MS03-031 SQL security patch
ThanksHassan wrote:
> I want to run a query in QA om each server to ensure that I have
> upgraded all my SQL Servers to SP3a along with the MS03-031 SQL
> security patch
> Thanks
From T-SQL just execute @.@.version (SP3 is version 760). Since that
hotfix does not modify the SQL Server version (as it can be applied to
both SQL 7 and 2000), you need to follow this article to see if the
patch is applied:
http://support.microsoft.com/?kbid=815495
David Gugick
Imceda Software
www.imceda.com|||On Sat, 9 Apr 2005 20:57:37 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:

>I want to run a query in QA om each server to ensure that I have upgraded
>all my SQL Servers to SP3a along with the MS03-031 SQL security patch
>Thanks
SELECT ServerProperty('ProductLevel')
will give you a very succinct answer as to which service pack has been
applied.
Andrew Watt
MVP - InfoPath|||Hi
MS03-031 is build 818.
SELECT SERVERPROPERTY('ProductVersion') will return for the minor version,
760 for SP3 and 818 for MS03-31
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e23LCVZPFHA.3076@.tk2msftngp13.phx.gbl...
> Hassan wrote:
> From T-SQL just execute @.@.version (SP3 is version 760). Since that hotfix
> does not modify the SQL Server version (as it can be applied to both SQL 7
> and 2000), you need to follow this article to see if the patch is applied:
> http://support.microsoft.com/?kbid=815495
> --
> David Gugick
> Imceda Software
> www.imceda.com

query to run to find SQL SP upgrade successful

I want to run a query in QA om each server to ensure that I have upgraded
all my SQL Servers to SP3a along with the MS03-031 SQL security patch
ThanksHassan wrote:
> I want to run a query in QA om each server to ensure that I have
> upgraded all my SQL Servers to SP3a along with the MS03-031 SQL
> security patch
> Thanks
From T-SQL just execute @.@.version (SP3 is version 760). Since that
hotfix does not modify the SQL Server version (as it can be applied to
both SQL 7 and 2000), you need to follow this article to see if the
patch is applied:
http://support.microsoft.com/?kbid=815495
--
David Gugick
Imceda Software
www.imceda.com|||On Sat, 9 Apr 2005 20:57:37 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>I want to run a query in QA om each server to ensure that I have upgraded
>all my SQL Servers to SP3a along with the MS03-031 SQL security patch
>Thanks
SELECT ServerProperty('ProductLevel')
will give you a very succinct answer as to which service pack has been
applied.
Andrew Watt
MVP - InfoPath|||Hi
MS03-031 is build 818.
SELECT SERVERPROPERTY('ProductVersion') will return for the minor version,
760 for SP3 and 818 for MS03-31
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e23LCVZPFHA.3076@.tk2msftngp13.phx.gbl...
> Hassan wrote:
>> I want to run a query in QA om each server to ensure that I have
>> upgraded all my SQL Servers to SP3a along with the MS03-031 SQL
>> security patch
>> Thanks
> From T-SQL just execute @.@.version (SP3 is version 760). Since that hotfix
> does not modify the SQL Server version (as it can be applied to both SQL 7
> and 2000), you need to follow this article to see if the patch is applied:
> http://support.microsoft.com/?kbid=815495
> --
> David Gugick
> Imceda Software
> www.imceda.com

query to obtain linked server properties

Im trying to find the table/column that stores the linked server security
property info such as
For a login not defined in the list above, connections will:
Not be made
Be made without using a security context
Be made using the logins security context
Be made using this security context ..
I want to generate a query that will give me a list of all linked servers
defined along with these settings . Also what Server type it is..whether its
a SQL Server or another data source.. I looked at sysservers alreadyHi Hassan,
Check out 'sp_helplinkedsrvlogin' That gives you what you are looking
for.
Regards,
-Manoj

Query to another database

Dear all,

I have views in database that select records from a table in another database in the same server.
Can I set security in such a way for not to give direct access to the table. I know than it is possible if a table and views are in the same database and have the same owner, but I failed to get the same for different databases.

Thanks.

The following thread includes an example of how to achieve this via signed code:

controlling security through stored procedures -- 2005 behaviour

Also, for another option, search for "cross database ownership chaining" in Books Online. There is a server option that enables this. BOL contains information on it.

Thanks
Laurentiu

|||Thank you

Query Through Internet

I have a security setup question: I have a group of employees that must
be able to make ad hoc queries against data stored on the SQL Server
computer via the Internet. I have one SQL computer and one IIS computer.
Many of these employees do not have computers that use a Microsoft
operating system or Web browser. I know I must use IIS virtual
directory, which is configured for Basic Authentication right now. But,
in addition to these known users, how would I also configure for
anonymous users? Would I then use the IIS Intenet Guest account?
Help apprecaited. Thanks
Frank
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Yes, if you wish anonymous users, you must add the login IUSR_servername as
a valid SQL login and give it appropriate permissions...
"Frank Py" <fpy@.proactnet.com> wrote in message
news:uiJ1mGnuDHA.1224@.TK2MSFTNGP09.phx.gbl...
> I have a security setup question: I have a group of employees that must
> be able to make ad hoc queries against data stored on the SQL Server
> computer via the Internet. I have one SQL computer and one IIS computer.
> Many of these employees do not have computers that use a Microsoft
> operating system or Web browser. I know I must use IIS virtual
> directory, which is configured for Basic Authentication right now. But,
> in addition to these known users, how would I also configure for
> anonymous users? Would I then use the IIS Intenet Guest account?
> Help apprecaited. Thanks
> Frank
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Query the Security Login or Security User of the current person logged in

How do you find out which group membership "Login or User" the current user has permissions to. I currently use Suser_name() for the current username but how do I see what security login or security user he is assosicated to?

I didn't understand what you meant by Security login/Security User.

Do you meant schema name of the current user.. If yes then the following query will help you.

Code Snippet

Select

Loginname = SUSER_SNAME()

, SchemaName = USER_NAME()

|||

Hi,

I think you want this:

Code Snippet

SELECT

G.name As "group_name",

U.name As "user_name"

FROM

sys.database_role_members M

JOIN

sys.sysusers G WITH(NOLOCK)ON M.role_principal_id = G.uid

JOIN

sys.sysusers U WITH(NOLOCK)ON M.member_principal_id = U.uid

WHERE

U.name = @.user_name

Regards,

Janos

|||I ran this as my sysadmin and it through back dbo, but when running as a user I get blanks. Where is it pulling the information from?|||

Hi,

why do not use the system stored procedure sp_helpuser?

Code Snippet

declare @.username sysname

set @.username =user_name()

execsp_helpuser @.username

Query the Security Login or Security User of the current person logged in

How do you find out which group membership "Login or User" the current user has permissions to. I currently use Suser_name() for the current username but how do I see what security login or security user he is assosicated to?

I didn't understand what you meant by Security login/Security User.

Do you meant schema name of the current user.. If yes then the following query will help you.

Code Snippet

Select

Loginname = SUSER_SNAME()

, SchemaName = USER_NAME()

|||

Hi,

I think you want this:

Code Snippet

SELECT

G.name As "group_name",

U.name As "user_name"

FROM

sys.database_role_members M

JOIN

sys.sysusers G WITH(NOLOCK) ON M.role_principal_id = G.uid

JOIN

sys.sysusers U WITH(NOLOCK) ON M.member_principal_id = U.uid

WHERE

U.name = @.user_name

Regards,

Janos

|||I ran this as my sysadmin and it through back dbo, but when running as a user I get blanks. Where is it pulling the information from?|||

Hi,

why do not use the system stored procedure sp_helpuser?

Code Snippet

declare @.username sysname

set @.username = user_name()

exec sp_helpuser @.username