获得所有数据库名称:
use master
select [name] from [sysdatabases] order by [name]
获取数据库的所有表名:
select * from sysobjects where type='U'
查询用户赋值给别人的权限:
select b.id,b.name as tName, b.type, c.name as uName,
case
when a.action = 26 then 'REFERENCES'
when a.action = 178 then 'CREATE FUNCTION'
when a.action = 193 then 'SELECT'
when a.action = 195 then 'INSERT'
when a.action = 196 then 'DELETE'
when a.action = 197 then 'UPDATE'
when a.action = 198 then 'CREATE TABLE'
when a.action = 203 then 'CREATE DATABASE'
when a.action = 207 then 'CREATE VIEW'
when a.action = 222 then 'CREATE PROCEDURE'
when a.action = 224 then 'EXECUTE'
when a.action = 228 then 'BACKUP DATABASE'
when a.action = 233 then 'CREATE DEFAULT'
when a.action = 235 then 'BACKUP LOG'
when a.action = 236 then 'CREATE RULE'
end action
from sysprotects a inner join sysobjects b on a.id = b.id inner join sysusers c on a.uid = c.uid
WHERE b.type = 'U';
使用建立用户:
use Test001DB; --首先切换到相应的数据库
create login test006 with password='test006', default_database=DATABASENAME;
create user test006 for login test006 with default_schema=dbo;
获取spid强制删除连接:
select spid from sysprocesses where loginame = 'test006'--获取spid.
kill 52; --强制断开用户连接:
删除用户 :
drop user test006 --删除用户
drop login test006 --删除登陆帐户
获取当前表的字段名称:
SELECT name columnName FROM SysColumns WHERE id = Object_Id('$tableName')
获取当前服务器角色列表:
select * from sys.server_principals t where t.type = 'R';
获取当前用户的数据库服务器角色:
select g.name roleName, u.name userName
from sys.server_principals u, sys.server_principals g, sys.server_role_members m
where g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
获取当前用户的角色:
select g.name, u.name, u.sid
from sys.database_principals u, sys.database_principals g, sys.database_role_members m
where g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
赋值服务器角色和删除服务器角色:
exec sp_addsrvrolemember 'test001', 'securityadmin'
exec sp_dropsrvrolemember 'test001', 'securityadmin'
赋值角色和删除角色(不是服务器的角色):
exec sp_addrolemember 'test001', 'test_role';
exec sp_droprolemember 'test001', 'test_role';