Finding long-running blocks

Can anyone point me to a script (or alternative method) which will find bloc
king that has been going on for a while?
TIA,
John BrownJohn
http://support.microsoft.com/defaul...224453-- :
Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
(Q224453)
"John Brown" <anonymous@.discussions.microsoft.com> wrote in message
news:9F0A15E4-4DBB-4F4F-8DDE-1E822E0300E3@.microsoft.com...
> Can anyone point me to a script (or alternative method) which will find
blocking that has been going on for a while?
> TIA,
> John Brown|||Hi.
You can utilize the SQL Profiler [SQL Enterprise Manager -> Tools -> SQL
Profiler] or The Windows 2000 Monitor to verify the blocks of your database
. Through the Monitor can see a histograma graphic.
Hermilson.
****************************************
******************************
Can anyone point me to a script (or alternative method) which will find bloc
king that has been going on for a while?|||Spot on - thanks!
John

Finding long-running blocks

Can anyone point me to a script (or alternative method) which will find blocking that has been going on for a while
TIA
John BrownJohn
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224453-- :
Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
(Q224453)
"John Brown" <anonymous@.discussions.microsoft.com> wrote in message
news:9F0A15E4-4DBB-4F4F-8DDE-1E822E0300E3@.microsoft.com...
> Can anyone point me to a script (or alternative method) which will find
blocking that has been going on for a while?
> TIA,
> John Brown|||Hi
You can utilize the SQL Profiler [SQL Enterprise Manager -> Tools -> SQL Profiler] or The Windows 2000 Monitor to verify the blocks of your database. Through the Monitor can see a histograma graphic
Hermilson
*********************************************************************
Can anyone point me to a script (or alternative method) which will find blocking that has been going on for a while|||Spot on - thanks
Johnsql

Finding all SQL users permissions and access on every server and DB?

Hello team, I have learned quite a bit from everyone here. I have decided to post a question for you guys and see what the best method is. Our Sr. DBA has asked me to get a list of all of our users permissions on all of our databases. We have about 20 SQL servers some of which have more databases and instances than I have fingers and toes. Can anyone recommend any solution to this problem? Perhaps there is a script I can download/write which will help me on this? Thanks in advance.

Sorry if I didn't provide enough information on my first post. Listed below are the requirements of items I need to put in an excel sheet. Thanks!

I need to gather:

Who has admin access to what servers

What are the log in accounts and what is it used for in SQL

Which users have which rights to which databases

Thanks again.

|||

Are all servers running SQL Server 2005, or do you have a mix of versions? This list can get to be pretty long very easily. Anyway, assuming you have only SQL Server 2005 servers, here are some pointers:

1) For admin access, you can query members of sysadmin and, additionally, you can query grantees of CONTROL SERVER. Check the sys.server_role_members and sys.server_permissions catalogs.

2) For login accounts, check the sys.server_principals catalog. What they are used for in SQL is something that isn't known to the database - the semantics of use of a certain entity are not captured anywhere - only its capabilities are. So you can find out what those logins can do, but not necessarily what they are meant to do.

3) Database owners and sysadmins aside, you need to list the users from sys.database_principals. Their permissions are stored in sys.database_permissions. For permissions inherited from groups or roles, you have to do separate queries - not a trivial task.

I'm not sure how helpful this information will be to you, even if you go to the trouble of getting it - I expect you will get way more data than any single person will actually want to analyze. It would be simpler to think of what you want to get out of this information and search for that particular thing specifically.

Thanks

Laurentiu

|||

Thank you very much for the considerate answer, I'll look into those and my appologies for not mentioning we are only running SQL 2000 exclusively. We will upgrade to 2005 in about 1-3 months.

|||

Ok, on SQL Server 2000 you would do something similar, but you will need to use the old catalogs. I don't know their names by heart, I just remember sysprotects was storing the permissions, but you can search Books Online and it should contain this information. If you can't figure out how to query the old catalogs, let us know.

Thanks

Laurentiu

|||

Hi Laurentiu.

I have a big problem and I would like to ask you via e-mail. I cannot find your e-mail, so please write to me on gg@.moj.pl.

I hope you will contact me. Thank you very much.

Jack.

|||

Ok so this is what I have done so far:

SELECT DISTINCT syslogins.loginname, syslogins.dbname,
@.@.SERVERNAME AS 'ServerName'
FROM sysprotects, syslogins
WHERE sysadmin = 1

BUILTIN\Administrators master HMIS-MMCSPADDEN\JDELOCAL
sa master HMIS-MMCSPADDEN\JDELOCAL

I believe this is giving me the information for the users who can connect to our SQL server, but what about the database and table access rights? Where would I find those? Thanks in advance.

|||

Note that you can drop the sysprotects from your statement, as you are not selecting anything from it.

For database permissions, you can use syspermissions: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx. You can ignore sysprotects, as you can query syspermissions instead, but here is the link for it as well: http://msdn2.microsoft.com/en-us/library/aa260449(SQL.80).aspx

