Duplicating Permissions Of A Database Role

Jan 20, 2004

thanks for reading.

i would like to create a new database role that has exactly the same permissions as an existing database role. the combinations of permissions are complicated enough to make this a time consuming task worthy of a script of some sort. any suggestions?

i'm running SQL Server 2000 and can't find anything about copying roles unless it's through DTS from one DB to another. not sure if this is applicable to basically duplicating and renaming a role in one database.

in case you're wondering why anyone would want to do this you may not be surprised to know that it's a government thing. some policy about differentiating between 2 roles -- even though they currently have exactly the same permissions, the similarities might change in the future.

thanks in advance for your input.

View 1 Replies

Database Role Permissions

May 14, 2008

Can we assign serverrole to a database role.

For example i have a new role created as A
Now can i add A to any of the server role..

Please advice..

Thanks,
ServerTeam

View 2 Replies View Related

Database Role Permissions

Oct 12, 2007

Hello.

I've moved a database from 2000 to 2005 and in 2005 I cannot see the permissions for this Database Role that I created. In 2000 you just right-clicked, selected properies and clicked on permissions. Am I going mad here or is this not an option in 2005. If not, how do I see the permissions that have been given to the role?

The 2005 database I refer to is on standard edition SP2

Thank You

View 7 Replies View Related

Copy Permissions Of A Database Role

Jul 23, 2005

Brief description of the problem:My production server has about 50 databases and various permissions aregranted to public role on all these databases. Because of this any newuser added to any database gets unnecessary access to objects by virtueof being a member of public by default. I would like to fix this flawin the way the server is setup as below:1. Setup a new database role called NewRole on all 50 databases andcopy permissions to NewRole from public.2. Add all existing users to the new Role.3. Remove all permissions from public.Any suggestions on scripting this task are welcome.

View 3 Replies View Related

Way To Retreive Database Role Permissions

Mar 13, 2008

I want to allow administrators of the program to change the permissions of the database roles. Is there a way to retreive the the specific permissions granted an denyed to a user and a database role. For instance, a store procedure that you call, pass it the database role and you get whether SELECT is allowed on table1, or if UPDATE is denied on table2, etc.

View 3 Replies View Related

How To Script Out Permissions For A Database Role

Oct 2, 2007

Is there a way to script out a database role from SQL management studio?
I can only get a script for create and drop. I am looking for a script that shows all object permissions that the role has in a database.

Thanks.

View 3 Replies View Related

How To List Permissions For Public Role For A Database In SQL Server

Oct 17, 2007

Hello,
Does anybody have the transact SQL to find the permissions granted to the public role in a Database ?

Also looking for the SQL to find the permissions granted to the user Guest in a database ?

thanks

View 5 Replies View Related

Duplicating A Database

Dec 2, 2004

Hi

What i am trying to do is duplicate a database on my server without the data contained within it. Is there a way to do this and if so, how?

Thanks

View 12 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

Duplicating A Database In SQL 2005

Mar 14, 2008

I like the script table to.... function is sql server 2005, but I was wondering if there was a way to do this for the entire database.Like if I want to create the database and all the tables inside it without having to copy each individual table's code. Know how to do this? Thankee.

View 5 Replies View Related

Choices For Duplicating Database

Aug 22, 2006

I am developing an application that uses SQL Server 2000 for the back end. I am at the stage where some modules in the app can be tested while I finish development on some others. I run my own tests against SQL Server running on my own PC but for other people to test I have set up another server with SQL Server 2000 and have restored my database there.

My question is as follows:
I would like any changes to my database (structure and data) to be replicated on the test server's database (not necessarily immediately, but without much delay). I've heard the buzz words (log shipping, replication, etc) but would like some advice on the best way to proceed.
At the moment I don't need any data back from the test server and I don't particularly care if test data on that server is lost although these may become issues later on.

View 9 Replies View Related

Server Role Permissions

Jan 5, 2005

Where can I find the information about dbcc setcpuweight? I checked books online and couldn't find it. I also went and searched Google with the same results. Any help is greatly appreciated.

View 2 Replies View Related

Role Permissions Disappear

Feb 17, 2004

I created a new database role to give a number of users select privilege only on some tables and every day I have to go back in and add the tables back to the role. Is there something I'm missing here?

View 8 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

