Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Queries to know your server configurations and settings

adaptive server

Queries to know your server configurations and settings

by  DotNetGnat  Posted    (Edited  )
Hello Everyone,

I have seen these below questions being frequently asked in this forum:

1. How can i list all users in my database?
2. How can list roles of all users in my database?
and so on...

i have this small collection, gleaned over some time on internet and i thougth would be helpful to you all if i put it as a FAQ.

Please provide feedback or suggestions or any corrections.

1. To list Remote Servers, Backup servers and other Serverson which your Adaptive

Server can execute RPC, along with server options

Code: 
[blue]exec sp_helpserver[/blue]

2. To get information about the databases present on a particular server

Code: 
[blue]exec sp_helpdb [/blue]

3. To check which authentication mode is enabled

Code: 
[blue]exec sp_loginconfig "login mode"
if the value is:
0- Standard Mode
1- Integrated Mode
2- Mixed Mode
[/blue]

4. To find out information about the default login

Code: 
[blue]exec sp_loginconfig "default account"[/blue]

5. To get the detail of current version and patches applied

Code: 
[blue]Select @@VERSION[/blue]

6. To get current configuration for the sybase ASE Server

Code: 
[blue]exec sp_configure[/blue]

7. To Check for 'allow updates to system table'

Code: 
[blue]exec sp_configure "allow updates to system tables"[/blue]

8. To check for the key 'allow resource limit', and ensure it is set to '1'

Code: 
[blue]exec sp_configure "allow resource limit"[/blue]

9. To ensure that system table 'syscomments' is protected

Code: 
[blue]exec sp_configure "select on syscomments.text"[/blue]

10. To check if failed login attempts are logged to error logs
Code: 
[blue]exec sp_configure "log audit logon failure"[/blue]

11. To check if successful login attempts are logged to error logs
Code: 
[blue]exec sp_configure "log audit logon success"[/blue]

12.To get all groups present in the particular database
Code: 
[blue]
Use DBName
exec sp_helpgroup
[/blue]

13. To get a group-wise listing of users present in each database
Code: 
[blue]
Use DBName
exec sp_helpgroup GroupName
[/blue]

14. To check for Server Roles and User-Defined roles present on server

Code: 
[blue]
Select name, password, pwdate, status from syssrvroles

[/blue]

15. To obtain detailed information of each role

Code: 
[blue]
exec sp_displayroles "RoleName", expand_up
'you can also use expand_down
[/blue]

16. To get the details about roles of a particular user
Code: 
[blue]
Exec sp_displayroles UserName, expand_down
[/blue]

17. To check all roles without a password
Code: 
[blue]
select name from syssrvroles where password=NULL
[/blue]

18. To get all users in a particular database
Code: 
[blue]
Exec sp_helpuser
[/blue]

19. To get the hashed user passwords
Code: 
[blue]
select name, apssword from syslogins
[/blue]

20. To get detailed information about a particular user
Code: 
[blue]
sp_displaylogin UserName
[/blue]
21. To get information about permissions assigned to users
Code: 
[blue]
Use DBName
exec sp_helpprotect
'execute this query for each database
[/blue]

22. To check for System-wide password expiration interval
Code: 
[blue]
exec sp_configure "password expiration interval"
[/blue]

23. To check if password contains at least one digit
Code: 
[blue]
exec sp_configure "check password for digit"
[/blue]

24. To check for server wide minimum password length
Code: 
[blue]
exec sp_configure "minimum password length"
[/blue]

25. To check permissions on critical tables, procedures, triggers
Code: 
[blue]
Use DBName
exec sp_helpprotect ObjectName
[/blue]

Thanks

-DNG








Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top