MS SQL Server :: What Is The Difference Between Db_accessadmin ROLE And Db

Scripting Role/schema

Mar 7, 2006

I'm trying to give permissions to a particular user to execute a collection of stored procedures. From what I read, it seems the best way is to define a role and schema - I've called it db_executeusersp, and use it to grant Execute permission to a set of stored procedures. I can then create the user and simply add that role to the user and all is wonderful.

But, I'm trying to figure out how to script this thing. In SQL Server Management studio, I can script the objects, but cannot figure out how to script the connection between the schema / role and stored procedure. All I get is a script of the object definition.

Any ideas??

View 1 Replies View Related

Restrict Role To CREATE TABLE On An Assigned SCHEMA

May 9, 2008

Can anyone help me with this? The objective is to allow an application user (with db_datareader & db_datawriter database roles assigned) to be able to create tables in the assigned schema (dbo) via a new role.

-- Create User

use master

go

create login DBA with password='xx', CHECK_EXPIRATION=ON, CHECK_POLICY=ON

use AdventureWorks

go

create user dba from login DBA
alter user dba with DEFAULT_SCHEMA=dbo

go

-- Create Role

use AdventureWorks

go

create role sp_ddl_role AUTHORIZATION dbo

grant CREATE TABLE to sp_ddl_role

-- assign user to Role

use AdventureWorks

go

exec sp_addrolemember sp_ddl_role, dba

go

-- Create Table statement then run with following error
Error:

Msg 2760, Level 16, State 1, Line 1

The specified schema name "dbo" either does not exist or you do not have permission to use it.

Remedy: Grant ALTER on schema::dbo to sp_ddl_role

My problem is that I want to restrict user permissions via this role to just the CREATE TABLE and granting ALTER on a SCHEMA will open up a whole lot more permissions from a security standpoint.

Question: How do I restirct this role to just the CREATE TABLE within a SCHEMA?

View 1 Replies View Related

Sql 2005 Database Role Vs Application Role ?

May 18, 2007

Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?

thanks

View 2 Replies View Related

Can Anyone Give Me A Layman's Explanation Of The Difference Between CURRENT_TIMESTAMP And GETDATE() (if There Is A Difference)?

Oct 24, 2007

Question is in the subject.

Thanks in advance
-Jamie

View 7 Replies View Related

The 'System.Web.Security.SqlMembershipProvider' Requires A Database Schema Compatible With Schema Version '1'.

Sep 27, 2007

Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!

View 4 Replies View Related

Transferring Objects Form Schema A To Schema B In One Shot....!

May 27, 2008

I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.

I know how to do one by one...!

alter schema SchemaB transfer
SchemaA.TableA

but it will take long time...!

Thanks,

View 3 Replies View Related

Can't Make Database Role A Member Of Another Database Role In 2005.

Jan 9, 2006

In sql server 2000, I created some custom database roles called ProjectLeader and Developer. I would make these roles a member in the fixed database roles so that I would only have to add the user to the ProjectLeader or Developer role once and they would presto-magico have the security I wanted them to have with no unecessary mouse clicking. I'm not sure how to repeat this process in 2005? Management Studio doesn't seem to allow you to add a role as a member in another role. Is there a work around or solution for this?

View 1 Replies View Related

Database Schema Compatible With Schema Version '1'

Apr 12, 2008

Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?

View 2 Replies View Related

Moving Data From One DB Schema To Another DB Schema Using SSIS

May 8, 2007

Hello,

I would like to use SSIS tool to move the data from one database schema to another database schema.

For example:

Source table has

1. UserName (varchar 20) (no null)

2. Email (varchar 50) (can be null)

Destination table has

1. UserID (uniqueidentifier - GUID)

2. UserName (varchar 50) (no null)

3. EmailAddress (nvarchar 50) (can be null)

4. DateTime

Questions:

1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?

OLE DB Source, OLE DB Destination, Data Converson and .....

How do I insert Guid and Date at the same time?

2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.

Please give me some advices here.

Thanks.

View 3 Replies View Related

Application Role, App Role

Jan 23, 2008

Hi all, I am trying to connect to the database using application role. But gives an error An error has occurred while establishing a connection to the
server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)
for the given connection string Dim connstring
As String = "Data Source=Northwind;Initial
Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;"

        Dim cmd
As SqlCommand

        Dim
