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 & rdgs
Hi,
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 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 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

get the description of a column

Okay guys heres the senario.

I have written a kick butt asp application that allows me to test sql
statements and manage/display all my databases from the web but I have
a feature I want to include that I can't figure out how. In Enterprise
Manager, one of the column editable properties is the Column
Description. I can't find it in sql server itself. only in the
Enterprise Manager. I need to access it using a sql statement so that
it will display in my table definiation view that I create in the asp
app.These descriptions are kept as extended properties in sysproperties.
Look up sp_addextendedproperty in the help file for more information.
To figure out what Enterprise Manager is doing in situations like
these, you can use Profiler to see what SQL code it is sending to the
server.

-Tom.

get Stored Procedures Parameters, how?

hi
i make smal Application to get information from SQL Server 2000 by using
VB6.
now i can get Databases, Tables and Colomn, and Stored Procedures, but my
problem how i can get SP Parametres?
i am thinking to make small function to get Parametres From SP.Text, but i
think its not good solution.
Tarek M. SialaMaybe you could cross-post to a few more groups, or try this search engine
called google before casting such a wide net. Anyway, here is one page that
might help. Followups set accordingly.
http://www.aspfaq.com/2463
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message
news:edmJJ3scGHA.3908@.TK2MSFTNGP04.phx.gbl...
> hi
> i make smal Application to get information from SQL Server 2000 by using
> VB6.
> now i can get Databases, Tables and Colomn, and Stored Procedures, but my
> problem how i can get SP Parametres?
> i am thinking to make small function to get Parametres From SP.Text, but i
> think its not good solution.
> --
> Tarek M. Siala
>

Get space used of all databases

Hello,
I need to get space information of all databases and report it. I create one
cursor that enter in every databases and report space used, but it returns
the following error.
"Server: Msg 8114, Level 16, State 5, Line 16
Error converting data type nvarchar to numeric."
If i treat my variables like normal text ('') and use the output generated
it works fine.
I send you part of the script and hope that you can help me
declare @.dbsize dec(15,0),
@.dbname sysname,
@.sql nvarchar(850)
declare dbid_cur cursor for
select [name]
from master..sysdatabases
where [name] <> 'tempdb' for read only
open dbid_cur
fetch next from dbid_cur into @.dbname
while @.@.fetch_status = 0
begin
set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
+ 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
varchar(20))'
print @.sql
-- sp_executesql @.sql
fetch next from dbid_cur into @.dbname
end
close dbid_cur
deallocate dbid_cur
Thanks and best regardsHave you looked at sp_helpdb? Does it give you what you want?
Keith
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards|||How about
EXEC sp_msforeachdb 'USE ?; EXEC sp_spaceused'
(Note that the procedure is unsupported.)
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards

Get space used of all databases

Hello,
I need to get space information of all databases and report it. I create one
cursor that enter in every databases and report space used, but it returns
the following error.
"Server: Msg 8114, Level 16, State 5, Line 16
Error converting data type nvarchar to numeric."
If i treat my variables like normal text ('') and use the output generated
it works fine.
I send you part of the script and hope that you can help me
declare @.dbsize dec(15,0),
@.dbnamesysname,
@.sql nvarchar(850)
declare dbid_cur cursor for
select [name]
from master..sysdatabases
where [name] <> 'tempdb' for read only
open dbid_cur
fetch next from dbid_cur into @.dbname
while @.@.fetch_status = 0
begin
set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
+ 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
varchar(20))'
print @.sql
-- sp_executesql @.sql
fetch next from dbid_cur into @.dbname
end
close dbid_cur
deallocate dbid_cur
Thanks and best regards
Have you looked at sp_helpdb? Does it give you what you want?
Keith
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards
|||How about
EXEC sp_msforeachdb 'USE ?; EXEC sp_spaceused'
(Note that the procedure is unsupported.)
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards

Get space used of all databases

Hello,
I need to get space information of all databases and report it. I create one
cursor that enter in every databases and report space used, but it returns
the following error.
"Server: Msg 8114, Level 16, State 5, Line 16
Error converting data type nvarchar to numeric."
If i treat my variables like normal text ('') and use the output generated
it works fine.
I send you part of the script and hope that you can help me
declare @.dbsize dec(15,0),
@.dbname sysname,
@.sql nvarchar(850)
declare dbid_cur cursor for
select [name]
from master..sysdatabases
where [name] <> 'tempdb' for read only
open dbid_cur
fetch next from dbid_cur into @.dbname
while @.@.fetch_status = 0
begin
set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
+ 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
varchar(20))'
print @.sql
-- sp_executesql @.sql
fetch next from dbid_cur into @.dbname
end
close dbid_cur
deallocate dbid_cur
Thanks and best regardsHave you looked at sp_helpdb? Does it give you what you want?
--
Keith
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards|||How about
EXEC sp_msforeachdb 'USE ?; EXEC sp_spaceused'
(Note that the procedure is unsupported.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards