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