param As SqlParameter

        Dim
cookie As Byte()

        Dim cn As New
SqlConnection(connstring)

        If
(cn.State = ConnectionState.Closed) Then

            cn.Open()

        End If Please help..

  Thanking you, Nirmala

View 2 Replies View Related

Adding A XML Schema To XML Schema Collection

Apr 19, 2006

I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin

View 3 Replies View Related

Copy Objects From One Schema To Another Schema?

Nov 21, 2011

I am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.

In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.

View 5 Replies View Related

Adding A Database Role To A Database Role

Feb 29, 2008

Hi,

I have SQL server 2005 (Developer edition) installed and I want to add a database role to a database role. It is working on my SQL 2000 server,however, when I tried to do the same thing on SQL2005, It didn't allowed me.

When I go to the Add role and then adding members to the role, the browse screen does not allow me the choice of object type Roles. It only shows me "Users".

Can someone please help me with this and provide me some information of how / what should I set to get the Roles in object types list so I can add a role to a role.

Thanks

View 4 Replies View Related

Which Role Do I Have ?

Jul 14, 2000

Hi , I'm looking for a way that SQL-S7 tells me, what db-role I have.
I want to avoid that the server generates an error message when I'm not
authorized to access an information. So I could give the users only that
information they are authorized for.

May be there is a stored procedure or a template ?

Or is there an other way for controlled checking whether I have the rights
or not ?

Thanks for help

View 1 Replies View Related

Role Being Ignored!

Apr 14, 2004

When I assign a role to a user, the user is not actually getting the permissions defined in the role. I am forced to give the permissions directly to the user..any idea why it's ignoring the role which is assigned?

Thanks.

View 3 Replies View Related

New Role

May 2, 2008

I have set up all new Roles assigned different views, table access to each user i set up.

Whilst setting this up - i could do script to generate the script.

Is there a way to get this script after the effect - some roles i forgot to generate script.

I know im being lazy - i could write the entire script out but thought maybe there a way to get this .

Thanks

View 5 Replies View Related

DB_DDLAdmin Role In SQL

Oct 28, 2007

Hello:
I have read that giving a User the DB_DDLAdmin role in SQL might causes problems with ownership chains in the future. Since the User will have ownership to all objects created, what preventive measures can one take to help avoid any problems which might loom in the distant future due to ownership chains?
Thank you,
-H

View 1 Replies View Related

Public Role

May 13, 2002

I am new at this and we encountered a problem. Can names in the public role be deleted? We have some names that need to go - however the delete option does not high light?

I would appreciate your help..

View 8 Replies View Related

To Delete A Role...

Aug 30, 2001

Hi Everybody,
Experts........
i am using Sql server7.0
I want to Delete existing Role How Can I Delete it.
Anybody can please Tell me Immediately...
I tryied Sp_DropRole But it display's message successfully
completed but role of that name is still there...

Thanx a Lot in Advance.....(after solving this problem)
(Mohanlal)

View 1 Replies View Related

DBA Role And His Rights

Oct 30, 2001

