Create A List If User

Jun 1, 2007

Hi there,



Can someone please help me how to generate the list of all "user" in a database and it's access role? really need it . .



Thanks

View 3 Replies


ADVERTISEMENT

DBO Invisible In User List

Mar 20, 2002

Recently found a situation which I can not figure out the cause. Wish somebody can help me here.

The scenario is as follows:

Server: SQL 7.0 with SP2
login name: DBA, standard SQL login, member of sysadmins
DB Name: TEST

DBA is the owner of database TEST and a member of db_owners in the database. Using EM, if you look at Users in TEST database, the only entry you can find is DBA where both Names (user name Login Name) are the same: DBA. There's no entry for user dbo. (from BOL, dbo can not be deleted)

Run sp_helpuser gives out two rows.
user login name
dbo NULL
DBA DBA

I don't know what happened to this machine before. Any clue about the possible cause is highly appreciated.

Hong

View 1 Replies View Related

List User Tables

Apr 14, 2001

I'm brain-dead today, sadly. If it weren't for IE remembering previous entries, I don't know if my name and email would have made it into the header correct :-)

I want the SQL command that lists the names of all user tables.

Alternatively, I have the following problematic Access 2000 code:

Public Sub ListAllTables()
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim cnn As New ADODB.Connection
Dim i As Integer, j As Integer
Dim vgRet As Variant
Dim intPrefixLen As Integer
Dim strAppend As String

DoCmd.Hourglass True
cnn.Open CurrentProject.Connection
cat.ActiveConnection = CurrentProject.Connection
intPrefixLen = Len(CON_pkgPrefix)

Debug.Print cat.Tables.Count

For j = (cat.Tables.Count - 1) To 0 Step -1
Set tbl = cat.Tables(j)
With tbl
Debug.Print tbl.Name
vgRet = SysCmd(acSysCmdSetStatus, tbl.Name)
End With
Next

Set tbl = Nothing
Set cnn = Nothing
Set cat = Nothing
DoCmd.Hourglass False
vgRet = SysCmd(acSysCmdSetStatus, " ")
End Sub

This code runs fine against an MDB but against SQL it includes all the views, rather than just the tables. If you have a fix for this, that will do just fine!

Arthur

View 1 Replies View Related

User Permission List

Apr 28, 2006

Hi, Does anyone has a script to get user permissions on all tables?
Thanks

View 8 Replies View Related

List Of User Sp From Master Db

Jun 15, 2006

Is their any sql to find the list of sp created by user's in master db?

View 9 Replies View Related

List Available Reports For A User

Feb 11, 2008



Hi there,

Don't know if anyone has done this before -and I would really be wondered if not-:
I want to show a list of available reports to a user. Since we are working with SharePoint Portal Server 2003 the webparts can't be used (need MOSS 2007 or WSS 3.0), but I really need to be able to build a dynamically generated list and show it on a sharepoint page.

My first idea is to build a report (kind of menu). I am already this far:

to get a list of reports with the active directory groups and their mapped roles:
select u.UserName, c.Path, r.RoleName

from PolicyUserRole pur

left join Users u on pur.userID=u.userID

left join Roles r on pur.roleID=r.roleID

left join Catalog c on pur.policyID=c.policyID

order by u.UserName, c.Path, r.RoleName


I managed to link active directory via LDAP - linked server.
I even managed to get a list of users and groups. Unfortunately I cannot check whether a user belongs to a group (any idea?), since all of our security (on sql server) is managed by active directory groups this really is a problem.

Or am I completely wrong and is there another way to fetch a list of reports for a user?

Any help would be great!

Thanks,
Tom

View 1 Replies View Related

Using A Loop To Create A List Of Emails

May 29, 2008

I have a stored proc I am running, and I would like to create a list of email addresses from a table and put that list into a variable. I did a basic while loop to work on syntax, but now I don't know how to actually get each address added on. Here's how I started it

declare @start int, @testEmail nvarchar(2000)

set @start = 1
set @testEmail = NULL
while @start <= (Select count(PADM_Email) from PADM_Emails)
Begin
--Print @start
set @testEmail = @testEmail + (Select distinct PADM_Email from PADM_Emails) + ';'
set @start = @start + 1
End

I know that the above is wrong, but I don't know how to get it right. Ideally, I want the @testEmail to look like this:

emailaddress1;emailaddress2;emailaddress3;

View 7 Replies View Related

Database List For The Currently Logged In User

Jul 11, 2004

I can get a list of all databases in SQL Server using

sqlText = "select name from sysdatabases order by name".

How can I get such a list for the currently logged in user only?

Thanks in advance

Harold Hoffman

View 1 Replies View Related

How To List All The Objects That An User Has Access To?

Aug 27, 2004

Is there anyway, that I can generate a list of all the objects that a given user has access to (including type of access whether select or update etc), by running a SQL command? One of our databases has nearly 40,000 tables (no kidding!) I can always find this out by manually looking into the roles etc on the enterprise manager, but I need a way to find out using a T-SQL query..Thanks for any help!

View 2 Replies View Related

List All Databases That A User Has Access To

Nov 17, 2014

Any way to list all databases that a user has access to?

View 6 Replies View Related

User List With First Activated Offer

Nov 27, 2013

I have 3 tables, please find the table structure and sample data below

Table 1 : tblUserInfo

UserID | FirstName | Email | JoinedDate

1 Testuser01 User01@User.com 10-10-2013
2 Testuser10 User10@User.com 11-10-2013
3 Testuser20 User10@User.com 11-10-2013

Table 2 : tblOffers

OfferID | OfferName | ExpiryDate

1 OfferSample1 15-10-2014
2 OfferSample2 15-9-2014
3 OfferSample3 10-07-2014

Table 3 :tblOfferActivated

ActivationID | UserID | OfferID | ActivationCode | ActivatedDate

1 2 3 ABC 11-11-2013
2 2 1 CEG 13-11-2013
3 3 1 JHG 18-11-2013
4 3 2 KIU 20-11-2013

Expected Output

I want to list out the users with the first activated offer details.The OfferName Should be based on the first activated date

UserID | FirstName | Email | JoinedDate | OfferID | OfferName |ActivatedDate | ActivationCode

1 Testuser01 User01@User.com 10-10-2013 Null Null Null Null
2 Testuser10 User10@User.com 11-10-2013 3 OfferSample2 11-11-2013 ABC
3 Testuser20 User10@User.com 11-10-2013 4 offerSample3 18-11-2013 JHG

View 6 Replies View Related

Find The List Of Tables For A User?

Feb 18, 2015

how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.

View 1 Replies View Related

List All Databases That A User Has A Login

Nov 26, 2007

Hi everyone,
I have an instance with many databases in it. I am looking for the easiest way to see which of those databases a user has a login on. What is the most efficient way of doing this?
Thanks,
Anil

View 5 Replies View Related

How Will I Create A Delimited List In A SELECT Query?

Jun 10, 2007

SELECT FriendName from Friends where RegionId = 23
I would like to create a comma delimited list of 'FriendName' column values in above query (example - Mike,John,Lisa,Emburey).
How would I do that?

View 5 Replies View Related

How Can I Create A Script To List All JobId And Job Descriptions

Oct 22, 2007

Hi Guys,
I just need a script to get all Job Id and Job Descriptions from Sql Server Agent/Jobs and then save it into a text file.   I'm not sure how to accomplish this using Sql Server Management Objects(SMO).   Any sample scripts and/or stored procedures, etc. would be appreciated. 
Thanks for your help.
-W2528

View 6 Replies View Related

How Can I Create A List Of Users Permissions Daily

Aug 16, 2004

I need to be able to set up a table listing users permissions, this needs to be run daily and then notify me of any changes without using the auditing or profiler software.


Any ideas?? :eek: :eek: :confused:

View 3 Replies View Related

Multiple Tables To Create A More Complete List

Jun 26, 2014

My SQL knowledge is limited to only trial-and-error and on-the-fly learning, sadly. However, I've had past experience with VB and some earlier, more basic programming, so I don't find it that difficult to grasp; it's just rather overwhelming without any formalized training.

I'm trying now to solve a problem that involves a sales history report. Our sales history table lists only records for product lines which a customer has purchased in the past. However, the budgeting team needs a list of each customer with all product lines, even if they've never purchased, and they'd like to include an industry code value that is from another table.

So the way the tables are looking is this:

Sales_History as the main table with the appropriate data in it
Product_Lines as a reference to fill out the main table with non-purchased lines

