Query Users In A Security Group With LDAP

Aug 11, 2004

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.

View 3 Replies


ADVERTISEMENT

Query LDAP/ADSI For Group Members?

May 16, 2007

I am trying to get members of an Active Directory group by querying the AD server from Transact-SQL (SQL Server 2005). Although there does not seem to be any written list of LDAP attributes that can be queried in AD (or I am not finding it), I have gotten this far:



SELECT * FROM

OPENQUERY( MYSERVER,

'SELECT cn, msExchHomeServerName, userPrincipalName FROM ''LDAP://CN=Users,DC=MYSERVER,DC=COM'' WHERE userPrincipalName=''*'' ')



This gives me a user list. But I can't find the syntax or attribute name(s) to query in order to get the membership of a specific group - for example, the group "SQL_Developers".



Anybody out there familiar enough with LDAP, AD and OPENQUERY() to give me a hand?



Thanks....



Tom

View 2 Replies View Related

SQL Security :: Users Are Able To Login To Server Without Any Login Names Or Being Part Of A Group

Jun 5, 2015

I have a server that has 20 databases . I have tested with few users with different level of access and all of them were able to connect to the server and also see, select, update , delete from a particular database which is kind of weird because they do not have a user login associated or mapped to that database. I checked and no user is part of any group in AD that would give them permission to connect . I need a query that would find the permission path of a user. I already queried with xp_logininfo but I am not getting any thing.

View 9 Replies View Related

SQL Security :: Did Not Create Any New Users And There Are No Other Users Listed In Accounts Section

Sep 28, 2015

I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.

View 2 Replies View Related

LDAP - HOW DO I CONNECT TO LDAP DB FROM SQL SERVER 2005?

Dec 12, 2006

