-- 查询当前数据库用户账户所属的数据库角色 select user_name(groupuid) from sysmembers where memberuid=user_id()-- 查询当前数据库用户账户的权限 select object_name(id) obj, (case [action] when 26 then 'REFERENCES' when 178 then 'CREATE FUNCTION' when 193 then 'SELECT' when 195 then 'INSERT' when 196 then 'DELETE' when 197 then 'UPDATE' when 198 then 'CREATE TABLE' when 203 then 'CREATE DATABASE' when 207 then 'CREATE VIEW' when 222 then 'CREATE PROCEDURE' when 224 then 'EXECUTE' when 228 then 'BACKUP DATABASE' when 233 then 'CREATE DEFAULT' when 235 then 'BACKUP LOG' when 236 then 'CREATE RULE' end) perm from sysprotects where [uid]=user_id()
这个?? --sql中所有登录 select name,* from master.dbo.sysxlogins where srvid is null
--当前库的所有用户 select name,* from sysusers where islogin=1
-- 登陆帐户的当前数据库名 select loginame,db_name(dbid) from sys.sysprocesses where spid>50;
study.....
这个问题以前写数据库开发工具时也遇到过,也在CSDN和微软技术社区上上问过,结果自己想了两天想出来了,其实很简单,就一句: sp_msforeachdb "select '?'"写成存储过程就是: create proc sp_EnumDatabases as begin create table ##tt1(dbname varchar(500)) exec sp_msforeachdb "insert into ##tt1 select '?' dbname " select * from ##tt1 DROP TABLE ##tt1 end
写成存储过程就是: create proc sp_EnumDatabases as begin create table ##tt1(dbname varchar(500)) exec sp_msfo……
-- 查询当前数据库用户账户的权限 select object_name(id) obj, (case [action] when 26 then 'REFERENCES' when 178 then 'CREATE FUNCTION' when 193 then 'SELECT' when 195 then 'INSERT' when 196 then 'DELETE' when 197 then 'UPDATE' when 198 then 'CREATE TABLE' when 203 then 'CREATE DATABASE' when 207 then 'CREATE VIEW' when 222 then 'CREATE PROCEDURE' when 224 then 'EXECUTE' when 228 then 'BACKUP DATABASE' when 233 then 'CREATE DEFAULT' when 235 then 'BACKUP LOG' when 236 then 'CREATE RULE' end) perm from sysprotects where [uid]=user_id()-------------------------------------- 学习了!!!
sp_who
查看SQL Server数据库里的活动用户和进程的信息
sp_who 'active'
查看所有数据库用户所属的角色信息
sp_helpsrvrolemember
select user_name(groupuid) from sysmembers where memberuid=user_id()-- 查询当前数据库用户账户的权限
select object_name(id) obj,
(case [action] when 26 then 'REFERENCES'
when 178 then 'CREATE FUNCTION'
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 198 then 'CREATE TABLE'
when 203 then 'CREATE DATABASE'
when 207 then 'CREATE VIEW'
when 222 then 'CREATE PROCEDURE'
when 224 then 'EXECUTE'
when 228 then 'BACKUP DATABASE'
when 233 then 'CREATE DEFAULT'
when 235 then 'BACKUP LOG'
when 236 then 'CREATE RULE' end) perm
from sysprotects where [uid]=user_id()
--sql中所有登录
select name,*
from master.dbo.sysxlogins
where srvid is null
--当前库的所有用户
select name,*
from sysusers
where islogin=1
-- 登陆帐户的当前数据库名
select loginame,db_name(dbid) from sys.sysprocesses where spid>50;
sp_msforeachdb "select '?'"写成存储过程就是:
create proc sp_EnumDatabases
as
begin
create table ##tt1(dbname varchar(500))
exec sp_msforeachdb "insert into ##tt1 select '?' dbname "
select * from ##tt1
DROP TABLE ##tt1
end
create proc sp_EnumDatabases
as
begin
create table ##tt1(dbname varchar(500))
exec sp_msfo……
风暴A型主机 15元/月1G全能空间,不限IIS与CPU、流量 100M 数据库+100M邮局 智能双线标准1型主机(支持10个子目录绑定) 19元/月1G全能空间,1M带宽,不限IIS与CPU, 赠送200M邮局 自定义伪静态 标准1S型主机(支持10个子目录绑定) 29元/月1G全能空间,1M带宽 500M 数据库,10用户10G邮局 自定义伪静态 LINUX自由型(支持5个子目录绑定) 29元/月1G全能空间,1M带宽 500M 数据库,10用户10G邮局 自定义伪静态
飞网中国---高速空间,全能空间,论坛空间,网店空间
http://www.zhhcz.com
aion gold
aion kina
aion kinah
aion power leveling
buy aion gold
buy aion kinah
0)Query[code=SQL]
DECLARE @RoleName varchar(50)
DECLARE @CMD varchar(1000)CREATE TABLE #UserRoles
(DatabaseName varchar(50),
Role varchar(50))CREATE TABLE #RoleMember
(DBRole varchar(100),
MemberName varchar(100),
MemberSid varbinary(2048))
SET @CMD = 'use ?TRUNCATE TABLE #RoleMemberINSERT INTO #RoleMember
EXEC sp_helprolemember INSERT INTO #UserRoles
(DatabaseName, Role)
SELECT DB_NAME(), DBROLE
FROM #RoleMember'EXEC sp_MSForEachDB @CMDSELECT * FROM #UserRoles1)ResultDatabaseName Role
-------------------------------------------------- --------------------------------------------------
master db_owner
tempdb db_owner
model db_owner
msdb db_owner
msdb SQLAgentReaderRole
msdb SQLAgentUserRole
OSMS db_owner
Test_Temp db_owner
Test_Temp db_owner
TestShen db_datareader
TestShen db_denydatawriter
TestShen db_owner
AdventureWorks db_owner
Testshenliang db_owner
Import db_owner
Test_shenliang db_owner(16 row(s) affected)[/code]
select object_name(id) obj,
(case [action] when 26 then 'REFERENCES'
when 178 then 'CREATE FUNCTION'
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 198 then 'CREATE TABLE'
when 203 then 'CREATE DATABASE'
when 207 then 'CREATE VIEW'
when 222 then 'CREATE PROCEDURE'
when 224 then 'EXECUTE'
when 228 then 'BACKUP DATABASE'
when 233 then 'CREATE DEFAULT'
when 235 then 'BACKUP LOG'
when 236 then 'CREATE RULE' end) perm
from sysprotects where [uid]=user_id()--------------------------------------
学习了!!!
'database_name'
数据库的名称,用户希望获取有关该数据库的访问信息。database_name 的数据类型为 sysname。返回类型
int 备注
如果用户可以访问该数据库,则 HAS_DBACCESS 返回 1。如果用户不能访问该数据库,则返回 0。如果该数据库名无效,则返回 NULL。权限