Accounts as another reference that gives the industry code per the customer ID in the main table''I'm seemingly on the right track with the following, which tries to create a list of all product lines and the records from sales_history which match them:

SELECT sales_history.sah_sort_id,sales_history.sah_sortkey3,sales_history.sah_sortkey2_desc,
prod_lines.pl_prodline_id,prod_lines.pl_short_desc
FROM dbo.sales_history
INNER JOIN prod_lines ON sales_history.sah_sortkey3=prod_lines.pl_prodline_id

[code]...

However, this produces a list of only 21 rows, when there should be 41.

View 15 Replies View Related

How Do I Create An In-line Comma Seperated List

Jul 20, 2005

Scenario:Table 1 (a id, b name)Table 2 (a FKid, d value)A standard join on a gives me something like:a1 b1 d1a1 b1 d2What I want is:a1 b1 d1,d2I can easily do this with a function or cursor, but is is somewhatslow, and I need to do this a lot and I don't really want to have tomaintain tons of functions or cursors.Thoughts?

View 3 Replies View Related

How Can I Use The GetSchema To Get A List Of Only User Tables And Views.

Apr 26, 2006

How can I use the GetSchema method new in 2.0 to get a list of only user tables and views.

View 6 Replies View Related

Is There A Quick Way To Get A List Of Roles A User Is A Member Of?

Jul 23, 2005

What I'm looking for is a list of roles a particular user is a memberof.the closest I've found so far is sp_helprolemember without anyarguements. but this gives me all the roles and all the users. I wantthis same list filtered on a specific user.something like sp_??? 'user'

View 2 Replies View Related

SQL-DMO And C#: Retrieve The List Of User-Defined Functions

Apr 9, 2006

http://www.csharphelp.com/archives2/archive342.htmlI am using the sample code from this link but I amunable to figure out how to retrieve the list ofthe User-Defined Functions. I am able to get thecount of the user defined functions correctly using:db.ListObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj _UserDefinedFunction,SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name).Cou ntbut I am unable to get to enumerate the function names.Then I tried to see if I can achieve what I want usingSQLObjectList but I was unsuccessful.Does someone know how I can do this using C#?Thank youThis is the full code I have:private void linkLabel5_LinkClicked(object sender,LinkLabelLinkClickedEventArgs e){this.Cursor = Cursors.WaitCursor;SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();srv.Connect(this.cboServers.SelectedItem.ToString( ), this.txtUser.Text,this.txtPassword.Text);for (int i = 0; i < srv.Databases.Count; i++){if (srv.Databases.Item(i + 1, "dbo").Name ==this.cboDatabase.SelectedItem.ToString()){SQLDMO._Database db = srv.Databases.Item(i + 1, "dbo");this.lstObjects.Items.Clear();SQLDMO.SQLObjectList sqludf;sqludf =db.ListObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj _UserDefinedFunction,SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name);for (int j = 0; j < sqludf.Count; j++){//this.lstObjects.Items.Add(db.ListObjects(SQLDMO.SQ LDMO_OBJECT_TYPE.SQLDMOObj_UserDefinedFunction,SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name).Ite m(j + 1, "dbo").Name);}this.Cursor = Cursors.Default;return;}}this.Cursor = Cursors.Default;}

View 4 Replies View Related

Stored Procedure To List Out User Access

Jan 19, 2008

Is there a built in stored procedure that would allow me to list outthe database permissions assigned to a particular user or role?

View 2 Replies View Related

How To Generate Any Report That The User Selects From A List

Oct 10, 2007



I know this a simple question but I cannot find an example of using the ReportExecutionService to render a report that doesn't take any parameters. Can somebody provide and example? Or to make it easier, tell me what to change in the msdn example: http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx


Thanks in advance,

Chris

View 1 Replies View Related

How To List The User Objects In Sql Server 2000

Mar 24, 2008



hi all

i am using sysobjects for taking the list of user objects.

by using userid, status column combinations, able to take out the user tables/triggers/views
but for stored procedures, it doesn't work.

is there any better way to list the user objects.

thanks in advance.
sam alex

View 3 Replies View Related

Query To Create A Countable List Using A Single Value From Table

Mar 15, 2004

Hi,

from a single numeric value, say 'n', in a field in a table, i'd like to create a SELECT query that produces a list from 1 to n, e.g

if the value was 6, I would like selected back:
1
2
3
4
5
6