For database users, you can use sysusers: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx.

For role memberships in a database, you can check sysmembers: http://msdn2.microsoft.com/en-us/library/aa260440(SQL.80).aspx

The article for syslogins is here: http://msdn2.microsoft.com/en-us/library/aa260424(SQL.80).aspx.

And, to browse all system catalogs in SQL Server 2000, you can start from here: http://msdn2.microsoft.com/en-us/library/aa260604(SQL.80).aspx.

Thanks

Laurentiu

|||Wow thanks!

Finding all SQL users permissions and access on every server and DB?

Hello team, I have learned quite a bit from everyone here. I have decided to post a question for you guys and see what the best method is. Our Sr. DBA has asked me to get a list of all of our users permissions on all of our databases. We have about 20 SQL servers some of which have more databases and instances than I have fingers and toes. Can anyone recommend any solution to this problem? Perhaps there is a script I can download/write which will help me on this? Thanks in advance.

Sorry if I didn't provide enough information on my first post. Listed below are the requirements of items I need to put in an excel sheet. Thanks!

I need to gather:

Who has admin access to what servers

What are the log in accounts and what is it used for in SQL

Which users have which rights to which databases

Thanks again.

|||

Are all servers running SQL Server 2005, or do you have a mix of versions? This list can get to be pretty long very easily. Anyway, assuming you have only SQL Server 2005 servers, here are some pointers:

1) For admin access, you can query members of sysadmin and, additionally, you can query grantees of CONTROL SERVER. Check the sys.server_role_members and sys.server_permissions catalogs.

2) For login accounts, check the sys.server_principals catalog. What they are used for in SQL is something that isn't known to the database - the semantics of use of a certain entity are not captured anywhere - only its capabilities are. So you can find out what those logins can do, but not necessarily what they are meant to do.

3) Database owners and sysadmins aside, you need to list the users from sys.database_principals. Their permissions are stored in sys.database_permissions. For permissions inherited from groups or roles, you have to do separate queries - not a trivial task.

I'm not sure how helpful this information will be to you, even if you go to the trouble of getting it - I expect you will get way more data than any single person will actually want to analyze. It would be simpler to think of what you want to get out of this information and search for that particular thing specifically.

Thanks

Laurentiu

|||

Thank you very much for the considerate answer, I'll look into those and my appologies for not mentioning we are only running SQL 2000 exclusively. We will upgrade to 2005 in about 1-3 months.

|||

Ok, on SQL Server 2000 you would do something similar, but you will need to use the old catalogs. I don't know their names by heart, I just remember sysprotects was storing the permissions, but you can search Books Online and it should contain this information. If you can't figure out how to query the old catalogs, let us know.

Thanks

Laurentiu

|||

Hi Laurentiu.

I have a big problem and I would like to ask you via e-mail. I cannot find your e-mail, so please write to me on gg@.moj.pl.

I hope you will contact me. Thank you very much.

Jack.

|||

Ok so this is what I have done so far:

SELECT DISTINCT syslogins.loginname, syslogins.dbname,
@.@.SERVERNAME AS 'ServerName'
FROM sysprotects, syslogins
WHERE sysadmin = 1

BUILTIN\Administrators master HMIS-MMCSPADDEN\JDELOCAL
sa master HMIS-MMCSPADDEN\JDELOCAL

I believe this is giving me the information for the users who can connect to our SQL server, but what about the database and table access rights? Where would I find those? Thanks in advance.

|||

Note that you can drop the sysprotects from your statement, as you are not selecting anything from it.

For database permissions, you can use syspermissions: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx. You can ignore sysprotects, as you can query syspermissions instead, but here is the link for it as well: http://msdn2.microsoft.com/en-us/library/aa260449(SQL.80).aspx

For database users, you can use sysusers: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx.

For role memberships in a database, you can check sysmembers: http://msdn2.microsoft.com/en-us/library/aa260440(SQL.80).aspx

The article for syslogins is here: http://msdn2.microsoft.com/en-us/library/aa260424(SQL.80).aspx.

And, to browse all system catalogs in SQL Server 2000, you can start from here: http://msdn2.microsoft.com/en-us/library/aa260604(SQL.80).aspx.

Thanks

Laurentiu

|||Wow thanks!

Find out the database name from the connection string

Hi,
Is there any standard string method to find out the database name from the connection string..
Thanks
Once you're connected, SELECT DB_NAME()
http://www.aspfaq.com/
(Reverse address to reply.)
"DbQuester" <anonymous@.discussions.microsoft.com> wrote in message
news:C4725B60-6FD5-4DCA-BE75-D2839E3B53D9@.microsoft.com...
> Hi,
> Is there any standard string method to find out the database name from the
connection string..
> Thanks
|||ok thanks, but wouldnt it be better to have the database name right in the app, instead of telling t-sql to talk to the database and then give out the result.. my connection string will be in app.config file and I want to just split it out and use info li
ke InstanceName and database etc all throughtout my app..
thanks
|||(a) If you're already connected to the database, SELECT DB_NAME() is not
going to be any more of a performance hit than string parsing. And it's
still something you only have to do once.
(b) In your app config file, keep the parameters you want separate and then
build the connection string in the app. This way you can keep certain
attributes in separate variables.
(c) There is no magic string parser that will look at a connection string
and tell you certain attribute values. You can use regular expressions, but
see (a).
http://www.aspfaq.com/
(Reverse address to reply.)
"DbQuester" <anonymous@.discussions.microsoft.com> wrote in message
news:CCB255A7-DF84-4BB4-B121-4975FE7C38B0@.microsoft.com...
> ok thanks, but wouldnt it be better to have the database name right in the
app, instead of telling t-sql to talk to the database and then give out the
result.. my connection string will be in app.config file and I want to just
split it out and use info like InstanceName and database etc all throughtout
my app..
> thanks
|||Thanks, it makes sense..
|||1. You could create a UDL and test it against the SQL Server/DB in question.
2. Then open the UDL as a notepad and copy the connection string which you
can use for app's init file.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Find out the database name from the connection string

Hi,
Is there any standard string method to find out the database name from the c
onnection string..
ThanksOnce you're connected, SELECT DB_NAME()
http://www.aspfaq.com/
(Reverse address to reply.)
"DbQuester" <anonymous@.discussions.microsoft.com> wrote in message
news:C4725B60-6FD5-4DCA-BE75-D2839E3B53D9@.microsoft.com...
> Hi,
> Is there any standard string method to find out the database name from the
connection string..
> Thanks|||ok thanks, but wouldnt it be better to have the database name right in the a
pp, instead of telling t-sql to talk to the database and then give out the r
esult.. my connection string will be in app.config file and I want to just s
plit it out and use info li
ke InstanceName and database etc all throughtout my app..
thanks|||(a) If you're already connected to the database, SELECT DB_NAME() is not
going to be any more of a performance hit than string parsing. And it's
still something you only have to do once.
(b) In your app config file, keep the parameters you want separate and then
build the connection string in the app. This way you can keep certain
attributes in separate variables.
(c) There is no magic string parser that will look at a connection string
and tell you certain attribute values. You can use regular expressions, but
see (a).
http://www.aspfaq.com/
(Reverse address to reply.)
"DbQuester" <anonymous@.discussions.microsoft.com> wrote in message
news:CCB255A7-DF84-4BB4-B121-4975FE7C38B0@.microsoft.com...
> ok thanks, but wouldnt it be better to have the database name right in the
app, instead of telling t-sql to talk to the database and then give out the
result.. my connection string will be in app.config file and I want to just
split it out and use info like InstanceName and database etc all throughtout
my app..
> thanks|||Thanks, it makes sense..|||1. You could create a UDL and test it against the SQL Server/DB in question.
2. Then open the UDL as a notepad and copy the connection string which you
can use for app's init file.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Find out the database name from the connection string

Hi
Is there any standard string method to find out the database name from the connection string.
ThanksOnce you're connected, SELECT DB_NAME()
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DbQuester" <anonymous@.discussions.microsoft.com> wrote in message
news:C4725B60-6FD5-4DCA-BE75-D2839E3B53D9@.microsoft.com...
> Hi,
> Is there any standard string method to find out the database name from the
connection string..
> Thanks|||ok thanks, but wouldnt it be better to have the database name right in the app, instead of telling t-sql to talk to the database and then give out the result.. my connection string will be in app.config file and I want to just split it out and use info like InstanceName and database etc all throughtout my app.
thanks|||(a) If you're already connected to the database, SELECT DB_NAME() is not
going to be any more of a performance hit than string parsing. And it's
still something you only have to do once.
(b) In your app config file, keep the parameters you want separate and then
build the connection string in the app. This way you can keep certain
attributes in separate variables.
(c) There is no magic string parser that will look at a connection string
and tell you certain attribute values. You can use regular expressions, but
see (a).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DbQuester" <anonymous@.discussions.microsoft.com> wrote in message
news:CCB255A7-DF84-4BB4-B121-4975FE7C38B0@.microsoft.com...
> ok thanks, but wouldnt it be better to have the database name right in the
app, instead of telling t-sql to talk to the database and then give out the
result.. my connection string will be in app.config file and I want to just
split it out and use info like InstanceName and database etc all throughtout
my app..
> thanks|||1. You could create a UDL and test it against the SQL Server/DB in question.
2. Then open the UDL as a notepad and copy the connection string which you
can use for app's init file.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

find invalid views etc.

Hi,
Newbie as I am I cannot find the method to track down invalid objects in a MS SQLServer database.
Looking through documentation and looking at internet does not provide much help.
Please help
WilcoThe only method you find is the one you create using the Mark 1 mod 0 eyeball!!

For instance ... you create and sucessfully compile a sproc. Then you alter the schema of the table and drop a column. The proc knows nothing about the altered table until you try to execute it, at which time SQL Server will throw the error.