Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs

Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want t
o
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs

Getting a list of the queried parameters for a report (via soap)

What I'd liek to be able to do is display dropdowns (like the dropdowns in
the standard reportviewer component with queried parameters (eg, values come
from a datasource).
For example Employee Sales Summary in SampleReports includes a field for
employee name/id... which is automatically populated.
When i run GetReportParameters, I end up with nothing other dthan a type.
Thanks
Weston WeemsWeston,
Your code to call GetReportParameters should look similar to this:
bool forRendering = true;
string historyID = null;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(report, historyID, forRendering, values,
credentials);
Then you just have to loop through the ValidValues of parameters.
foreach (ReportParameter parameter in parameters)
{
/// insert code to evaluate which parameter type/name, if dropdown
foreach (ValidValue vv in parameter.ValidValues)
{
LI = new System.Web.UI.WebControls.ListItem(vv.Label, vv.Value);
///see if this value is the same with the default value
///in which case we make the current list item selected
if (vv.Value == parameter.DefaultValues[0] && parameter.State ==ParameterStateEnum.HasValidValue)
{
LI.Selected = true;
}
this.ProcessorID.Items.Add(LI);
}
This code works for the one parameter that I have setup as a query based
parameter in the report.
Hope that helps,
Steve
"Weston Weems" wrote:
> What I'd liek to be able to do is display dropdowns (like the dropdowns in
> the standard reportviewer component with queried parameters (eg, values come
> from a datasource).
> For example Employee Sales Summary in SampleReports includes a field for
> employee name/id... which is automatically populated.
> When i run GetReportParameters, I end up with nothing other dthan a type.
> Thanks
> Weston Weems
>
>sql

Getting a final version of a person into a DW

I have about 8 databases to integrate. All of the databases have ssno, address city...ect. I need to create a DW table with one unique record for each actual person. In other words,

Joe Smith,123 Main St, Anytown, State,....+ssno

goes into the DW table and is the same person as Joseph S. Smith,123 Main Street... and any other versions.

Could someone point me to a reference or give me an outline of how to do this in and SSIS package?

Is fuzzy logic used here?

Do I need to deduplicate the feeder systems first?

It needs to handle a situation in, for example, the Bronx New York where there could be an apartment buiding with 7 people named Jose Sanchez .

I hope I've been clear, I'm a newbie at this DW stuff, but it's fascinating. Any help would be appreciated. Thanks

Fuzzy could be of help here. The fuzzy grouping can be used for deduplicating, the fuzzy lookup to check whether you already have a record that resembles your new one.

You will need to spend some time (by testing) to figure out a similarity value that suits your situation. In a DW environment, I think this should be a business decision.

From what I understand, ssno probably needs to be involved. In the lookup (haven't used the grouping yet) you can set similarity values for ssno addressno and name. For example, ssno (or birthdate?) needs a similarity of 1, and the name needs to have a similarity > 0.6

Regards,

Pipo

Getting a file name from Query Analyzer

Hi all,

im trying to write a stored procedure that will basically browse a folder and get me the first file that it sees. Is there any way that I can do this in TSQL or using CLR in C#? I was thinking something along the lines of using the dos dir command and triyng to pipe it into a variable, not sure how to go about doing this. Any suggestions?

dir /b ...gives me the bare file names, but it lists all the files in the folder, any way that i can just get the first file ( i dont really care what file).

create table #filelist
(
files varchar(500)
)

truncate table #filelist

insert #filelist
EXEC xp_cmdshell 'dir c:*.* /b'

select top 1 * from #filelist

|||

This might help out:

http://stevekass.com/blog/wp-content/Folders/sql/TextDriver.htm

You can use TOP 1 to get just one file name.

Steve Kass

Drew University

http://www.stevekass.com

YoungEngineer@.discussions.microsoft.com wrote:

> Hi all,

>

> im trying to write a stored procedure that will basically browse a

> folder and get me the first file that it sees. Is there any way that I

> can do this in TSQL or using CLR in C#? I was thinking something along

> the lines of using the dos dir command and triyng to pipe it into a

> variable, not sure how to go about doing this. Any suggestions?

>

> dir /b ...gives me the bare file names, but it lists all the files in

> the folder, any way that i can just get the first file ( i dont really

> care what file).

>

>

>

>

Getting a field from subtotal

hi all,

i have a problem in my report; some fields grouped by matrix and include a subtotal. In column group there is 2 field and one of them contain integer values. I want to sum all of the this fields and minus from a rowgroup field. Is there a way for to sum fields in coloumn group or getting total field from subtotal?

I'm having trouble understanding your problem and your English. Could you try to rephrase your question and add a little more detail?

Thanks

Getting a error in DTS Active x script

Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End FunctionLook at http://dbforums.com/t673820.html

Hugh Scott

Originally posted by Bigced_21
Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End Function