QUERY: 1 SQL Server restricted access according to user account...

Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
displays specific databases corresponding to the user account logged in?
Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>

QUERY: 1 SQL Server restricted access according to user account...

Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
displays specific databases corresponding to the user account logged in?Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>

QUERY: 1 SQL Server restricted access according to user account...

Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
displays specific databases corresponding to the user account logged in?Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>

Query/test for db_owner role?

Is there a SQL query that can explicitly return the roles that the current
SQL user has been explicitly assigned? I'm trying to find out if the user
has been given the db_owner role (versus it being implied by the user being
an administrator).
Thanks!
What version of SQL server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>
|||Would this work for you?
sp_helprolemember 'db_owner'
Ben Nevarez, MCDBA, OCP
Database Administrator
"R Baker" wrote:

> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>
>
|||Yes, that might. I'll have to figure out how to process the result set using
the MFC CDatabase classes, but it seems doable.
Thanks.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...[vbcol=seagreen]
> Would this work for you?
> sp_helprolemember 'db_owner'
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "R Baker" wrote:
|||SQL 2000 or SQL 2005 (mainly SQL 2000 for now).
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:erz8UAWPGHA.3576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> What version of SQL server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "R Baker" <nospamx@.nospamx.com> wrote in message
> news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
|||It is only one resultset, so you would process it like any resultset (as if you were sending a
SELECT from your app, or executing your own stored procedure which returns a result set).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:eTB3u8WPGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Yes, that might. I'll have to figure out how to process the result set using the MFC CDatabase
> classes, but it seems doable.
> Thanks.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
>

Query/test for db_owner role?

Is there a SQL query that can explicitly return the roles that the current
SQL user has been explicitly assigned? I'm trying to find out if the user
has been given the db_owner role (versus it being implied by the user being
an administrator).
Thanks!What version of SQL server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>|||Would this work for you?
sp_helprolemember 'db_owner'
Ben Nevarez, MCDBA, OCP
Database Administrator
"R Baker" wrote:
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>
>|||Yes, that might. I'll have to figure out how to process the result set using
the MFC CDatabase classes, but it seems doable.
Thanks.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
> Would this work for you?
> sp_helprolemember 'db_owner'
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "R Baker" wrote:
>> Is there a SQL query that can explicitly return the roles that the
>> current
>> SQL user has been explicitly assigned? I'm trying to find out if the user
>> has been given the db_owner role (versus it being implied by the user
>> being
>> an administrator).
>> Thanks!
>>|||SQL 2000 or SQL 2005 (mainly SQL 2000 for now).
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:erz8UAWPGHA.3576@.TK2MSFTNGP15.phx.gbl...
> What version of SQL server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "R Baker" <nospamx@.nospamx.com> wrote in message
> news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
>> Is there a SQL query that can explicitly return the roles that the
>> current SQL user has been explicitly assigned? I'm trying to find out if
>> the user has been given the db_owner role (versus it being implied by the
>> user being an administrator).
>> Thanks!|||It is only one resultset, so you would process it like any resultset (as if you were sending a
SELECT from your app, or executing your own stored procedure which returns a result set).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:eTB3u8WPGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Yes, that might. I'll have to figure out how to process the result set using the MFC CDatabase
> classes, but it seems doable.
> Thanks.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
>> Would this work for you?
>> sp_helprolemember 'db_owner'
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "R Baker" wrote:
>> Is there a SQL query that can explicitly return the roles that the current
>> SQL user has been explicitly assigned? I'm trying to find out if the user
>> has been given the db_owner role (versus it being implied by the user being
>> an administrator).
>> Thanks!
>>
>

Query/test for db_owner role?

Is there a SQL query that can explicitly return the roles that the current
SQL user has been explicitly assigned? I'm trying to find out if the user
has been given the db_owner role (versus it being implied by the user being
an administrator).
Thanks!What version of SQL server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gb
l...
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user bein
g
> an administrator).
> Thanks!
>|||Would this work for you?
sp_helprolemember 'db_owner'
Ben Nevarez, MCDBA, OCP
Database Administrator
"R Baker" wrote:

> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user bein
g
> an administrator).
> Thanks!
>
>|||Yes, that might. I'll have to figure out how to process the result set using
the MFC CDatabase classes, but it seems doable.
Thanks.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...[vbcol=seagreen]
> Would this work for you?
> sp_helprolemember 'db_owner'
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "R Baker" wrote:
>|||SQL 2000 or SQL 2005 (mainly SQL 2000 for now).
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:erz8UAWPGHA.3576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> What version of SQL server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "R Baker" <nospamx@.nospamx.com> wrote in message
> news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...|||It is only one resultset, so you would process it like any resultset (as if
you were sending a
SELECT from your app, or executing your own stored procedure which returns a
result set).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:eTB3u8WPGHA.3936@.TK2MSFTNGP10.phx.gbl.
.
> Yes, that might. I'll have to figure out how to process the result set usi
ng the MFC CDatabase
> classes, but it seems doable.
> Thanks.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
>

query w/ case help

Hey all, here is my problem: I want to run a query to grab all the 'next dates' from a column after the user inputs a date. .. and then repeat accordingly.

Here is some simplified example code: This works fine (but doesn't grab all rows, which varies, with the Date. It only grabs the first, hence the 'top 1')

select top 1 datewrk
from hours
where datewrk is not null and datewrk > '06/15/2004' and purchord = '4112'
order by datewrk

Soo, then this grabs all the rows with the Date, but doesn't 'skip' correctly. If you pick the date right before a valid row, as in there are rows of data for Date 6/18/2004 and you pick 6/17/2004 it will bring up the next date fine. BUT if you pick 6/15/2004 it will not 'skip ahead'. Any ideas? Thanks

select datewrk
from hours
where datewrk is not null and datewrk > '06/15/2004' and 1 = (case when Datewrk = (select min(Datewrk) from Hours where Datewrk is not null and Datewrk > '06/15/2004') then 1 else 0 end) and purchord = '4112'
order by datewrkDoes this work:SELECT datewrk
FROM hours
WHERE datewrk IS NOT NULL
AND datewrk > '06/15/2004'
AND purchord = '4112'
AND 1 = (case when Datewrk = (select min(Datewrk)
FROM Hours
WHERE Datewrk IS NOT NULL
AND purchord = '4112'
AND Datewrk > '06/15/2004') THEN 1 ELSE 0 END)
ORDER BY datewrk-PatP|||Yes, that works! THANK YOU.

Query user/worlstation access of database

I have a client who needs to be able to run a query that shows a list of all
workstations (IP address, logon etc) who are currently accessing a specified
SQL database with logon / last activity time (if possible).
Most of the users are using a web application to access the database, so how
would we identify which users are accessing the database through a web
server. There are some however who are using the fat client application to
access the same database.
Is this possible?see if this is what you want?
select * from sysprocesses
--
"Mark - HIS" wrote:

> I have a client who needs to be able to run a query that shows a list of a
ll
> workstations (IP address, logon etc) who are currently accessing a specifi
ed
> SQL database with logon / last activity time (if possible).
> Most of the users are using a web application to access the database, so h
ow
> would we identify which users are accessing the database through a web
> server. There are some however who are using the fat client application to
> access the same database.
> Is this possible?
>|||If using SQL Server 2000 (not sure about the 2005 equivalent) you can
view this info through the GUI: Under the "Management" node there
should be a node called "Current Activity" and under that a node called
"Process Info" & this is the one you want. You should click on this
node to get a display that contains, among other things, the following
info:
* The SQL Server login that is associated with a process
* The host that the process connected from (if access is via a web
server the entry for the host should be the name of the web server)
* Time logged in
* Time of last batch/command execution
* Command that was last executed (double click on a process for this
information)
In SQL Server 2000 you have the sp_who (you also have the undocumented
sp_who2 stored proc in SQL Server 2000, not sure if it's in SQL Server
2005) which will materialize a result set. In this result set, the
following may be of interest to you:
* loginname - The SQL Server login associated with a process
* hostname - The host the process connected from
In addition, sp_who2 provides a column (lastbatch) that gives the date
and time when the last command/batch is executed for a process.
Quick example calls:
EXEC sp_who
GO
EXEC sp_who2
GO
Hope that helps a bit|||This table is in the master database.
So use
select * from master.dbo.sysprocesses.|||Anyone connecting to the web server will show up as the network user that
the web page is running under. If you disable anonymous access and have the
users login with their network credentials, I think these MAY show up as the
network account in SQL Server, but I have not tried this myself, so I could
be wrong.
The IP address will always be the web server itself, since that is where the
connection is made.
The SQL Server user will show up as whatever login the web server is using
to connect to the database. If you are using the same connection string for
all users, then that is the ID that will show up.
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:48AAC7C6-FEA9-4090-9A85-DC502F55EE86@.microsoft.com...
> I have a client who needs to be able to run a query that shows a list of
all
> workstations (IP address, logon etc) who are currently accessing a
specified
> SQL database with logon / last activity time (if possible).
> Most of the users are using a web application to access the database, so
how
> would we identify which users are accessing the database through a web
> server. There are some however who are using the fat client application to
> access the same database.
> Is this possible?
>

Query to Search all fields in simple table

I am trying to write a simple search page that will searchall the fields in a database to find all records that match a user input string. The string could happen anywhere in any of the fields. I have a dataset and can write a query but am unsure what the format is for this simple task. I figured it would look like this:

SELECT Table.*

FROM Table

WHERE * = @.USERINPUT

But thats not working. Can someone help.? Thanks..

Not a simple task, but this should get you started.

SELECT *

FROM Table

WHERE field1 LIKE '%' + @.UserInput + '%' OR field2 LIKE'%'+@.UserInput+'%' OR...

sql

Query to retreive user tables in sql db

Is there a way to create a query that will return all user tables inside a sql db

ThanxYou can SELECT * FROM a sysobjects table, where
you can filter the records by a 'U'ser type.

Regards,|||Microsoft always advises against querying the system tables directly. You could use the INFORMATION_SCHEMA.Tables view instead.

SELECT * FROM INFORMATION_SCHEMA.Tables WHERE Table_Catalog = 'myDatabase'

Terri|||what about a list of stored procedures using INFORMATION_SCHEMA?|||I am pretty sure you can get those out of INFORMATION_SCHEMA.ROUTINES. Check out the Index of Books Online.

Terri

Query to report database user role membership

Hi All,

I using sql server 2005 sp2. I'm trying to construct a query that'll show me all roles and the users that are in the roles. I'm having trouble figuring how sql server stores this information in the system tables. I think it's based around the database_principals system tables but haven't been able to display the info i need.

If anyone has a query like this can they please help me?

thanks,
Dave
I figured it out. I found the sys.database_role_members which had the info i was after:

select distinct c.name, b.name from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner join sys.database_principals c on c.principal_id = a.role_principal_id
and b.type <> 'R

Query to report database user role membership

Hi All,

I using sql server 2005 sp2. I'm trying to construct a query that'll show me all roles and the users that are in the roles. I'm having trouble figuring how sql server stores this information in the system tables. I think it's based around the database_principals system tables but haven't been able to display the info i need.

If anyone has a query like this can they please help me?

thanks,
Dave
I figured it out. I found the sys.database_role_members which had the info i was after:

select distinct c.name, b.name from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner join sys.database_principals c on c.principal_id = a.role_principal_id
and b.type <> 'R

Query to get the all the users from ADS datasource

Please help me to get the all the user from ADS, i searched a lot and found we can get that using linked server,

i ran the following query to add the linked server,

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'

The query ran succesfully and the 'ADSI' has been added as linked server

I got a query from net to get the users from ADS

SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')

I am not able to understand the query above and what i need to give to get my ADS users

please help me.

i tried from my side after refering these links ... go through this...

http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx

http://support.microsoft.com/kb/299410

http://blogs.msdn.com/euanga/archive/2007/03/22/faq-how-do-i-query-active-directory-from-sql-server.aspx

Madhu

|||

Madhu,

I am having the same problem as posted above, but despite trying your suggestions, it still doesnt work.

The code used:

Code Block

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'

SELECT * FROM OPENQUERY( ADSI,
'SELECT name, adsPath
FROM ''LDAP://DC=myCompany,DC=lan''
WHERE objectCategory = ''Person'' AND objectClass= ''user''')

PS: the domain is myCompany.lan

We use windows authentication mode. I dont belong to the administrators' group (which is systems administrator's stuff here).

May it be something related to the linked server's security? I've tried "be made using the login's current security context" and "be made without a security context" (we never know ). Both without sucess.

Or would it be related to AD reading permissions?

|||

Well, a clue:

Digging around, I've found this script at http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug04/hey0824.mspx:

Code Block

On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = _
"SELECT Name FROM 'LDAP://dc=myCompany,dc=lan' " & _
"WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst
Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields("Name").Value
objRecordSet.MoveNext
Loop

It works like charm. Whatever it may be, is not related to permissions. An interesting thing: in Brendan Tompkins' blog http://adsdsoobject.codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx, he quotes this link: http://www.dbforums.com/archive/index.php/t-958399.html, which mentions certain windows registry keys I havent found in both my server and desktops: there is no entry called "provider", under the mentioned path. Is there anything missing here?

Thanks in advance!

|||

More news:

Indeed, the query runs smoothly... using SSMS on the server machine. But not in any workstation.

As I said, I can run that very vb script quoted above from my workstation, but not the query in SSMS. Why is it so?

No clues?!

|||

Do you get any error when you are not expecting correct results?

If it doesn't work with server name you might try with IP Address instead.

|||

Thanks for your reply, Satya.

I didnt understand your first question...

Actually, the query below doesnt work when I try to run from my workstation (that same "An error occurred while preparing the query..." error). I've tried using IP as you suggested:

select * from openquery
(ADSI,'SELECT name
FROM ''LDAP://172.23.0.21''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')

Something weird: it doesnt work while running from SSMS right on the server, either! I get this error:

Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".

Query to get the all the users from ADS datasource

Please help me to get the all the user from ADS, i searched a lot and found we can get that using linked server,

i ran the following query to add the linked server,

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'

The query ran succesfully and the 'ADSI' has been added as linked server

I got a query from net to get the users from ADS

SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')

I am not able to understand the query above and what i need to give to get my ADS users

please help me.

i tried from my side after refering these links ... go through this...

http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx

http://support.microsoft.com/kb/299410

http://blogs.msdn.com/euanga/archive/2007/03/22/faq-how-do-i-query-active-directory-from-sql-server.aspx

Madhu

|||

Madhu,

I am having the same problem as posted above, but despite trying your suggestions, it still doesnt work.

The code used:

Code Block

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'

SELECT * FROM OPENQUERY( ADSI,
'SELECT name, adsPath
FROM ''LDAP://DC=myCompany,DC=lan''
WHERE objectCategory = ''Person'' AND objectClass= ''user''')

PS: the domain is myCompany.lan

We use windows authentication mode. I dont belong to the administrators' group (which is systems administrator's stuff here).