This is something that should not be so difficult, but it is proving to be very cumbersome.
I need to query an LDAP database on a Linux server FROM a sql server, namely sql server 2005.
I have found bits and pieces, but can€™t seem to put it all together yet €“ it should be easy, but it€™s proving to be very difficult.
It seems like you can either link an LDAP server through sp_addlinkedserver OR through enterprise manager/sql server studio, you can create a LINKED SERVER.
The problem is HOW to do this.
Here are the parameters for the ldap server:
Server name: serverabc
Ldap info (which I don€™t know how to label) is: 'ou=Hosts,dc=mc,dc=vanderbilt,dc=edu'.
I was able to somehow get this to work last week, but now it€™s not working - it didn't return any data, as i don't have any ldap hierarchy specified, but at least it came back with "command completed successfully":
SELECT * FROM OPENQUERY( [serverabc],
'SELECT * FROM ''LDAP://serverabcu/ OU=Hosts,DC=mc,DC=vanderbilt,DC=edu''
')
With all the ldap usage out there, this is really frustrating not being able to find out how to do this.
Any help is greatly appreciated.
Thanks


View 3 Replies View Related

Difference Between Database -- &&> Users And Security --&&> Users

Nov 28, 2006

Hi Team,

In SQL Enterprise Manager, when we expand "Database -->Users", we see the

users there. When we expand "Security --> logins" we see the same users there.

Can you differentiate these two.

Thanks

Santhosh

View 1 Replies View Related

SQL Security :: Running Job As Windows Security Group

Oct 18, 2015

Is there any possibility to schedule SQL job execution as Windows Security Group? I need to run powershell script through SQL job with one of this group member's permissions. 

View 4 Replies View Related

Use An Existing AD DL Security Group For Security Role

Jun 18, 2007

I want to use an Active Directory security group that is a Distribution List for a new role assignment for an existing report. Can someone tell me if this is possible? I get an error each time I try:














The user or group name <DLName> is not recognized. (rsUnknownUserName)"

View 1 Replies View Related

LDAP Query Returning Erroneous Values

Jan 24, 2007

I'm trying to query an LDAP server from a stored procedure written for the CLR but not getting the expected results.

The code is as follows:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub LDAP_UserExists(<Out()> ByRef exists As Boolean, ByVal username As SqlString)

Dim adspath As New StringBuilder()
adspath.Append(LDAP://[.......]/ou=Members/cn=)
adspath.Append(username)

If username.ToString().Length > 0 Then
Dim uobject As New DirectoryEntry(adspath.ToString(), "", "", System.DirectoryServices.AuthenticationTypes.Anonymous)
If Not (uobject Is Nothing) Then
exists = True
Else
exists = False
End If
End If

End Sub

The same code works fine from an ASP.NET. If I deploy the code and execute it with

exec LDAP_UserExists 'username'

I receive the error

Error converting data type varchar to bit.

And if I right-click and select "Execute Stored Procedure..." I receive @exists = 1 and Return Value = 0, regardless of the value I pass in as the username parameter.

Given that the same code works correctly on the ASP.NET page I suspect that this error has something to do with the <out()> parameter in the stored procedure declaration.

Can anyone suggest the correct method of performing this query?

View 1 Replies View Related

Query LDAP/Active Directory In Stored Procedure

Apr 28, 2004

I know you can access LDAP via ADO.NET and the ADsDSOObject Provider.

Is there away to do it within a SQL Server Stored Procedure?

Thanks,
Tim

View 2 Replies View Related

SQL 2005 LDAP Query Error: Msg 7321, Level 16

Aug 4, 2006

Hello I am trying to run a query via tsql against ad. Below is the error I am getting. I have read the http://msdn2.microsoft.com/en-US/library/ms190803.aspx and changed the domain but still having issues. Any help would be appreciated.

 

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',

'ADSDSOObject', 'adsdatasource'

GO

SELECT *

FROM OPENQUERY( ADSI,

'SELECT Name, SN, ST

FROM ''LDAP://ADSISrv/ OU=Users,DC=XXXXX,DC=LOCAL''

WHERE objectCategory = ''Person'' AND

objectClass = ''user''')

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "SELECT Name, SN, ST

FROM 'LDAP://ADSISrv/ OU=Users,DC=XXXXX,DC=LOCAL'

WHERE objectCategory = 'Person' AND

objectClass = 'user'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

View 14 Replies View Related

Transact SQL :: LDAP Query Checking When Created Within Last 7 Days

Nov 6, 2015

I currently have an ldap query in a stored procedure that is working fine but is checking the 'whenCreated' attribute against a hardcoded data.

SELECT * FROM OpenQuery (
  ADSI,  
  'SELECT  whenCreated,
  whenChanged,
  telephoneNumber,

[Code] .....

How can I modify the hardcoded date (''20130101000000.0Z'') to check against current_date - 7 days?

View 7 Replies View Related

Reporting Services :: Pass A Parameter To LDAP Query In SSRS

Aug 20, 2015

Is it possible to use a parameter in a ldap query using the ADsDSOObject provider?  I keep getting an error "The ICommandWithParameters interface is not supported by the "ADSDSOObject" provider".  Command parameters are unsupported with the current provider.

I don't have a linked server on my DB server to Active Directory so I'm just querying in the SSRS report design.  Here is my query for my dataset.  If I hard code an example it works.  Just doesn't work when I pass a parameter. I've tried making it an expression (= sign), Tried several syntax's, Tried everything I can think of.  Is this possible? or do I just need to push for a linked server?

="SELECT sAMAccountName, displayName, distinguishedName " +
"FROM 'LDAP://DC=xxxx,DC=xx,DC=xx,DC=xx,DC=xx,DC=xx' " +
"WHERE objectCategory = 'Person' " +
"AND objectClass = 'user' " +
"AND memberOf = '" + @GlobalGroup + "'"

View 3 Replies View Related

ADSI LDAP Query 1000 Record Limit For MSSQLServer Linked Server

Jul 23, 2005

Hi,I have successfully set and used a linked server to query ADSI.Since this question also concerns MSSQLServer, I've cross posted it --I hope this is not a breach of etiquette.I have successly created a view based on the linked server.Unfortunately, it only shows 1000 records, and there does not seem tobe any way to set the Page Size.I found the following:http://support.microsoft.com/defaul...kb;en-us;243281Which seems to imply that the default can be set by changing registrykey: "HKEY_CURRENT_USERSoftwarePoliciesMicrosoftWind owsDirectoryUI"I have set this key, and also set it for the user account under whichMSSQLServer runs. The value persists after a reboot. The Domain Grouppolicy sets the default to 15000.This behaviour is not restricted to the linked server. If I use thescript found here:http://hacks.oreilly.com/pub/h/1121 I can access morethan 1000 records, but only if I set the "Page Size" property. If Icomment it out to let the default hold, it is 1000.It must be settable SOMEWHERE or the whole linked server thing is ofvery limited use.At present, the best solution I've been able to come up with is to usethe above script modified to run as a DTS package. Yuck.TIA,BM

View 2 Replies View Related

SQL Security :: Adding 3 Users To A Login?

Oct 28, 2015

I have to give three users read access to a database.  My manager told me to map them to a User called "zxDatabase.Read.ug".  I scripted  the User and got this...

CREATE
USER[zxDatabase.Read.ug]
FORLOGIN[zxDatabase.Read.ug]

I don't see a Login at the server level named zxDatabase.Read.ug, so this is confusing.  Where is this login in SSMS?

How do I map my three users to user zxDatabase.Read.ug. Or am I not understanding correctly?  Do I just add there windows login to the database as Users and configure them the same as zxDatabase.Read.ug

View 4 Replies View Related

SQL Security :: Users Logged In Server

Jun 12, 2015

We wanted to know list of  users who had logged into the SQL Server from last 10 to 30 days.

View 2 Replies View Related

ASPNETDB Deploy With Users/Roles Security

Apr 10, 2007

I have developed a site on my local machine utilizing the built-in security/roles provides by ASP.NET 2.0.  I am trying to publish the web application to a production server.  It will publish correctly, however the ASPNETDB is not recognized.  The folder app_data is created with the proper database files.  My question is how do I set this up to use the data I have created in this database?  I've searched on how to accomplish this but haven't found any definitive answers.  I have found ScottGu's blog to be useful, but I was wondering if there is a different way.
1 - Do I push the data to a SQL Server install via SQL scripts?
2 - Can I link to the ASPNETDB that is created locally on my machine?
3 - If I make changes to the application and re-publish, is there a way to keep from overwriting the ASPNETDB on the server?
I am a unsure of how the built-in roles/security works when you are ready to push something to production.  If someone wouldn't mind pointing me in the right direction, it would greatly be appreciated.  Any literature, advice, links, etc.. would be great.
 
- Thanks
Andrew 

View 4 Replies View Related

SQL Security :: How To Organize And Secure Database Users

Apr 21, 2015

Is there any way in which I could distinguish a group of Database users from the other users. Say for e.g. store the Database users hierarchically, etc. Adding a Prefix/Suffix to the user name as a distinguish-er will not work in my case. I want to restrict the deletion of these Database users. Even the login with sysadmin or serveradmin rights should not be able to DROP those Database users.

View 11 Replies View Related

SQL Security :: Assign Database Permission To All The Users?

May 31, 2015

How can I assign permission to new database for all the existing user in SQL.?

View 3 Replies View Related

How To Get The Folder Wise Security Info Of All The Users?

Dec 21, 2006

Hi,
The Report Manager portal has many folders. For each folder there are specific users with different roles.
I am trying to figure out the way to extract User, folder wise security data. I want to run a query and retrieve users name, the folders they have access to and the user role corresponding to that folder.

Use ReportServer

SELECT u.UserName, r.RoleName FROM users u, policyuserrole pur, roles r

WHERE pur.UserID=u.UserID AND pur.RoleID=r.RoleID
The above query fetches all the users and their roles.
The folder information corresponds to Path column of Catalog table. Am unable link this table with the above query.
TIA

View 1 Replies View Related

SQL Security :: Domain Admin Users Cannot Login

Jun 12, 2015

Is SQL Server sensitive to Domain group name? Like "Domain Admin"?

I have user that belong to "myDomainDomain Admin" group. Group is in SQL as sysadmin but user cannot login using domain credentials. When I move that user to a different domain group which that group is in SQL again as sysadmin my user is able to login. 

Environment: SQL 2008 Standard Edition. 

View 12 Replies View Related

Problem Getting Some Users To Be Able To Run Reports (probably Security Of Some Sort)

Jan 12, 2007

I am able to deploy and run reports from my screen at work, but some non-developers cannot run them. They can see the reports, but get the following error message when they click on the report:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors
--------------------------
-----

- I assume the preceding error has something to do with credentials. I am using Windows security authentication for this. One user has worked through this and is now getting cannot run report query (one of the report queries in the report). Help would be appreciated.

View 3 Replies View Related

Return Users Not In A Group

Jun 19, 2007

I need some assistance creating a query. I have users table, groups table, and UsersGroups table. Basically a single user could belong to one or more groups. I need to be able to return the users that are not in a specified groups. Table def are below.

USERS:
--------
ID (Primary Key)
Username
Password


GROUPS:
------------
ID (Primary Key)
GroupName



USERSGROUPS:
-----------------
ID (Primary Key)
GroupID
UserID


Any help is greatly appreciated!
Thanks!

View 4 Replies View Related

Get Last Posts Of A Group Users

Sep 11, 2007



Hello dear coders

I have two tables (Members and Blogs)

I want to get the last posts of my user group 2 for each user i want to see this sentences

i-developer : last post on 5 May 2007 at 3.55 am
kingalone: last post 0n 6 June 2007 at 400 am

so i tried a lot of sql queries but none of them succed

my codes are;




SELECT Members.Username, Blogs.IsPublished, Blogs.PublishedDate

FROM Members INNER JOIN

Blogs ON Members.MemberID = Blogs.MemberID

WHERE (Members.RoleID = 2)

ORDER BY Blogs.EntryID

group by Username

select Max(EntryID),MemberID from Blogs where RoleID = 2

with FilteredLastPost ( MemberID,EntryID,PublishedDate)

as

(

select MemberID,EntryID,PublishedDate from Blogs where IsPublished = 1 and MemberID in( (select MemberID from Members where RoleID = 2)) order by EntryID desc, MemberID asc

)

select MemberID from FilteredLastPost

select distinct Blogs.MemberID,EntryID,PublishedDate from Blogs

right join Members on Members.MemberID = Blogs.MemberID

where not PublishedDate ='' and IsPublished = 1 and Members.RoleID = 2

order by EntryID desc, PublishedDate desc









select DISTINCT MemberID, EntryID, PublishedDate DISTINCT IsPulished from Blogs where IsPublished = 1 and not PublishedDate ='' and MemberID in ( select MemberID from Members where RoleID = 2 )

View 3 Replies View Related

SQL Security :: Maximum Number Of Users That Could Be Created For Any Database

Jun 16, 2015

Is there any maximum cap on the number of users that could be created in a given database of MS-SQL Server?

View 4 Replies View Related

Schema-level Security For Multiple Users In One Database

Apr 17, 2007



My developers would like a 'sandbox' database with full ddl and dml permissions, however, they do not want others to read/change/drop their objects. With SQL 2005, can DDL permissions be granted to a user at the schema level? I'd rather not set up a database for each developer.

View 3 Replies View Related

SQL Security :: Restricting Users To Login To Database Using SSMS

Jun 9, 2015

we have an application which lets users connect to production database with windows credentials, They are able access the sql tables too with windows login. I want to restrict them from accessing the sql tables. How do I do that.?I tried a db_deny but that prevented them from accessing the application too.

View 10 Replies View Related

Users Do Not InheritInheritance Of Group Rights

Apr 23, 2008

I gave to

View 1 Replies View Related

Security Report - List Of All Active Users In Database With Their Privileges

Aug 28, 2014

I am looking to write a script that would give the list of all active users in a database with their privileges.

Sample would look something like this.

Login User Schema IsdbOwner canWrite canRead
xyz xyz dbo N Y Y

View 2 Replies View Related

SQL Security :: Can Hide Databases From Users Who Don't Have Access In Management Studio

Apr 21, 2015

I have a client who would like to access his database via SQL Server Management Studio. I created a login for him. This login has access only to his database on the server (I set this up using the user mapping), the Securables has Connect to SQL checked (otherwise he won't see his database).  When I test this login, he is able to see all the other databases on the server, though when he clicks on the ones he doesn't have access to, it will tell him that he can't access that database.  He can access his database alright.  My question is, can I hide all the other databases from him, and only display that only database he has access to?  I have tried many different ways, I can't seem to be able to do so.

View 6 Replies View Related

Recovery :: Adding Users To Availability Group?

Nov 9, 2015

I am in the process of rolling out a pair of SQL 2014 servers. I have setup an Availability Group, Listener and databases. It's my understanding that I will be giving the listener name to our developers so that they can do their work. In testing, I noticed that If I am using Studio Manager and connected to the the AG using the listener name, when I setup a user in security the user is only added to the active primary node. Is there a way to add a user to both servers in one shot instead of having to install on both servers? 

View 5 Replies View Related

Advice On Security Model For XML Web Services For Many Users In Active Directory Role

Feb 21, 2006

I am working on the security model for an application that will be used by 100s of users with a dedicated SQL 2005 database for this application and access via SQL XML Web Services.

The client has asked to make it "open" during alpha testing such that anyone can access the web services without having to set them up first. Is there a way to do this? The best I can figure is to use mixed mode security and hard code a login and password. Any method using Windows authentication would require that I add every user at a minimum to the database.

In production, all users will have an active directory role specified that determines if they should have access to the web services or not. However, it is my understanding that to use Windows authentication, I would still need to add each individual user at a minimum as a Login to the SQL Server, and under best practices also as database users with permissions granted to the endpoint.

Am I correct in the above, or is there a more efficient way to achieve these results?

Thanks

-L

View 1 Replies View Related

Issues Setting Up Users To Authenticate Via NT Group Into MSSQL

Feb 26, 2004

I have a NT Group of users that need to connect to MSSQL db using integrated auth and run a stored proc. I have got this concept to work for individual windows users but not a group.

Does anyone have any ideas?

(Win XP, MSSQL 2000 SP3, Windows Group = "SQLUsers", member is "User2")

--- TSQL ---

USE master
GO
sp_grantlogin 'pcSQLUsers'
GO
sp_defaultdb 'pcSQLUsers', 'theDB'
GO
USE theDB
GO
sp_grantdbaccess 'pcSQLUsers', 'SQLUsers'
GO
GRANT EXECUTE ON [dbo].[uspThing] TO SQLUsers
GO

-- Trying to connect with OSQL.EXE:

> osql -d theDB -E
Login failed for user 'pcUser2'.


BUT.....

runing it for the individual user:

---TSQL

USE master
GO
sp_grantlogin 'pcUser2'
GO
sp_defaultdb 'pcUser2', 'theDB'
GO
USE theDB
GO
sp_grantdbaccess 'pcUser2', 'SQLUsers'
GO
GRANT EXECUTE ON [dbo].[uspThing] TO SQLUsers
GO

---- now trying to connect with OSQL.EXE as User2:

>osql -d theDB -E
1> exec uspThing
2> go
3> data....

all is fine......

I can't use the user method - to much admin... any ideas!?

PK

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved