Hi 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
I am very new to the SQL database. I have the following query. I would appreciate if someone could clarify this for me:
I have created two users (user1 & user2) under the same login name test1 in SQL Server 2005 Database. Further I used the login name (test1) & password (******) of SQL Server in connection string to connect to database.
Now I want to know that how & where can I refer the user name (user1 or user2) to use its previliges.
How will I know that which user's privileges level is used in the connection.?
Is there any script to know users and their database roles privileges and server roles of particular database?
View 1 Replies View RelatedMy VB.net application manipulates data in a local SQL Express database. When the app is installed, the database does not exist, but it cannot be created at run-time by anyone other than a user with administrator privileges. In addition, the application shares the data stored in SQL with a critical 3rd party component that can only reach the database via named DSN (also not existing prior to installation).
I see my primary SQL security options as being:
(1) Use SQL or Mixed Mode authentication with an admin-level username/password combination, or
(2) Create a db user/group with admin-level privileges and grant membership to all NT authenticated users
Secondary problem: Creating the DSN.
Does it make sense to create a Custom Action (.dll) that is called at the end of the installation process in order to create the database, the user security context and the named DSN?
Am I overlooking some built-in functionality provided by Visual Studio 2005 that will accompish some or all of this for me? I am aware that customizing the 'silent' installation of SQL Express to use a different authentication mode requires manifest tweaking -- I just don't know anything about setting up the appropriate security for this situation. Would you put the db and role creation stuff in a SQL script and execute it post-install?
Thanks in advance for any insight you can provide.
-T
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
All of a sudden I have 4 of my databases, including pubs that are all marked suspect and seem to be un-recoverable. I have followed the Resetting the suspect status directions as well as trying to attach only the .mdf files but still run into problems reading the .ldf file. I have not tried to do the procedure of creating another database with the same name and structure then swapping the files to trick SQl but will do so if I need to. Does anyone know why this happens all of a sudden to multile databases, of which Pubs I have never used for anything??
In my log files during startup I get this error
Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
Then this error for every database that is suspect, (the actual LSN numbers are different for each one).
The LSN (4:517:1) passed to log scan in database 'pubs' is invalid..
Is there anything else I can do to prevent this from happening or recover the DB's
Thanks
i want to obtain two columns in a query but i don´t know how to.ex.
col1 col2
1 A
1 B
2 A
1 C
I need something like this.
col1 col2
1 A, B, C
2 A
Col1 = Table Z
Col2 = Table Y
I'm trying to devise a query for use on SQL Server 2000 that will dowhat was previously done with one query in MS Access. The MS Accessquery was like this:SELECT Count(*) as [Opened],Abs(Sum([Status] Like 'Cancel*')) As [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened)Order By Year(DateOpened), Month(DateOpened)Here were I'm at with SQL Server, TSQLSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Opened]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yieldsMonthOpened======================Aug 2004503Sep 2004752Oct 2004828Nov 2004658Dec 2004533Jan 2005736Feb 2005707Mar 2005797Apr 2005412AndSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus like 'Cancelled%'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yields;MonthCancelled=========================Aug 200478Sep 2004105Oct 2004121Nov 2004106Dec 200475Jan 200582Feb 200571Mar 200594Apr 200533What is desired isMonthOpenedCancelled============================Aug 200450378Sep 2004752105Oct 2004828121Nov 2004658106Dec 200453375Jan 200573682Feb 200570771Mar 200579794Apr 200541233Any assistance would be appreciated.Cheers;Bill
View 3 Replies View RelatedHi,Is there a way to findout the query plan of the executing process usingthe SPID/KPID information.Thanks in advance,Thyagu.D
View 1 Replies View RelatedHi guys,
I am looking for system procedure or system variable that will give the execution time of a SQL query.
I need to capture the execution time of a query in a variable and depending on the value do furthur processing.
Any help in this regard will be appreciated.
im running a dynamic query with open rowset in it
pseudocode:
@CMD=declare @ RETURN SELECT @RETURN =SUM(X) FROM OPENROWSET(....) SELECT @RETURN
EXEC @CMD
This pseudocode dipplay the result of @return
the problem:
capture @return into @myvalue outside the dynamic sql scope
something like
Select @myvalue=exec(@cmd)
I don't wanna run on ditributed transaction like this
insert mytable
exec(@cmd)
thanks,
joey
hi,
In sql2k you used to be able to have a same user in 2 databases under the same login. Just moved to 2005, using sql authentication, and have some users who need to access 2 databases using the same login credentials. But sql2005 will not let me create 2 users under the same login across databases. Any ideas?
Thanks,
Mat
While web hosting I use Sql Server 2000 as the database. Imagine I have hosted 3 Web Sites. All these 3 users want to modify/update their designs. What I did is I created respective 3 users having access to the respective databases only. So that they can registerd the ip and add to the Sql Server 2000 Enterprise Manager. These users are able to access their own databases only. But all these 3 users are able to access the default databases like master,pubs etc. How to restrict this.
Also suggest which is the optimal way to give control to the respective users while using Sql Server 2000.
====Suresh, P.R, Postal Training Centre, Mysore.
I am going to give user rights for an external user to connect to my SQL Server via Client Network Utility.
Atlough I have given user permissions to only access one database and not the whole list, how do I make sure that they cannot see all the other databases on my SQL Server?
I have 20 instances of databases on my SQL Server and ideally I would like to give 20 different people access - but each of them when they enter my SQL Server, should not even know that the other databases exist.
Thanks.
I'd like to find out who is accessing various databases and log the time they did so.
Is there a way to do this? I just need the account name and the time logged and then to write to a table so I can query it.
I have a third party application that creates several databases on my sql server. This party uses the same user for accessing all this databases. However after moving to a new server this databases the user is no longer able to query them whith the exception of the "sa" user. Could anyone explain me how to add permissions to this databases manually so the user can query them again.
Thanks in advance
brgds
Miguel
I need a script to kill existing sessions on [db1] and [db2] except sessions from [user1]. I have a script like this now:
USE master
GO
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('db1)
EXEC(@kill);
I just need to extend to run on db2 and don't kill sessions from user1
Does securityadmin Server level role can add, alter logins and corresponding users on all databases ?Â
If not what is the best role other thn SA to have to manager logins and users.
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 RelatedBased on our database infrastructure, we need to secure our SQL databases. The security issue concerns on allowing a limited number of Domain Admin users to access the SQL databases.
We tried certain ways, based on the documents in the Microsoft web site, but we couldn€™t reach to the point of preventing the Domain Admin users accessing the SQL databases.
Thanks in advance.
I have a requirement to delete all the orphans users for the databases. The issue I am having is with when database principal owns a schema in the DB, User cannt be dropped.
How do I transfer it to DBO in case I am looping multiple databases. This is what I got so far .
declare @is_read_only nvarchar (200)
Select @is_read_only = is_read_only from master.sys.databases where name='test' /* This should be a parameter value */
IF @IS_READ_ONLY= 0
BEGIN
Declare @SQL as varchar (200)
[Code] .....
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
group by substring(m.PTNumber, 3,3)
order by AUPct Desc
Thanks. Dan
Hello,
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
This 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')
I 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 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?
I 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
Hi,
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
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.
Hi 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 ?
HelloWe 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 RelatedHi,
in mixed mode,
is there a way to prevent access from user SA to a specific database?
thanks
I have just noticed something very discomforting.
I was told that a user with DBO privileges is able to alter their own database. A conversation of course began to where I was in disagreement with him. The ultimate test of course would be setup the scenario. To my surpise he was right!
I checked the BOL documentation and my concerns were verified.
I have checked permissions on the user I created as well as on a user that previously exists on the MSSQL Server. Only DBO permissions were given to the tested users.
I thought maybe this had something to do with the autogrow setting which is a setting we would enable on a dedicated MSSQL Server but not on a shared MSSQL Server. I toggled this option and the DBO was still able to make size changes to their database.
This is very upsetting as we charge for additional reserved database space. Aside from that, we wouldn't want to have a user with unlimited resources to the server. I could easily fill up a hard drive if I were to update the autogrow setting of the database as DBO and run an infinite loop that would insert data into tables.
I then tested the ability for a user to restore a backup and to my surprise it worked without error for the DBO only privileged user. The DBO user was also able to restore previously dated databases assuming that they knew the file name which would not be hard to guess since it is appended with a date stamp (My_Database_20042905.BAK).
Why is this? Is there a way to correct this and prevent the DBO user to only have access to their database but not the above mentioned type privileges?