May it be something related to the linked server's security? I've tried "be made using the login's current security context" and "be made without a security context" (we never know ). Both without sucess.

Or would it be related to AD reading permissions?

|||

Well, a clue:

Digging around, I've found this script at http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug04/hey0824.mspx:

Code Block

On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = _
"SELECT Name FROM 'LDAP://dc=myCompany,dc=lan' " & _
"WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst
Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields("Name").Value
objRecordSet.MoveNext
Loop

It works like charm. Whatever it may be, is not related to permissions. An interesting thing: in Brendan Tompkins' blog http://adsdsoobject.codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx, he quotes this link: http://www.dbforums.com/archive/index.php/t-958399.html, which mentions certain windows registry keys I havent found in both my server and desktops: there is no entry called "provider", under the mentioned path. Is there anything missing here?

Thanks in advance!

|||

More news:

Indeed, the query runs smoothly... using SSMS on the server machine. But not in any workstation.

As I said, I can run that very vb script quoted above from my workstation, but not the query in SSMS. Why is it so?

No clues?!

|||

Do you get any error when you are not expecting correct results?

If it doesn't work with server name you might try with IP Address instead.

|||

Thanks for your reply, Satya.

I didnt understand your first question...

Actually, the query below doesnt work when I try to run from my workstation (that same "An error occurred while preparing the query..." error). I've tried using IP as you suggested:

select * from openquery
(ADSI,'SELECT name
FROM ''LDAP://172.23.0.21''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')

Something weird: it doesnt work while running from SSMS right on the server, either! I get this error:

Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".

Query to get all user tables with columns

Hi,

I tried to create a simple view as follows

CREATE VIEW V_ALL_USERTABLE_COLUMNS
AS
(
SELECT
OBJ.NAME as TableName,
COL.NAME as ColName,
TYP.NAME AS TYPE

FROM
SYSOBJECTS OBJ,
SYSCOLUMNS COL,
SYSTYPES TYP

WHERE
OBJ.TYPE = 'U'
AND OBJ.ID = COL.ID
AND COL.TYPE = TYP.TYPE
)

Combined with consistent naming conventions I will use this view to
easily find foreign keys; a la

SELECT *
FROM V_ALL_USERTABLE_COLUMNS
WHERE ColName LIKE ('%user_id')

There is something wrong with my view definition that I don't get
though; it doesn't return all the columns. I have a table with the
following definition

CREATE TABLE [dbo].[c_messages]
(
[cid] [int] IDENTITY (1, 1) NOT NULL ,
[touser_id] [int] NULL ,
[tosession_id] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fromuser_id] [int] NOT NULL ,
[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[message_read] [bit] NOT NULL ,
[logout] [bit] NULL
) ON [PRIMARY]
GO

The problem is that the select I used to define the view doesn't
return the touser_id column. I have sort of a sneaking suspicion that
the problem has to do with joining syscolumns.type to systypes.type,
but I don't know what to do instead (I'd really like to include the
type; it's useful if I ever changed the type of a primary key and want
to check that I also changed all the foreign keys).

Any help would be appreciated!Use the information schema rather than the system tables:

SELECT * FROM information_schema.columns

This format is much easier to use.

Your original query should work if you join on XTYPE rather than TYPE
but this is not recommended. In general you should avoid referencing
system tables directly.

--
David Portas
SQL Server MVP
--

Query to find who runs jobs

I would like to create a query to find what user owns the job. It probably is in the master db, but I wouldn't know where to begin other than that. Telling me how to either change the job owner or create a job through t-sql would also help. Thanks
-Kyle

Not quite. You should check msdb for all things SQL Agent/job related.

Rather than querying the tables directly though, i'd recommend using the system sprocs:

Check out sp_update_job, sp_add_job, sp_help_job (plus associated sprocs) in Books Online.

HTH!

query to find service account user

Is there a way to query to find the user that the service account is running
under?select SYSTEM_USER
"Vassago" <Vassago@.discussions.microsoft.com> wrote in message
news:23875C9F-58A3-4AF0-AA4F-9BB5F5BF585F@.microsoft.com...
> Is there a way to query to find the user that the service account is
> running
> under?

query to find service account user

Is there a way to query to find the user that the service account is running
under?select SYSTEM_USER
"Vassago" <Vassago@.discussions.microsoft.com> wrote in message
news:23875C9F-58A3-4AF0-AA4F-9BB5F5BF585F@.microsoft.com...
> Is there a way to query to find the user that the service account is
> running
> under?|||select SYSTEM_USER
"Vassago" <Vassago@.discussions.microsoft.com> wrote in message
news:23875C9F-58A3-4AF0-AA4F-9BB5F5BF585F@.microsoft.com...
> Is there a way to query to find the user that the service account is
> running
> under?

Query to find permissions..

Hello
I need a query that will show me what permissions a user has in a particular
database. I am not exactly sure how to go about doing that. I know I will
want to specify a database name and user name and based off of that
information for the query to go through and show what kind of permissions
this users has on user objects.
I am using SQL Server 2000.
Please advise..
Thank you,
BrettYou could start with
EXEC databasename..sp_helpuser 'username'
SELECT OBJECT_NAME(id),*
FROM databasename..syspermissions
WHERE grantee=USER_ID('username')
EXEC databasename..sp_msForEachTable 'EXEC sp_MSobjectprivs
@.objname=N''?'',@.grantee=''username'''
In the last output you will see an action column here, it corresponds to
this list, from Books Online
Go|URL: tsqlref.chm::/ts_sys-p_0837.htm
26 = REFERENCES
178 = CREATE FUNCTION
193 = SELECT
195 = INSERT
196 = DELETE
197 = UPDATE
198 = CREATE TABLE
203 = CREATE DATABASE
207 = CREATE VIEW
222 = CREATE PROCEDURE
224 = EXECUTE
228 = BACKUP DATABASE
233 = CREATE DEFAULT
235 = BACKUP LOG
236 = CREATE RULE
"Brett Davis" <bdavis123@.cox.net> wrote in message
news:Oo6rRZsnFHA.3068@.TK2MSFTNGP15.phx.gbl...
> Hello
> I need a query that will show me what permissions a user has in a
> particular database. I am not exactly sure how to go about doing that. I
> know I will want to specify a database name and user name and based off of
> that information for the query to go through and show what kind of
> permissions this users has on user objects.
> I am using SQL Server 2000.
> Please advise..
> Thank you,
> Brett
>

Query to connect to a database with different user

Hello:

Is there a way through T-SQL to run a store procedure and inside the store procedure, change the user, execute a select, and get back the original user that run the store procedure?

Thanks

In SQL 2005, yes there is.

Refer to Books Online, Topic: 'EXECUTE AS (Transact-SQL).

Example A demonstrates exactly what you are seeking.

|||

Thanks Arnie, is this possible in SQL 2000?|||

Unfortunately, no.

There is no easy way to cause security context switching.

|||You can use the Ad-Hoc remote quries, OPENROWSET & OPENDATASOURCE..