We are using Windows authenication within our system, and I was wondering how it would be possible to determine if the user conected to the SQL SERVER instance was a member of a particular active directory security group?
Thanks.
Current: One common SQL login is being used by SQL DBA on all the servers
New Plan: Creating one windows AD group, adding the DBA's to that group and create as a login with sysadmin server access on all the SQL Server boxes
how to achieve this activity. Creating SQL login is fine but how to change the ownership of various objects, jobs to new login on all servers?
Is it possible to check for Active Directory group.. ie see if the user running the Stored Proc, is in a specific Active Directory Group? Or if I set up Login's using Active Directory, can I get the Login that way... or will it give me the user's account?
View 6 Replies View RelatedIs there a way that we can tell what active directory group the person belongs to that is running the report? I know that you can detect a user id, but I need to access the Active Directory Group that they belong to.
View 1 Replies View RelatedHow do you limit access to data based on Active Directory group membership and/or SQL Server database access?
View 1 Replies View Related
Hello. I am trying to write a report that pulls information in from Active Directory. I have a view created that gets a listing of users and a view that creates a listing of user groups, but I can't seem to figure out how to get all user groups that are associated with the users. This is what I have.
SELECT TOP 100 PERCENT *
FROM OPENQUERY(ADSI, 'SELECT cn, groupMembershipSAM FROM ''LDAP://wmdomain.local'' WHERE objectcategory=''group''') Rowset_1
SELECT *
FROM OPENQUERY(ADSI,
'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn
FROM ''LDAP://wmdomain.local''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')
Rowset_1
Let me know if you have any suggestions!
BJ
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
My question is I have a SQL Server running on Web Server which is a member of a 2000 Active Directory, I only grant access to the database via Global Groups from the Active Directory. When I log onto the database via Windows Authentication the actual user shows up in the master.dbo.sysprocesses table, I can tell what database that process is going to but not how that user is being translated to the Global Group that was actually given access. I need the actual database user name which is the Global Group name that had permissions granted via user defined database roles so that I can do some pre-processing in an ASP.NET application so that I know what parts of a form are updatable or not.
View 1 Replies View RelatedIs it possible to use T-SQL to create a user in Active Directory?
View 7 Replies View RelatedFor code reuse, I am trying to get a table valued function to return users of a given AD group name. I can easily get this with hard-coding the group name. But because OpenQuery wont accept parameters, I can't insert my group name there. And because functions can't call dynamic SQL, I can't do it via dynamic sql. I have seen people do it with CLR, but I rather not go that route. I can use a stored procedure + cursor and iterate through each group and store the results into real tables and create a cache, but I rather query Active Directory itself to save space, but I rather do the caching then the CLR. Any approach I am missing on how to do this?
The following works fine:
SELECT DISTINCT sAMAccountName
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, sn
FROM ''LDAP://OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''
WHERE objectCategory=''Person'' AND objectClass=''USER'' AND memberOf=''CN=SomeGroupName,OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''') a
WHERE sn IS NOT NULL
The following gives me the error:
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
CREATE FUNCTION [dbo].queryADGroupMembers
(
@group nvarchar(255)
)
RETURNS @rtnTable TABLE
[Code] .....
Hello,
I am fairly new to SQL 2005 and Reporting Services.
We are trying to create a report that will display sales data based on group membership from Active Directory.
For example, if USER1 logs in and looks at a Year to Date Sales report, it will only show data that pertains to his group. If USER2 logs in and accesses the same report, it will display different Year to Date information because he is in a different group.
Background Information: We are running SQL 2005 Enterprise Edition Service Pack 2 with Analysis and Reporting Services. We are delivering the reports through a Sharepoint site.
Please let me know if anyone has a good way to tackle this.
Thanks,
Justin
Greetings,VWD EE and other Tools do not have problems working O.K. on my machine, when I am logged-on as restricted(limited) user, both environment and local web server are functioning, and it was MS effort to do it right.BUT the Problem is, when I try to connect to database->new DB connection(either through vwd or management studio etc). I get an Error - which should be written to event log (according to msg) but it isn't - that I can't connect. I am sure that it is because NO SQL SERVER2005EXPRESS instance is running, which I think is the only prerequisite to have it work (I do not need sqlbrowser service I am doint local development).SQL2005express service is configured on MANUAL start. I can start it as administrator through SQL config manager, but it is not convienient and what I want. I need to start it ONLY as a developer user, I do not want it to be running all-the-time for everyuser using computer. It was congigured as Network Service logon, I tried Local Service logon, and I even tried configuring it to logon as -my developer user- account, e.g. with limited user name and his password.In every case I can't start the service as member of users group and this developer. Then I added limited user to SQL2005EXPRESS group. Still NO help, won't start either.My question is, If I want to start developing ADO2.NET application and I need to have running SQL2005Express instance as a develper, how can I start it? I think I do not have some rights to masterDB or something. Do it allways need to be running when computer starts? Isn't there any other way, to start it JUST when I think I start developing?I know of user-mode of accessing SQL2005express DBs, but it also assumes that SQL2005Express service is already running, what I am trying to prevent. I do not want to have it running for everyone who uses computer, just for someone who neeeds it.Any help explaining me the right way HOW TO SETUP WORKING ENVIRONMENT when developing as limited user welcome, I read the the documentations and haven't found the answers.THANK YOU !
View 1 Replies View RelatedI would like to use ISS to extract the user name and email address out of the active directory. I would like to put it in an MS-SQL 2005 table that I can use stored procedures on. I am hoping I can use a query to do it but I understand there is a 1000 entry limit on the extraction of the Active Directory and I have more than 1000. Could someone point me in the right direction please. Thanks.
View 1 Replies View Related
The permissions granted to user 'xxxxyyy' are insufficient for performing this operation. When a user is deleted and then readded to active directory. Reporting services returns the insufficient permissions granted error. In dbo.users in the sql database there are 2 entries for the deleted user one with UserType 0 and another with UserType 1. How do you clean up reporting services or sql to allow the reciently re-added user to re-connect to reporting services. We use the MY-Reports option of reporting services but cannot delete the home directory for this user either in report Mgr. The same error is returned for this action.
I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.
1 Has the current user been granted login access to the trusted SQL Server?
2 Has the current user been granted access to a specific database?
3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?
Thanks,
Sean
Has anyone used this successfully from an OLEDB source component, or even from the Execute SQL Task? I've seen some examples of using a script component, but nothing that uses it through a connection manager.
View 6 Replies View RelatedHi all,
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
Hi all,
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
I invoke xp_cmdshell proc from inside a stored procedure on a 2-node active/passive SQL 2005 SP2 Standard cluster. Depending on which server the xp_cmdshell gets executed on I need to pass different arguments in the shell command. I thought I could use host_name() function to get the runtime process server, however, I am finding that it's not behaving correctly. In one example I know my active node is server2, but the host_name() function is returning server1. The only thing that I could possible explain this is that the MSDTC cluster group is not always on the same active node as the SQL server group and in the case I am talking about the cluster groups are in this mode (differnet nodes). Does the xp_cmdshell get executed by the SQL active node or the MDTC active node? And what is the best way to find out which server is going to run my xp_cmdshell?
Thanks.
Edit:
Perhaps another by product of this is that if I run select host_name() from the Studio Management query window i get different results depending on which server I am running the Studio Management on. On server1 I get server1 and on server 2 I get server 2, all the while server2 is the active node. I need a different function that will always let me determine the correct server that'll be running the xp_cmdshell...
Edit 2: I guess I could determine the running host inside the command shell itself, but I am curious to see if i can do it (cleaner) from SQL.
How does one Determine the number of Active users logged into a SQL Server Database?
I want to use the info to control concurrent licensing for my Application
I wrote a custom IIS log reader windows service that reads and parses log data and periodically uploads it to a SQL server table which I run my reports from later. Currently I read the file by giving the absolute physical path name for the log directory followed by the log file name I anticipate depending on date/time stamps.
My question is: Is there a programmatic way to get the directory path which is configured in IIS to store the IIS log files to? We use non default location e.g. D:DATALOGSIISW3SVC1 or something similar, which might change from server to server. Also, I am not worried about the individual log file names but the directory. I would prefer a C# code example.
Any help is appreciated!
KC
I want to change the display message when else is 0 to show "non active member", how to edit this query for this result .
---------------------------------------------------------------
Current Query
cast(sum(case when trx_date > dateadd(month, -3, getdate()) then 1 else 0 end) as varchar(10))+ ' Active Member := '+ user_member
---------------------------------------------------------------
I try this query but not work and prompt error.
cast(sum(case when trx_date > dateadd(month, -3, getdate()) then 1 else "non active" end) as varchar(10))+ ' Active Member := '+ user_member
----------------------------------------------------------------
We wanted to know list of users who had logged into the SQL Server from last 10 to 30 days.
View 2 Replies View RelatedHi all,
Now I want get AD value(e.g file path),how can I get this value from AD?
Thanks
Hie,
Someone can tell me haw can i do in order to migrate my server sql to active directory.
What is the step
Hello,
I have recently upgraded my the server that runs SQL Server to an Active Directory Domain Controler. Now I can't connect to the SQL Server from ASP.NET Applications when the application is not located on the local machine. The error message I get is SQL Server does not exist or access is denied.
I have no problems connecting with QueryAnalyer and Enterprise Manager from my workstation. I have added the Sql Server to the directory via the "Active Directory"-tab in the Property window for my Sql Server Registration i Enterprise Manager.
If I copy a directory from the wwwroot on my workstation to the server the application has no problem to connect so the connectionstring seams to work fine.
Any ideas?
Regards,
Kalle
hi,
we have recently completed an upgrade to 2000 server and now have AD on our network.
How do i go about querying this from any of my SQL 2000 servers?
I have found a few websites that mention adding a linked server. I have never done this and am not sure how to query a linked server, if that is the way to go.
can anyone offer some advice please?:confused:
TIA
A little background, We have a DEV Server running SQL Server 2000. This is the first of many to be migrated from out NT Domain to our new AD (active directory Domain). All Domain user accounts have already been migrated.
When they migrated this first Server running SQL Server, I am getting the following error when I try to make the owner of a job (any job) run by the SQL Server Agent a domain account in the new AD - when I switch the ownership back to our old NT Domain, it works fine.
I am getting this error:
The job failed. Unable to determine if the owner (domainusername) of job testjob has server access (reason: Could not obtain information about Windows NT group/user 'domainusername'. [SQLSTATE 42000] (Error 8198)).
note that this is happening to all windows authenticated sql server accounts on this Server. All of these account are in the local Admin group on the Server.
Does anyone know what needs to be done in SQL Server to make the AD migration seemless???? I need to try and find this out before we begin migrating Production Servers. Thank you!!
Hi folks,
I'm try'n to find out if i need active directory for sql2000 if my primary network is running on windows2000? What are the pros and cons? Thanks!
Joe R.
Hi!
I want to write a trigger that add a new computer account in my active directory when I do an Insert in my MSSQL table.
I know how to use SELECTstatements using LDAP but I want to do a INSERT statement. Is that even possible?
Can you write vb code directly in SP i mssql 2000?
What I think I have to do is to have a vbscript that does the adding then call the script using exetended SP cmd execute passing the name to the script.
If someone has a another solutions please let me know!
Regards..
Hi there,
Is it possible to, somehow, get a specific users password from active directory? The reason I ask is that I am writing a new system and really don't want the users having to remember yet another password, but rather be able to use there network password? I would like to write the logon section myself and not use any built in functions that anything may have.
Please can someone advise. I don't think it is possible but have been asked to persue the issue.
Thanks
Hi,
I want to migrate my sqlserver to active directory.
Someone can tell me what is the procedure and how can i do ?
Someone have already do this migration ?
Regards
We are implementing Active Directory. I need to know if this will presentany issues/changes for our SQL Server 2000 servers.TIADave Edwards
View 1 Replies View Related