sql server 面试题（7） - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

sql server 面试题（7）

sql server 面试题（7）# Database - 数据库

z*y2008-11-04 08:11

1 楼

问题来了！
这个是考察DBA的基本管理技能，主要看ＤＢＡ是不是头脑清楚。大概是去年的电面。
sql server 2005 only
1. As DBA, you need to drop one login from your db instance. Not only drop
the login, you also need to drop associated user with this login from all
databases.
Currently this user might be active in several databases.
what would you do?
Use script to give a solution.
2是关于security controls。
2. Project architect need to view the definitions of two stored procedures
on production server. Currently this user only has db_datareader

c*d2008-11-04 08:11

2 楼

1. logins and users
the following sql gives you a little hint
select
'select master..syslogins.name as login_name,'
+ name +
'..sysusers.name as user_name
from master..syslogins inner join sysusers
on master..syslogins.sid = sysusers.sid'
from
master..sysdatabases

【在 z***y 的大作中提到】

: 问题来了！
: 这个是考察DBA的基本管理技能，主要看ＤＢＡ是不是头脑清楚。大概是去年的电面。
: sql server 2005 only
: 1. As DBA, you need to drop one login from your db instance. Not only drop
: the login, you also need to drop associated user with this login from all
: databases.
: Currently this user might be active in several databases.
: what would you do?
: Use script to give a solution.
: 2是关于security controls。

p*l2008-11-04 08:11

3 楼

1. sp_helplogins, sp_dropalias, sp_droplogin, sp_dropuser
2. Actually they already can view the source as datareader.

【在 z***y 的大作中提到】

z*y2008-11-04 08:11

4 楼

1. Not complete...let's use new feature of SQL Server 2005 instead. Also,
can you drop user when the user has active connection? If you can, why? If
you cannot, how to proceed?
2. Are you sure?

【在 p********l 的大作中提到】

: 1. sp_helplogins, sp_dropalias, sp_droplogin, sp_dropuser
: 2. Actually they already can view the source as datareader.

B*g2008-11-04 08:11

5 楼

1最后两句说反了

【在 z***y 的大作中提到】

: 1. Not complete...let's use new feature of SQL Server 2005 instead. Also,
: can you drop user when the user has active connection? If you can, why? If
: you cannot, how to proceed?
: 2. Are you sure?

S*k2008-11-04 08:11

6 楼

1.
Get mapped users of a login
sp_helplogins
Get principal_id of a database user
select * from sys.database_principals
Get schema owned by the database user
select * from sys.schemas
change schema ownership if necessary
ALTER authorization on schema::schema_name to other_principal_name
Drop database user
Drop user user_name
Get session id of connections
select * from sys.dm_exec_sessions where login_name = 'login_name'
Kill session_id
DROP Login login_name
2.
Grant VIEW Definition ON OBJECT::Pro

z*y2008-11-04 08:11

7 楼

多谢回复！窃以为这是最好的回答。

【在 S***k 的大作中提到】

: 1.
: Get mapped users of a login
: sp_helplogins
: Get principal_id of a database user
: select * from sys.database_principals
: Get schema owned by the database user
: select * from sys.schemas
: change schema ownership if necessary
: ALTER authorization on schema::schema_name to other_principal_name
: Drop database user

p*l2008-11-04 08:11

8 楼

Oops, I forgot this is for SQL 2005. There are a lot of differece between 2k
and 2k5.