Any pointers?
Greg

View 5 Replies View Related

Create List Of Table Names And Size For A Database

May 5, 2004

Hi there,

I am trying to create a list of all the tables in one database and then list the size of each table. So for example I want to create a table with the table name and table size for one DB

E.g

Table1 1111KB
Table2 123300MB
Table3 120448KB

etc for all the tables in a particukar DB

I know there is a stored procedure to list the sizes: 'sp_spaceused' but not sure how to script all this together.

can anyone help please!!

From

NewToSQL

View 12 Replies View Related

T-SQL (SS2K8) :: Combining Keys To Create A Master List?

Mar 5, 2014

I am struggling trying to clean some data and identify duplicate records. I used fuzzy grouping in SSIS and provided back a series of groups. The issue is some of the individual records can appear in multiple groups (so in reality the groups should be combined). This is best explained with an example:

Original Data
key1 key2
647942600014
647942285437
2324662490640
2324662285437
2324662066128
2222 2285437
2222 1111111
9999 1111111
9999 2222222

Should look like:

22222600014
22222285437
22222490640
22222066128
22221111111
22222222222

I only choose 2222 as the surviving key because it was the smallest number. I really do not care which number remains as long as it is the same across.

I tried playing with self joins between the tables but have had no success.

I am using Sql Server 2008 and the number of records could 500K to 1MM.

View 2 Replies View Related

!!help!! How To Save User Inputs From Checkbox List To Database??

Dec 11, 2007

Hi,
 
I'm new to ASP.NET 2.0. I'll like to ask how do one save user input from txtbox, radiobttnlist or checkboxlist into database?
Im implementing a suvrey form here by the way.
 -any reference webs,
-any pointers from experts?
Thank you for your time in reading this email
 
Kayln

View 7 Replies View Related

List Of Stored Procedures With Permission For Executing For User

Jul 26, 2007

I have user XY in SQL 05. I would like to find all stored procedures, where user XY has permission for executing. Is there any way to find it than look in every stored procedure?

Thanks for tips

View 4 Replies View Related

Function To Create Comma Separated List From Any Given Column/table.

Jul 20, 2005

Hi,I'm sure this is a common problem.. to create a single field from awhole column, where each row would be separated by a comma.I can do this for a specified table, and column.. and I've created afunction using VBA to achieve a more dynamic (and very slow) solution..so I would like to implement it using a user defined function in sql server.The problems I'm facing are, that I can't use dynamic sql in afunction.. and I also can't use temporary tables which could build up a'standard' table from parameters given to then perform the function on.So, with these limitations, what other options do I have?Cheers,Chris

View 1 Replies View Related

Transact SQL :: Create Comma Separated List For Each Account And Date?

Jun 19, 2015

i have the following:

DECLARE @Table TABLE (
 OrgRoleNumTxt VARCHAR(10)
, AccountNm varchar(100)
, EffectDate DATETIME
, OperationNm varchar(100)
, Premium decimal(18,2)
)
Insert into @Table(OrgRoleNumTxt, AccountNm, EffectDate, OperationNm, Premium) VALUES
('00236', 'R.R. Donnelley', '2010-01-01', 'Chicago', 1000),
('00236', 'R.R. Donnelley', '2010-01-01', 'Boston', 3000)
select *
from @Table

but want this

Is it possible using basic T-SQL?

View 8 Replies View Related

Power Pivot :: Date Dimension - Create A List Of Months

Jul 21, 2015

I need to create a date dimension where the lowest level is month. I've seen examples which use the list function such as

Source = List.Dates(#date(2000, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2000,1,1)), #duration(1,0,0,0)),

The above increments by 1 day which is defined in the 1st argument of the #duration. My question is how can I dynamically change the value of this 1st argument such that its the number of days in the current month hence it will increment to only return the 1st date in the Month e.g

1/1/2000
1/2/2000
1/3/2000
etc..

I prefer to use an elegant approach if possible, the alternative would be return all dates, create a custom column from these dates which returns the month date - delete the dates column - get a distinct list of the month dates.

View 2 Replies View Related

How To Find Out The List Of All Tables And Views Owned By A Particular User In Any Database?

Oct 15, 2001

How to find out the list of all tables and views owned by a particular user in any database by querying the system tables?Thanks.

View 2 Replies View Related







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