What Permissions Has By Default Public Role?

Mar 27, 2008

Can someone tell me what permissions has public role in sql server? can it read every table or it can just connect to a database and no more?

thanks

View 1 Replies View Related

Exporting User/Role Permissions

Jan 26, 2007

I am not a DBA so please be gentle...

I am trying to export all of the user and role permissions out of several databases for auditing purposes. I see the Users and Roles listed under the Security tree view when I log into the database, but I do not see an option to export or query the permissions. In addition, we do not have any tables that reference user permissions in our databases. So, how would one go about exporting or querying this information?

I've seen similar topics where they recommend querying sys tables to gather the info, but I don't see those tables either. Any help would be greatly appreciated.

All my thanks!

- Isaac

Edit: I should add in that I am connecting to 7 and 2k DBs using 2k5 SMS. Not sure if that makes a difference...

View 1 Replies View Related

Reseting Select Permissions For Public Role

Sep 13, 2004

Every night, there are some stored procedures that run to recreate tables so that the information in the table is updated. After the tables are droped and recreated I have to go in and check the select box under the permissions for the public role. If i don't do this users will not be able to select from theres tables.

What can I do so that users are able to select from these tables after they are created?

Would you be able to specify the select permissions for the public role in the script that creates the table or run a script that gives all those tables select permissions for the public role?

All help is appreciated.

View 2 Replies View Related

Apply Permissions To User, Role Or Group?

Jul 11, 2006

Hi guys,

I'd appreciate your thoughts on this. Not done too much DB admin. Usually I do development work and the admins to the admin.

The database is behind an API of stored procedures to manipulate the data, and views to select the data.

The database needs to be accessed remotely by multiple clients.

How best to keep the database secure?

Create a new user and login on the database which is made known to all client applications. Then grant execute permission on the stored procs and grant select on the views?

There is probably a better way than one login for all? Should I be looking at roles and groups etc? If so, how best to set that up?

A few pointers would be gratefully received!

View 5 Replies View Related

Revoking Permissions On System Tables To The Public Role.

Oct 14, 2004

We have an audit issue that is requiring me to revoke the select permissions from the public role to the system tables. Has anyone had to do this?? What problems did you experience? Are their any tables that you were not able to change the permissions on? Any help is greatly appreciated.

View 5 Replies View Related

Grant Execute To Role But Permissions Dont Show Up

Apr 5, 2008

I have a stored procedure in which at the bottom of the code, im granting execute permissions to a role I have defined. However, when I view the permissions on the procedure, the role isnt there, what could I be missing ? The procedures were all created under the default or dbo schema. I could manually give the permissions to the role, but id rather have it scripted.

help ?

View 5 Replies View Related

Script To Copy Permissions For All Objects Given To A User Or A Role

Jul 20, 2005

How would I, using a sql script, copy permissions assigned to a useror a role in one or more databases to another user or a role in theirrespective databases?Help appreciated

View 2 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

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

Granting Permission To A Database User To Alter Database Role

Sep 5, 2006

I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??

View 1 Replies View Related

Duplicating Databases

Feb 12, 2004

Ok. Here is what I need to do. I don't even know if it is possible. I have a production server and a db backup server. In a perfect world, I want to be able to place a copy of my databases (users and all) on the backup server and have it update (all changes) regularly.

I messed with DTS but it errors out because I don't have user accounts set up on the backup server. (I'm not entirely sure it does anything I want to do anyway)

Can anyone help me find a clue?
!dbnewb!

View 3 Replies View Related

New Database Role

Feb 18, 2008

Hi,

I have a database with 500+ tables and 1000+ views and equal no of stored procedures. I have a sql server login id which I have included in the following roles:

db_datareader
db_datawriter

now the issues is.. i want to revoke delete permission from certain tables. Any revoke or deny do not affect the behaviour i.e. even after successfully executing revoke/deny statements on these tables for DELETE statement.. user is able to DELETE.

pl advise the best way to revoke delete permission from a few (half dozen) tables out of the total.

regards,
D2

View 1 Replies View Related

Duplicating Db Structure Without Data

Oct 28, 2004

i have a database on one server that I want to duplicate on a new server. I do not want to copy the data only the structure.

I created some SQL scripts from the current db as I have read that is what I should do.

My question is where do I use this script on my new server to create the new tables etc..

