Listing members of NT Group login

Does anybody have any experience with listing all the members who belong to
a
login that is a Windows Authenticated group using SQL Server 2k. My hunch i
s
that I will probably have to write some .net code using the activedirectory
namespace. anybody'exec xp_logininfo 'domain\group','members'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
wrote in message news:4D2CBEAB-6E96-46CE-B088-D7EBDCACAA11@.microsoft.com...
> Does anybody have any experience with listing all the members who belong
> to a
> login that is a Windows Authenticated group using SQL Server 2k. My hunch
> is
> that I will probably have to write some .net code using the
> activedirectory
> namespace. anybody'|||Thanks - I took it a little further because I needed the results in a table
-
create table ##li(
an nchar(128),
ty char(8),
priv char(9),
mln nchar(128),
pp nchar(128))
INSERT INTO ##li
EXEC xp_logininfo 'SAIC-US-WEST\CorporateITSDBA_Admins', 'members'
select * from ##li
"Jasper Smith" wrote:

> exec xp_logininfo 'domain\group','members'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
> wrote in message news:4D2CBEAB-6E96-46CE-B088-D7EBDCACAA11@.microsoft.com..
.
>
>

List database users & login

In SQL Server 2000, we can expand the console to "Database
\<database_name>\Users" and view the db user and the associated login
(detail view). But in SQL Server 2005, expanding the console "Database
\<database_name>\Security\Users", we can only see the db users and the
created date (detail view). I have to view the properties of user in
order to see the login associated to it.
Is that any way to list down all the database users and the associated
login for a particular database?
Thanks.
wodoy.peter
Hello,
For SQL 2005, In the SQL Server Management Studio -- Expand Security --
Logins --In the Right side pane--
Double clieck the login and choose user mapping. This will show the database
user mapping for all databases.
Thanks
Hari
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegrou ps.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>
|||You can say exec <dbname>.dbo.sp_helpuser from query analyser for any
database.
thks,
Manikanth S
MCDBA
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegrou ps.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>

List database users & login

In SQL Server 2000, we can expand the console to "Database
\<database_name>\Users" and view the db user and the associated login
(detail view). But in SQL Server 2005, expanding the console "Database
\<database_name>\Security\Users", we can only see the db users and the
created date (detail view). I have to view the properties of user in
order to see the login associated to it.
Is that any way to list down all the database users and the associated
login for a particular database?
Thanks.
wodoy.peterHello,
For SQL 2005, In the SQL Server Management Studio -- Expand Security --
Logins --In the Right side pane--
Double clieck the login and choose user mapping. This will show the database
user mapping for all databases.
Thanks
Hari
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>|||You can say exec <dbname>.dbo.sp_helpuser from query analyser for any
database.
thks,
Manikanth S
MCDBA
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>

List database users & login

In SQL Server 2000, we can expand the console to "Database
\<database_name>\Users" and view the db user and the associated login
(detail view). But in SQL Server 2005, expanding the console "Database
\<database_name>\Security\Users", we can only see the db users and the
created date (detail view). I have to view the properties of user in
order to see the login associated to it.
Is that any way to list down all the database users and the associated
login for a particular database?
Thanks.
wodoy.peterHello,
For SQL 2005, In the SQL Server Management Studio -- Expand Security --
Logins --In the Right side pane--
Double clieck the login and choose user mapping. This will show the database
user mapping for all databases.
Thanks
Hari
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>|||You can say exec <dbname>.dbo.sp_helpuser from query analyser for any
database.
thks,
Manikanth S
MCDBA
"wodoy.peter" <wodoy.peter@.gmail.com> wrote in message
news:1169999657.108525.46980@.h3g2000cwc.googlegroups.com...
> In SQL Server 2000, we can expand the console to "Database
> \<database_name>\Users" and view the db user and the associated login
> (detail view). But in SQL Server 2005, expanding the console "Database
> \<database_name>\Security\Users", we can only see the db users and the
> created date (detail view). I have to view the properties of user in
> order to see the login associated to it.
> Is that any way to list down all the database users and the associated
> login for a particular database?
> Thanks.
>
> wodoy.peter
>

List all the logins that have a certain privileges

Hi,
I would like to know how to get all the login names in a server that have a
certain privilege.
Such as I want to get all the logins that have an update privileges to a
table.
Thanks in advance
Frank
Hi,
Have a Look into sysprotects table. You could right a stored procedure to
query the sysprotects table in all the database where
a user have access.
Thanks
Hari
SQL Server MVP
"Frank" <wangping@.lucent.com> wrote in message
news:ehbHb51YFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to know how to get all the login names in a server that have
> a
> certain privilege.
> Such as I want to get all the logins that have an update privileges to a
> table.
> Thanks in advance
> Frank
>
|||While the information is in the sysprotects table, the difficult part is
that you must go through all of the groups that the user is a member of as
well... IF the user has 3 grants on a particular table, but is a member of
a group which is denied to the table, the permission is deny...
Also remember that is the object owners are the same, and the user is denied
to the table, but granted to a view on the table, he can access the table
via the view...
So doing this gets complicated very quickly... I would search for scripts on
the web as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Frank" <wangping@.lucent.com> wrote in message
news:ehbHb51YFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to know how to get all the login names in a server that have
> a
> certain privilege.
> Such as I want to get all the logins that have an update privileges to a
> table.
> Thanks in advance
> Frank
>

List all the logins that have a certain privileges

Hi,
I would like to know how to get all the login names in a server that have a
certain privilege.
Such as I want to get all the logins that have an update privileges to a
table.
Thanks in advance
FrankHi,
Have a Look into sysprotects table. You could right a stored procedure to
query the sysprotects table in all the database where
a user have access.
Thanks
Hari
SQL Server MVP
"Frank" <wangping@.lucent.com> wrote in message
news:ehbHb51YFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to know how to get all the login names in a server that have
> a
> certain privilege.
> Such as I want to get all the logins that have an update privileges to a
> table.
> Thanks in advance
> Frank
>|||While the information is in the sysprotects table, the difficult part is
that you must go through all of the groups that the user is a member of as
well... IF the user has 3 grants on a particular table, but is a member of
a group which is denied to the table, the permission is deny...
Also remember that is the object owners are the same, and the user is denied
to the table, but granted to a view on the table, he can access the table
via the view...
So doing this gets complicated very quickly... I would search for scripts on
the web as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Frank" <wangping@.lucent.com> wrote in message
news:ehbHb51YFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to know how to get all the login names in a server that have
> a
> certain privilege.
> Such as I want to get all the logins that have an update privileges to a
> table.
> Thanks in advance
> Frank
>

List all the logins that have a certain privileges

Hi,
I would like to know how to get all the login names in a server that have a
certain privilege.
Such as I want to get all the logins that have an update privileges to a
table.
Thanks in advance
FrankHi,
Have a Look into sysprotects table. You could right a stored procedure to
query the sysprotects table in all the database where
a user have access.
Thanks
Hari
SQL Server MVP
"Frank" <wangping@.lucent.com> wrote in message
news:ehbHb51YFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to know how to get all the login names in a server that have
> a
> certain privilege.
> Such as I want to get all the logins that have an update privileges to a
> table.
> Thanks in advance
> Frank
>|||While the information is in the sysprotects table, the difficult part is
that you must go through all of the groups that the user is a member of as
well... IF the user has 3 grants on a particular table, but is a member of
a group which is denied to the table, the permission is deny...
Also remember that is the object owners are the same, and the user is denied
to the table, but granted to a view on the table, he can access the table
via the view...
So doing this gets complicated very quickly... I would search for scripts on
the web as well.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Frank" <wangping@.lucent.com> wrote in message
news:ehbHb51YFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to know how to get all the login names in a server that have
> a
> certain privilege.
> Such as I want to get all the logins that have an update privileges to a
> table.
> Thanks in advance
> Frank
>

List all databases accessible by a login

Hi all. I am new to SQL-DMO so sorry if what i am asking is really easy.

Is it possible to get a list of all objects (including databases) which are accessible for a particular login.

So far I have:

Dim oSQLServer As SQLDMO.SQLServer
Dim oSQLDatabase As SQLDMO.Database
Dim oSQLLogin As New SQLDMO.Login
Dim oUser As New SQLDMO.User

oSQLServer = New SQLDMO.SQLServer
oSQLServer.Name = _oSQLServer.Host

' Check server is alive

If (oSQLServer.Status = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running) Then

Try

oSQLServer.LoginSecure = False

oSQLServer.Connect(_oSQLServer.Host, _oSQLServer.Login, _oSQLServer.Password)

For Each oSQLDatabase In oSQLServer.Databases

'oSQLDatabase.Users.

' Get a list of databases accessible by user

oSQLLogin.Name = _oSQLServer.Login

'oSQLObjectList = oSQLUser.ListOwnedObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj_Database)

'oSQLDMONameList = oSQLServer.ListMembers(SQLDMO.SQLDMO_ROLE_TYPE.SQLDMORole_All)

System.Diagnostics.Debug.Write(oSQLDatabase.Name.ToString & " : " & oSQLDatabase.IsUser(oSQLLogin.Name))

'oSQLDatabase.IsUser()

Next

Catch ex As Exception

System.Diagnostics.Debug.Write(ex.Message.ToString)

Finally

oSQLDatabase = Nothing

oSQLServer.DisConnect()

oSQLServer = Nothing

End Try

Else

System.Diagnostics.Debug.Write(oSQLServer.StatusInfoRefetchInterval( _

SQLDMO.SQLDMO_STATUSINFO_TYPE.SQLDMOStatInfo_All))

End If

The "oSQLLogin.Name = _oSQLServer.Login" statement does not seem to satisfy your requirements. Another side question, is there a particular reason why you are starting out with DMO? If not, you should consider using SMO, which ships with SQL Server 2005.

I have created a SMO program to meet your needs...The program checks to see if a user exists in the database. By access, you might want to check if the user has certain permissions. You may also need to deal with logins that are mapped to a different user name.

Server srv = new Server("MyServer"); //Yukon

srv.ConnectionContext.LoginSecure = true;

srv.ConnectionContext.Connect();

srv.DefaultTextMode = false;

string checkUser = "bob";

try

{

foreach (Database db in srv.Databases)

{

if (db.Users[checkUser] != null)

{

Console.WriteLine(checkUser + " has access to " + db.Name.ToString());

}

}

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

|||

I am using DMO as when I wrote the specification for the project, I stated that I would be using SQL-DMO. SQL 2005 had not be released and I could not wait for release date, incase of delays.

I am able to get a list of accessible databases for a particular login using

Dim oSQLDatabase As SQLDMO.Database

......

System.Diagnostics.Debug.WriteLine(oSQLDatabase.UserName & " user within database " & oSQLDatabase.Name)

However, it throughs an exception when every it hits the 'model' databases, dont suppose anybody knows why?

Thanks

DAN

|||

I tried the following code and it works against model (i.e no exception). What exception are you seeing? Which version of SQLDMO are you using -- located in "%ProgramFiles%\Microsoft SQL Server\80\Tools\Binn" folder? Which version of SQL Server are you targeting? (I am using the version of SQL-DMO that ships with SQL Server 2005 and I am targeting SQL Server 2000).

foreach (Interop.sqldmo._Database db in srv.Databases)

{

try

{

Console.WriteLine(db.Name + ":" + db.IsUser("bob"));

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

}

|||

Forget about the error, it was problem with the code else where in the applcation, conflicting names me thinks. Took your advice and moved over to SMO, after some alteration to the spec. Things a lot easier!

Thanks for your help Peter.