MSDE - Grant Permission?

Hi all,
If I would like to create a user with a db_owner permission & some permissions such as sp_addlogin, sp_adduser etc.
How can I do? Would you mind to give me some examples?
Many thanks.Hi all,

If I would like to create a user with a db_owner permission & some permissions such as sp_addlogin, sp_adduser etc.

How can I do? Would you mind to give me some examples?

Many thanks.

Check sp_addrolemember , Grant, sp_addlogin in Bol its been explained very well with good examples.|||Yes, I used sp_addrolemember, grant , etc.

The following are my steps:
1. use "sa" login to "master"
2. exec sp_addlogin "abc", "abc", "master", null, null, null
go
3. exec sp_adduser "abc", "abc1", "db_owner"
4. exec sp_addrolemember "db_accessadmin" , "abc1"
go
5. exec sp_addrolemember "db_securityadmin" , "abc1"
go
6. exec sp_addrolemember "db_ddladmin" , "abc1"
go
7. exec sp_addrolemember "db_backupoperator" , "abc1"
go
8. exec sp_addrolemember "db_datareader" , "abc1"
go
9. exec sp_addrolemember "db_datawriter" , "abc1"
go
9.5. grant all on sp_addlogin to abc1
go
10. use "abc" login into "master"
11. exec sp_addlogin "bb", "bbpassword", "master", null, null, null
go

Msg 15247, Level 16, State 1, Server MIS-TOMMY\AIAB, Procedure sp_addlogin, Line
17
User does not have permission to perform this action.

How can I solve this?

Many thanks.|||Yes, I used sp_addrolemember, grant , etc.

The following are my steps:
1. use "sa" login to "master"
2. exec sp_addlogin "abc", "abc", "master", null, null, null
go
3. exec sp_adduser "abc", "abc1", "db_owner"
4. exec sp_addrolemember "db_accessadmin" , "abc1"
go
5. exec sp_addrolemember "db_securityadmin" , "abc1"
go
6. exec sp_addrolemember "db_ddladmin" , "abc1"
go
7. exec sp_addrolemember "db_backupoperator" , "abc1"
go
8. exec sp_addrolemember "db_datareader" , "abc1"
go
9. exec sp_addrolemember "db_datawriter" , "abc1"
go
9.5. grant all on sp_addlogin to abc1
go
10. use "abc" login into "master"
11. exec sp_addlogin "bb", "bbpassword", "master", null, null, null
go

Msg 15247, Level 16, State 1, Server MIS-TOMMY\AIAB, Procedure sp_addlogin, Line
17
User does not have permission to perform this action.

How can I solve this?

Many thanks.

Just add two lines before line 10
exec sp_addsrvrolemember N'abc', sysadmin
go
EXEC sp_grantdbaccess N'abc', N'abc1'
go

I hope now it would be ok.
Joydeep :cool:

MSDB Table User Permissions

Just out of curiosity, could someone point me towards a listing of the user permissions for the MSDB table? I have looked through BOL and on the internet and cannot find a good listing. An example would be something like...
dts_admin: <dts_admin description>

Thanks in advance.
-Kyle

Unfortunately msdb is being used by a number of different SQL Server services (built on top of SQL Server Engine, but shipped as part of SQL Server itself), and each type of service documents their own set of roles and usage (i.e. http://msdn2.microsoft.com/en-us/library/ms141053.aspx can help you with dts* principals), but there is no centralized documentation.

My recommendation right now would be to use msdn to search for any out of the box principal you want to know more about, and typically you will find the documentation for all related principals as well.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

MSDB Permissions Error

Hi,
We've just upgraded a server to a new box and copied all of the settings
from the existing box. We are running a VB client front end, with COM+ as
middle tier and SQL Server 2000 as BE.
The COM+ account is running under a windows domain account (which is also in
the local server administrators group), but all connections to the SQL Serve
r
database are under an SQL login 'AppUser'.
We are just testing the new configuration but are getting all sorts of
errors in the VB client FE. First of all, the FE tries to submit a job and
received error
'SELECT permission denied on msdb.sysjobs'
Does anyone know the cause of this error. As far as I can see, all of the
permissions on our new server are the same as the previous server' The logi
n
AppUser seems to be in all the correct groups.
(Just for testing purposes, I've made 'AppUser' part of the db_owner
database role in both these databases which of course fixes the above errors
.
But I do not want to leave it like this!!)
Any advice appreciated
ThanksHas the guest account been disabled in the msdb database. In the msdb
database, in Query Analyzer run the following command :
Select * from sysusers where name = 'guest'
Check the hasdbaccess column. It should be 1. If not run the following in
the msdb database:
sp_grantdbaccess 'guest'
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi Rand,
Thanks for response, i ran the SQL & 'hasdbaccess' column is flagged as 1.
Any other possibilities to check...?
Thanks
Dylan|||Check the permissions of the public role in the database.
Rand
This posting is provided "as is" with no warranties and confers no rights.