首页新闻找找看学习计划

SQL server 存储过程创建数据库失败

0
悬赏园豆:50 [已解决问题] 解决于 2015-12-10 14:04

手动 用  appadmin 创建数据库成功  

但是存储过程中创建过程中却出错:

消息 262,级别 14,状态 1,第 1 行
在数据库 'master' 中拒绝了 CREATE DATABASE 权限。

存储过程创建语句:


if (exists (select * from sys.objects where name = 'proc_create_database'))
drop proc proc_create_database
go
create proc proc_create_database(
@Name nvarchar(50),
@Path nvarchar(255)
)
WITH EXECUTE AS N'appadmin'
as
declare @sql nvarchar(max);
set @sql = 'create database "<NAME>" on (name="<NAME>",filename="<PATH><NAME>.mdf",size=5mb,maxsize=unlimited,filegrowth=1) log on (name="<NAME>_log",filename="<PATH><NAME>_log.ldf",size=5mb,maxsize=unlimited,filegrowth=1)'
if exists(select * from sys.sysdatabases where name= @Name)
begin
SELECT system_user
print '删除存在库'
exec('use master drop database "'+ @name +'"')
print '建库'
set @sql = REPLACE(@sql,'<NAME>',@Name)
set @sql = REPLACE(@sql,'<PATH>',@Path)
exec(@sql)
end
else
begin
SELECT system_user
print '建库'
set @sql = REPLACE(@sql,'<NAME>',@Name)
set @sql = REPLACE(@sql,'<PATH>',@Path)
exec(@sql)
end

此存储过程没有在master库内  在另一个库内

 

_Arnold的主页 _Arnold | 小虾三级 | 园豆:635
提问于:2015-12-09 11:26
< >
分享
最佳答案
1

使用sa账户执行吧。摆明了是权限不足。

收获园豆:40
幻天芒 | 高人七级 |园豆:36522 | 2015-12-10 08:45

恩  我也明白是权限不足,

但是我不明白 appadmin  直接执行 Create table 是成功的 ,在存储过程中也是用的appadmin账户执行 ,却报错。。。

这个存储过程的作用是:

低权限用户本身不具备建库的权限 ,那么就赋予该用户执行此存储过程的权限,而存储过程又以高权限的用户身份去执行create table 命令,本身这个想法应该没有问题。

参考文章链接:http://blog.csdn.net/zjcxc/article/details/2935298 

_Arnold | 园豆:635 (小虾三级) | 2015-12-10 08:52

@xiaoxiao刀: appadmin是哪个库的管理员账号?你创建库,需要用到master的管理员账号,而不是你创建的库的管理员账号。

幻天芒 | 园豆:36522 (高人七级) | 2015-12-10 09:09

@幻天芒: 

谢谢关注,是我没有把问题描述清楚:

appadmin  用户在master创建并赋予了dbo权限。

_Arnold | 园豆:635 (小虾三级) | 2015-12-10 09:43

@xiaoxiao刀: 尝试下如下方案嘛:http://blog.csdn.net/u010800530/article/details/12093999

幻天芒 | 园豆:36522 (高人七级) | 2015-12-10 09:49

@幻天芒:

 

试过了,还是不行呀...    

http://mahaifang86.blog.163.com/blog/static/122125964200981210419941/

这篇文章说存储过程不能含有创建数据库的语句,看来这个思路本身就有问题。 

_Arnold | 园豆:635 (小虾三级) | 2015-12-10 10:04

@xiaoxiao刀: 还真没尝试过,不过也是有这种可能性的。

幻天芒 | 园豆:36522 (高人七级) | 2015-12-10 10:07

@幻天芒: 

谢谢关注,问题再摆1天如果没人来答的话,分就给你吧  。:)

_Arnold | 园豆:635 (小虾三级) | 2015-12-10 10:12

@xiaoxiao刀: 咨询了公司的dba,而且我也测试过了,存储过程是可以创建database的。

create proc db_create
as
create database [test2] on(name='test2', filename='D:\DbFiles\sss.mdf')
log on(name='test2_log', filename='D:\DbFiles\sss_log.ldf')

exec db_create

之后,我新建了一个用户,然后勾选了所有的服务器角色,就可以用新建的用户创建数据库了。

建议你使用一个最简化的proc来测试一下。

幻天芒 | 园豆:36522 (高人七级) | 2015-12-10 10:48

@幻天芒: 

恩   看来问题不是不能 create database

背景是这样的:

现在公司在做的应用程序有动态创建数据库的需求,但是处于安全考虑又不想直接让Web应用程序使用sa账户,于是有了以上的思路:  就是用sa账户创建一个存储过程,并使其的运行用户为  sa 或 其他管理权限(appadmin) ,只给Web应用程序的账户(此处为test账户)执行该存储过程的权限,从而做到权限的分离,这个思路也是这篇文章提到的:

http://blog.csdn.net/zjcxc/article/details/2935298 

这个方法中的  select 方法测试过了没有问题。

我把语句化简再测下  。

_Arnold | 园豆:635 (小虾三级) | 2015-12-10 11:10

@xiaoxiao刀: 你的test账户应该无法切换到appadmin执行吧?另外你这样的安全设计没啥意义。既然都能执行这样的存储过程了,说明你的Web的权限也很高。按照你的配置,appadmin是可以执行该存储过程的。但是就不知道你连接sql,用的是哪个账户了。

幻天芒 | 园豆:36522 (高人七级) | 2015-12-10 13:16

@幻天芒:

1.WebApp连接数据库用的账户问题: 

是这样的  WebApp  连接数据库用的是  test 账户    appadmin 设置成内置账户不允许外部访问。也就是说只有运行该存储过程时才会用到  appadmin   其他的操作都是用  test  账户来完成的  

 

2.test执行存储过程中切换appadmin问题:

你看我写的存储过程语句中有这样一句:

WITH EXECUTE AS N'appadmin'

这个是经过测试可行的。

 

我又测试了下,应该是 appadmin 账户的权限设置的还是不对,我把这个appadmin 换成  master 库中的 dbo 账户后测试  ,该存储过程在不存在重名的数据库的情况下,创建数据库成功,但是用  该dbo  去删除 刚刚创建的数据库是失败的。

还需要继续测试。。。 

_Arnold | 园豆:635 (小虾三级) | 2015-12-10 13:41

@幻天芒: 

恩  你说的貌似没错,  test 如果要运行 proc_create_table 存储过程时,必须要有master 库的执行权限,这样test 本身就具有很高的权限  ,   不可以完全做到权限隔离,还是想其他办法把  。

_Arnold | 园豆:635 (小虾三级) | 2015-12-10 14:03

@xiaoxiao刀: 学习了,WITH EXECUTE AS N'appadmin'居然还能用这种方式切换用户。再检查下appadmin的权限吧。勾选了服务器角色的sysadmin,就等价于sa权限了。

幻天芒 | 园豆:36522 (高人七级) | 2015-12-10 17:03
其他回答(1)
0

没有创建数据库的权限,点击该数据库-安全性-用户-双击你的用户-给该用户授予db_owner权限就可以了

收获园豆:10
澈澈 | 园豆:435 (菜鸟二级) | 2015-12-09 12:29

你好,已经设置过 db_owner 了  不然直接用appadmin  create  database 也不会成功    

支持(0) 反对(0) _Arnold | 园豆:635 (小虾三级) | 2015-12-09 14:05
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册