Do i use the import feature. Where do I run my sql script?

thanks

View 2 Replies View Related

Database Role Membership

Dec 12, 2007

Hi,

I am using SQL server 2000 and when I look in the database role membership I have SuperUser as a role.

Can I have the definition of SuperUser?

View 1 Replies View Related

Database Role Properties

Dec 17, 2007

database role properties/ permissions
when i click permissions it takes forever to pop up the window.

Anyone know why and how do i make it faster.

http://www.sqlserverstudy.com

View 5 Replies View Related

Create Database Role Via T-SQL

Feb 23, 2008

I have created the functionality to dynamically create databases and am now trying to figure out how to create database roles using T-SQL.

I keep finding information about the sp_addrole stored procedure which is the first step, but how do you go about defining what permissions this role has via T-SQL?

Thanks

View 3 Replies View Related

Stored Proc Duplicating Data

Aug 15, 2006

Hi all, I have a stored proc which returns twice the result and I dontknow why. Can someone have a look at the following code?BTW, I commented the last SELECT/JOIN, cause that one doubled theresult too.CREATE procedure ent_tasks_per_user_company (@companyName as varchar(50),@resourceName as varchar(50))ASSELECTtasks.WPROJ_ID as WPROJ_ID, tasks.ENT_ProjectUniqueID asProjectUniqueID, tasks.ENT_TaskUniqueID as TaskUniqueID,tasks.TaskEnterpriseOutlineCode1ID as TaskEnterpriseOutlineCode1ID,codes.OC_NAME as OC_NAME, codes.OC_DESCRIPTION as OC_DESCRIPTION,codes.OC_CACHED_FULL_NAME as OC_CACHED_FULL_NAME,taskStd.TaskName as TaskName, taskStd.TaskResourceNames asTaskResourceNames, taskStd.TaskPercentComplete as TaskPercentCompleteINTO #myTempFROM MSP_VIEW_PROJ_TASKS_ENT as tasksINNER JOIN MSP_OUTLINE_CODES as codesON(codes.CODE_UID = tasks.TaskEnterpriseOutlineCode1IDANDcodes.OC_CACHED_FULL_NAME LIKE @companyName + '.%')INNER JOIN MSP_VIEW_PROJ_TASKS_STD as taskStdON(taskStd.WPROJ_ID = tasks.WPROJ_IDANDtaskStd.TaskUniqueID = tasks.ENT_TaskUniqueID--AND--taskStd.TaskResourceNames LIKE '%' + @resourceName + '%')WHERE (tasks.TaskEnterpriseOutlineCode1ID <-1)/*SELECT #myTemp.*, taskCode.OC_NAME as Department FROM #myTempINNER JOIN MSP_OUTLINE_CODES taskCodeON(taskCode.CODE_UID = #myTemp.TaskEnterpriseOutlineCode1ID)*/SELECT * FROM #myTemp WHERE #myTemp.TaskResourceNames LIKE '%' +@resourceName + '%'Thank you!Chris

View 3 Replies View Related

Get The Currently Logged In User's SQL Role In A Different Database

May 14, 2008

NOTE: I am talking about roles in my sql server - NOT in asp.net. I need to create a stored procedure that retrieves the roles that the currently logged in sql user has for a different database. I have the code that gets the roles for the user, but it only works if the user is in the database. I want to be in one database, and get the roles for a different database. I have tried using USE DATABASE, but this is not allowed in a store procedure.

View 10 Replies View Related

Adding A Member For Database Role

Nov 8, 2000

Hi,

I have a login 'testuser' on test server.
This login was 'db_datareader' for database called 'invent_test' on test server.
I deleted the whole database and restored the production backup as 'invent_test' database.
Now I am trying to modify login 'testuser' by adding it as 'db_datareader' for 'invent_test' again but it gives me an error that 'this user or role already exists for current database'.

I can not see 'db_datareader' or even 'invent_test' options selected in login properties for 'testuser'. Also I can not find 'testuser' in database roles
-->db_datareader member list.
Is this a bug with sql server 7.0? Or how should I give this user priviledges to read?

I dropped the login and recreated it. Server is also rebooted meanwhile so there is no possibility for cashe problem. Still same error is there.
Is there any thing that I am missing here?

I really appreciate your help. I am very frustrated now.

View 2 Replies View Related