What Is The Difference Between Db_accessadmin ROLE And Db_accessadmin SCHEMA
May 16, 2008
The question is pretty simple so I don't see why it is so difficult to understand. I do hope somebody answers with something useful.
The problem is that SQL Server 2005 defines several default ROLES such as db_accessadmin, db_backupoperator, db_datareader, etc. and at the same time it also defines SCHEMAS with the same name (db_accessadmin, db_backupoperator, db_datareader).
I have no idea what MS developers where thinking but that is utterly confusing. When creating a role using Management studio one should be able to "grant" these default ROLES to the role one is creating (or the user), instead one is presented with schemas.
For example, when creating a new database role if you select the db_datareader SCHEMA (because ROLES are not presented...) then you can select the various permissions (CONTROL, SELECT, ALTER, etc.). That is ok if the object is a user defined schema but totally confusing for default db_ schemas. Why would one grant other than SELECT to db_datareader schema?
I am still confused about this and is hindering the implementation of a proper database role structure.
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.
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?
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) ?
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!
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?
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?
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.
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;"
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
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.
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 ?
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?
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
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?
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)
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
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.
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
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.
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!
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)
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.