Hi everybody,
The below I posted on SQL 2000 Forum about a week ago.
Any new thoughts................
I would like to get an input from as many people as possible on the following:
In our organization DBA is responsible for 5 servers ( currently NT 4/SQL 7)and is a part of group of a 5 people including manager and 3 developers.
DBA currently has a FULL access to every server.
In a few months we will be replacing the existing system with Windows 2000/SQL 2000.
LAN group will give to DBA only a read rights for the Windows 2000 environment, saying that the AUDITORS, both internal and external, require that. In other words, if DBA needs to run a command prompt, move files from one directory to another in Production environment, he has to fill the request to LAN, so LAN group would do that.
So I guess the main question(s) is:
What is the degree of involmment of DBA with Operating system?
Is DBA suppose to be an NT administrator ( I dont think so, since DBA has a lot of other thing to do?
If DBA accidently makes an unwanted changes to the Operating System, who should be blamed for ( not personally, but in more general terms) and would it be an extra argument to take write rights away from a DBA?
What auditors saying about that?
Thanks a lot in advance,
Andrei

View 3 Replies View Related

What Role Should Be Given To The End User?

Oct 25, 2000

Hi Everybody,

The end users are using VB Applications, there they will be entering datas. Those datas will be stored in the SMS Database.

My Problem is through which Roles (Fixed Server Roles or Database Roles) I should attach these end users. If it is a Fixed Server Roles, Other than sysadmin role in which role I should attach this end user. Like that other than DB_Owner in which role I should attach these end users to the Database Roles.

Can anyone guide me please.

thanks,
Srinivasan.

View 2 Replies View Related

Server Role

Jan 10, 2003

Hi!
What server role should be assign for individual to create and execute DTS packages.
Thank you,
Elena.

View 3 Replies View Related

Db_owner Role

Mar 2, 2007

Hi All,

Question:

Why would the user other than sa need the db_owner rights?

View 3 Replies View Related

DBA Role And His Rights

Oct 24, 2001

Hi everybody,
I would like to get an input from as many people as possible on the following:
In our organization DBA is responsible for 5 servers ( currently NT 4/SQL 7)and is a part of group of a 5 people including manager and 3 developers.
DBA currently has a FULL access to every server.
In a few months we will be replacing the existing system with Windows 2000/SQL 2000.
LAN group will give to DBA only a read rights for the Windows 2000 environment, saying that the AUDITORS, both internal and external, require that. In other words, if DBA needs to run a command prompt, move files from one directory to another in Production environment, he has to fill the request to LAN, so LAN group would do that.
So I guess the main question(s) is:
What is the degree of involmment of DBA with Operating system?
Is DBA suppose to be an NT administrator ( I dont think so, since DBA has a lot of other thing to do?
If DBA accidently makes an unwanted changes to the Operating System, who should be blamed for ( not personally, but in more general terms) and would it be an extra argument to take write rights away from a DBA?
What auditors saying about that?
Thanks a lot in advance,
Andrei

View 1 Replies View Related

Application Role

Aug 18, 2004

I am confused . What is considered an application and how SQL would know ?

If I have a web site accessing SQL VIA IIS will SQL Server treat it as an application ? How about MS Excel ?

Also , If I was to use the application of Power builder , using app role , how do control which user can use the app ?

Thanks

View 1 Replies View Related

Processadmin Role

Oct 5, 2005

I'm trying to allow my developers the ability to modify/execute their jobs and dts packages in production....without giving away the security farm so to speak.

Is the processadmin role a possibility?

BOL and the net only seems to say this role allows user to "manage process"...duh.

Your thoughts and advice would be great appreciated.

View 3 Replies View Related

Sysadmin Role

Jan 30, 2006

Hi All,

Is it possible to give a user a sysadmin role and then deny some of the privileges?

I am a junior dba, I should be able to view only everything that the sysadmin can see, i.e. db properties, logins, packages, jobs etc.

View 5 Replies View Related

SysAdmin Role

Feb 10, 2004

Hi
I'm new to SQL Server. I have created a databased named Sample and
I hae created the user with login named "Sman".
SMan owns some tables and sp's. I'm able to access the tables and SP's when I was logged in as Sman in Query analyser. I have given a Sysadmin privilege to Sman then I'm not able to access the tables and sp's when i try to login with Sman.

ie, Select * From tabl1 is not working But
Select * From Sman.tabl1 is working. I dont know Why is it so?
Can any one help me!

Thanks in Advance

View 1 Replies View Related

Sa And Sso Role Separation

May 11, 2004

Hi all

Do we have provision of separating sa and sso role in SQL server as we have in sybase? ( In such a case, sa shouldn't have any control on creating/modifying users/logins)

Thx

Wilson

View 7 Replies View Related

Role Permissions

Apr 11, 2008

Hi Guys,

How to copy one user role permissions to another role in same databse using stored procedue(sp)? Can any one help how to write sp.

Thanks.

View 2 Replies View Related

Help With ROLE Creation

Jun 16, 2008

I am a promotional DBA and need help with some code a developer wrote that errors out.
He is trying to create a role and to my knowledge there is nothing wrong with the syntax but it gives an incorrect syntax error.

CREATE ROLE 'PPS' IDENTIFIED BY 'stressboy';

error: Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'ROLE'.

we tried it with ' ' & "" and without either. I'm sure this is an easy one for you all. Thanks.

View 1 Replies View Related

Server Role

Mar 24, 2008

what views table that I can use to view the server roles on the instance?

thanks,

View 1 Replies View Related

What Is The Difference Between Db_accessadmin ROLE And Db_accessadmin SCHEMA