I Want To Query All Users Are Member Of SYSADMIN, How To Do?
Oct 14, 2000How to determine to a login is a member of SERVER ROLE or DB ROLE?
I want to query all users are member of SYSADMIN, how to do?
ADVERTISEMENT
Can Not Sysadmin Member See And Run Jobs From EM?
Jan 25, 2002Hello everybody .
I have a group - SQL support.I want to give them rights to run any job from EM but I don't want them be a part of sa group
What rights should I give them ?
All existing jobs owned by members of sysadmin group.
Thank you
Transact SQL :: Server Trigger To Monitor Member Of Sysadmin Activities
Jul 22, 2015I have to make server trigger to monitor the actitites if sysadmin members. i need to get the login name,hostname and query which they are running.
View 3 Replies View RelatedSYSAdmin Users Do Not Have Permission To See Records In A View?
Sep 15, 2006We have a third party application and wish to create a report based upon a view.
The strange thing is logged in to the server as a SQL SYSAdmin account, we cannot view the data via the view. SQL Admin accounts are setup correctly and there is nothing different on this particular server. No errors are returned just a blank view with no records.
Could this be a permissions problem or orphaned schemas in that particular database? I thought SYSAdmin could view and do just about anything and the people who use this particular database would not have the know how on denying permission to the SYSAdmin role.
Thanks
N
Users Are Unable To Connect To SQL Server 2005 Unless They Are In The Sysadmin Group
Nov 21, 2006We are running SQL Server 2005 in a Windows 2003 domain and I have a situation where some of my users are unable to connect to the SQL Server unless they are a member of the sysadmin group. Any attempts by these users to login result in a login failed,
Error: 18456, Severity: 14, State: 11
Which indicates that it is a valid user who does not have access to this SQL Server.
I have been able to narrow the failures down to the following situation:
Create a user, TestUser1, as a member of 1 domain local group TestGroup1
Give TestGroup1 access to SQL (standard public access to master)
All good. Login succeeds.
Add TestUser1 to another domain local group TestGroup2
Attempt to login to SQL Server -> login failed.
Add the user explicitly -> login failed
Add one of the groups to sysadmin -> login succeeds
It seems that as long as the user is a member of more than one AD group, and none of those groups is a member of the sysadmin server role then the user is unable to login. Obviously having all of the users as sysadmin isn't a workable solution, has anyone seen this issue before?
I have been able to replicate a similar situation in our test domain, but in that case the issue is resolved by adding the users explicitly to SQL Server (still not an ideal solution).
Interestingly, if I run the same test in our test domain but use global groups, it works. But unfortunately the network admin tells me the groups must stay as local.
Any help would be greatly appreciated.
Regards,
Daniel Watkins
Setup And Upgrade :: Users With Sysadmin Role Can't Access Network Drives
Oct 12, 2015When I create logins for SQL Server 2012, something strange happens. When I assign the sysadmin role to a login, the login loses the access to the network drives (for example when creating or attaching databases). The only possible place is the c:drive of the computer that is running SQL Server.
View 11 Replies View RelatedHide Fields Based On Users AD Member Ship
May 19, 2007If I wanted to make a report that shows different fields, one being cost, but I only wanted certain people to see cost based on their AD groups, is there a way to hide this information?
Basically, domain/user1 belongs to AD purchasing group purchasing, he can see item, description and cost, where domain/user2 belongs to sales so he can only see item and description?
A New Member Could Not Be Added To A Local Group Because The Member Has The Wrong Account Type
Mar 23, 2007Hi 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
A New Member Could Not Be Added To A Local Group Because The Member Has The Wrong Account Type
Mar 23, 2007Hi 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
Master Data Services :: How To Add A Reference To Another Entity Member When Creating A Member
Oct 23, 2014I need to create a member that one of its Attributes (maybe my term is wrong) is a reference to another entity member named group
the code
createRequest.Members.MemberType = MemberType.Leaf;
createRequest.Members.Members = new System.Collections.ObjectModel.Collection<Member> { };
Member aNewMember = new Member();
aNewMember.MemberId = new MemberIdentifier() { Name = uag.groupName, MemberType = MemberType.Leaf };
[code]....
"The attribute data type is not valid".Is it wrong to add the reference as attribute? How can I embed the reference in the new member?
SQL 2012 :: Delete Rows If Member Does Not Exists In The Member Table
Sep 24, 2015I have a table where I need to delete rows if the member does not exists in the member table. Will the following syntax work ?
Delete T
FROM Event_Temp_Lead_Screen T
left join member M on ( M.MemberID = T.MemberID )
where
T.Gender is NULL
Table Query Fails With Object Not Found Error After Assining Sysadmin Server Role To Login
May 14, 2015I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname...Also I am seeing table not found error while quering tables under dw schema, after the change.
View 5 Replies View RelatedAnalysis :: All Member Not Working In MDX Query
May 28, 2015[DIMCustomerBuyer].[CustId].[CustId] is working.
[DIMCustomerBuyer].[CustId].
All members is not working any hint why this is not working.
Transact SQL :: Overlapping Dates For Same Member Query
Oct 18, 2015IF OBJECT_ID('tempdb..#OverLappingDates') IS NOT NULL
DROP TABLE #OverLappingDates
CREATE TABLE #OverLappingDates
(
MemberID Varchar (50),
ClaimID Varchar(50),
StartDate DateTime,
EndDate DateTime
)
[Code] ....
I need to select only records where dates are over lapping for the same memberid...For this scenario i need an output First 2 records (MemberID 1 has 2 claiims overlapping dates).
Simple MDX Query: Multiple Non-Contiguous Members From Same Dimension In Calculated Member
May 28, 2008Hi
A very basic MDX question. Using the following from AdventureWorks as an example:
Code Snippet
WITH MEMBER [Measures].[Big Cities]
AS
(
[Geography].[City].&[New York]&[NY]
, [Measures].[Internet Order Count]
)
SELECT
[Measures].[Big Cities] ON COLUMNS
, [Customer].[Total Children].Children ON ROWS
FROM [Adventure Works]
How would I re-write the [Measures].[Big Cities] calculated member so it included both [Geography].[City].&[New York]&[NY] and [Geography].[City].&[Los Angeles]&[CA]? Assuming there is no logical connection between these members, and that they are non-contiguous. Also, in my real example, I cannot move [Measures].[Internet Order Count] to the WHERE clause, it must remain within the calculated member.
Thanks,
Julia.
Master Data Services :: Link To A Particular Member Via URL Query String Parameters In Explorer?
Jun 12, 2015I want to setup a link from an SSRS report to Master Data Manager so when the click on the SSRS report item, it will pull up Master Data Manager filtered to the particular member that the user clicked on from the report. I cannot find any references to that being possible. Is it possible? What is the query key value to pass a member codeid or other filter criteria to Master Data Manager explorer?
MID is Model ID
VID is Version ID
EID is Entity ID
Are there other possible parameters? Can a pass a member ID or code?
T-SQL (SS2K8) :: Query To Show Only Top Or Main Team For Team Member
Nov 2, 2014Team members appear twice or more if they belong to more than one team. I need to be able to show their name and main team. Team is not important at the moment but I just like to include all team members and display a team name against them.This is what I have at the moment:
SELECT SystemUser.systemuserid, FullName, TeamMembership.TeamID, TeamName
FROM Team
RIGHT OUTER JOIN TeamMembership ON Team.teamid = TeamMembership.teamid
LEFT OUTER JOIN SystemUser ON TeamMembership.systemuserid = SystemUser.systemuserid
order by FullName
How Do I Do A Query That Uses All Users?
Jul 5, 2000Hello,
I am doing a website that incorporates SQL and one of the things is which user to query for. In this part, I would like to put
the variable 'AllUsers' which consists of all the users, do you have any suggestions on how this can be done, here is what I
have without a query for 'AllUsers' in HTML:
<SELECT NAME="targetUserID">
<OPTION VALUE="AllUsers">All Users</OPTION>
<!-- =queryString "select distinct rtrim(userID) from
Track..cUsers order by userID" -->
<!-- ?{sqlBlock $queryString crux 2000 0 -1 errorString tmpuserID -->
<OPTION VALUE= "<!-- $ $tmpuserID -->"
<!-- ({ $tmpuserID == $targetUserID --> SELECTED<!-- (} -->>
<!-- $ $tmpuserID --></OPTION>
<!-- ?}sqlBlock -->
</SELECT>
Here is what I thought would work, but it didn't:
<SELECT NAME="targetUserID">
<!-- =queryString "declare @AllUsers varchar(16) select @AllUsers = userID from Track..cUsers" -->
<!-- ?{sqlBlock $queryString crux 2000 0 -1 errorString AllUsers -->
<OPTION VALUE="AllUsers">All Users</OPTION>
<!-- ?}sqlBlock -->
<!-- =queryString "select distinct rtrim(userID) from
Track..cUsers order by userID" -->
<!-- ?{sqlBlock $queryString crux 2000 0 -1 errorString tmpuserID -->
<OPTION VALUE= "<!-- $ $tmpuserID -->"
<!-- ({ $tmpuserID == $targetUserID --> SELECTED<!-- (} -->>
<!-- $ $tmpuserID --></OPTION>
<!-- ?}sqlBlock -->
</SELECT>
In case you dont like how that looks here is what I thought would work just in SQL:
declare @AllUsers varchar(16)
select @AllUsers = userID
from Track..cUsers
I also tried to do it with my query for the final information, but that didnt work either it went something like this:
select DATEDIFF( hour, startTime, stopTime), cSlice.projectID " +
from Track..cSlice, Track..cProjects
where '" + $targetUserID + "' = 'AllUsers'
and clientID = '" + $targetClientID + "'
and state = 'Stopped'
and typeID = 'Billable'
and cSlice.projectID = cProjects.projectID
and startTime like '" + $targetMonth + "%'
or userID = '" + $targetUserID + "'
and clientID = '" + $targetClientID + "'
and state = 'Stopped'
and typeID = 'Billable'
and cSlice.projectID = cProjects.projectID
and startTime like '" + $targetMonth + "%'
order by cSlice.projectID
Any help would be greatly appreciated, Thank you for your time.
Nathan Czuba
Query All My Users Activites Only
Apr 15, 2008This Query is returing all the Users information and more too much.
I need to only return the information that is ispecfici within that date for that particular user not tons (1,000 of records) of data. It should only be maye hundreds. HELP ME WITH QUERY PLEASE....
Query
Code:
SELECT DISTINCT
dbo.UserInfo.tp_SiteID AS SiteID, dbo.UserInfo.tp_GUID AS GUID, dbo.UserInfo.tp_ID AS ID, dbo.UserInfo.tp_Deleted AS FilesDeleted,
dbo.UserInfo.tp_Login, dbo.UserInfo.tp_Title AS Name, dbo.UserInfo.tp_Notes AS Extra, dbo.UserData.tp_Editor,
dbo.UserData.tp_Modified AS DateModified, dbo.Docs.DirName, dbo.Docs.LeafName, dbo.Docs.ListId, dbo.Docs.Type, dbo.Docs.TimeLastModified,
dbo.Docs.Extension
FROM dbo.Docs LEFT OUTER JOIN
dbo.UserData ON dbo.Docs.SiteId = dbo.UserData.tp_SiteId LEFT OUTER JOIN
dbo.UserInfo ON dbo.UserData.tp_SiteId = dbo.UserInfo.tp_SiteID
WHERE (dbo.UserInfo.tp_Login = 'HISNTsfehn') AND (dbo.UserInfo.tp_GUID = dbo.UserInfo.tp_GUID) AND (dbo.UserData.tp_Modified BETWEEN
'2008-01-01' AND '2008-04-04')
Different Query Results For Different Users?
Apr 22, 2008I have an SQL2K5 database where a group of users share a custom database role and access the database via trusted connections (Windows authentication).
I have a stored procedure which reports all of the users in this database role using an SQL statment something like this:
select name
from sysusers
inner
join sysmembers on
sysmembers.memberuid=sysusers.uid
and sysmembers.groupuid=7
As the programmer, and the dbo, when I log in to execute this query, I get the list of a dozen, or so, users.
When one of the users executes the same query, they only see a return of their own username.
I'd like to have the users be able to execute this query and get the full list of users. Can someone tell me how to accomplish this?
I assume that this is some security, or permissions, thing involving system tables, or Windows authentication security.
Thank you.
BTW--the users never actually directly execute the stored procedure I mentioned; They are using a VB client application that affects the execution.
How To Query Active Users - Where Is This Info Stored ???
Jul 25, 2001I know how to look at active users from Enterprise Manager, but how can I query out the information - what table is that info stored in ??? I don't want a list of all the logins, I just want the list of active users....
Help... thanks in advance,
Nancy
How To Write A Query To View OS And Db Authenticated Users ...
Aug 13, 2003Hi,
we have a sqlserver 2000 db with a mixture of OS and NT authentication.How to write a query (or get info) on users.
The query should be able to tell me that these users are OS authentiacted and these users are db authenticated.
Thanks,
-copernicus
Query Users In A Security Group With LDAP
Aug 11, 2004I 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.
Query To Show Only Users Who Logged On In Past 6 M
Feb 20, 2006Hi All
I have built this query :
Select position, SubsidiaryName
from position
Where SubsidiaryName in ('country1','country2')
and position not like('testuser_%')
and position not like ('Inactive_%')
and position not like('olduser_%');
However when I run it, the result more accounts in country 1 than expected. I think it could be inactive users who have not logged into this system for 6 months or more. How can I build a query that incorporates my original query + shows only users who logged into the past 6 months ?
Own Query Language For Non-technical Users, Any Frameworks Known?
Jul 23, 2005HelloWe have the challenge to introduce for our database applications adynmaic search for the end users. The end users are non-technicalusers.I am looking therefore for possibilities which would allow to build ownquery language with expressions according to the need and skills forthe users.I found one product so far. "EasyAsk - Decision Adviser". does somebodyknow also other tools or frameworks which can be integrated?http://www.easyask.com/product*s/decision-advisor.cfmThe environment is currently MS SQL Servers and C# Webservices.Myvision is to have a framework or library which can be seamlessintegrated into our existing solutions which is currently a smart C#clients. i.e. by using web-service or an C# API.thanks Mark Egloff
View 1 Replies View RelatedQuery To Obtain Users And Privileges From Databases
Apr 24, 2007Hi to all, is my first post, i need a query or script to obtain all users and privileges from all my databases, someone to help me. I'm learning Administration SQL server 2005.
I know that sys.database_principals and sys.server_principals have information about that, but i need users - privileges of every database.
thank you
Results From Query Are TRUE And FALSE But Users Wanting YES And NO
Jan 7, 2004Hi everyone.
I've got a Select query that pulls out some data from my database. Two of the columns are both booleans (bit's of size 1) so they come back as TRUE and FALSE - which I thought was fine.
However, the users are wanting to see YES and NO since they find TRUE and FALSE confusing (yes I know how silly that sounds).
Is there any way I can do this?
My query is like this:
SELECT [stuff], [things] FROM [table1], [table2] WHERE [table1].[condition] = [table2].[condition]
Thanks
Andrew
Reporting Services :: Adding (new) Child Domain Users To SSRS As System Users?
Jul 28, 2015We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
Why Out Of The Blue Would VPN Users Be Unable To Connect To Database And Local Users Are Unaffected?
Mar 6, 2008I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies View RelatedSQL Security :: Did Not Create Any New Users And There Are No Other Users Listed In Accounts Section
Sep 28, 2015I 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 RelatedStored Procedure To Get All Users Or All Users Of A Specific Country
Apr 30, 2008hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country}
who has an idea how to solve this problem?
Sysadmin Role
Jan 30, 2006Hi All,
Is it possible to give a user a sysadmin role and then deny some of the privileges?
I am a junior dba, I should be able to view only everything that the sysadmin can see, i.e. db properties, logins, packages, jobs etc.
SysAdmin Role
Feb 10, 2004Hi
I'm new to SQL Server. I have created a databased named Sample and
I hae created the user with login named "Sman".
SMan owns some tables and sp's. I'm able to access the tables and SP's when I was logged in as Sman in Query analyser. I have given a Sysadmin privilege to Sman then I'm not able to access the tables and sp's when i try to login with Sman.
ie, Select * From tabl1 is not working But
Select * From Sman.tabl1 is working. I dont know Why is it so?
Can any one help me!
Thanks in Advance