Determining Database Role Of Querying User

Dec 7, 2007

I know that I can use:

IS_MEMBER('MyRoleName') = 1

to determine if a user is a member of a predetermined role, but how do I ask what the role name of the querying user is instead? Basically I want to do something like:

SELECT * FROM CityTable WHERE City IN (SELECT City FROM CitySecurity WHERE SelectGroup = ROLE_NAME)

I'm doing this because the "CitySecurity" table contains security information from another system that has been imported into SQL Server, where the 'SelectGroup', as part of process, will be named the same as the SQL Server role.

Thanks,
Kayda

View 4 Replies


ADVERTISEMENT

Adding A User/role To All User Database

Jan 23, 2001

Hi everyone,

I try to add a db_role or a user to all my databases with one script. Although parsing doesn't report any problem I get a Syntax error during execution.
I first select the database names into a #temp table which has two columns, ID and dbname.
After that I use the following code:

DECLARE @Count smallint
declare @dbVarchar(20)

SET @Count = 1

WHILE (@Count <=(SELECT MAX(ID) FROM #temp))

BEGIN

Exec ("USE @db")

EXEC sp_adduser 'test'
EXEC sp_addrolemember 'my_role', 'testrole'


SET @COUNT = @COUNT + 1
SET @db = (SELECT dbname FROM #temp WHERE ID = @Count)
END

It seems that the "Use @db" part has no value for the variable @db.
Does anybody knows how to solve this?

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

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

Database Role/User Query

Sep 21, 2006

Anyone have a tsql query that will give me a listing of database roles and their users already put together?

View 1 Replies View Related

Create User - Permit In Database Role

Jan 7, 2004

Hi,
I hv an application which is using ASP.net. The connectionstring in web.config is
<appSettings>
<add key = "constring" value = "Initial Catalog=mydatabase;Data Source=mypc-pc;User ID=User1; Password=password1"/>
</appSettings>"

Then, i hv created a user in SQL Server 2000 which is User1. What should i put for the database role? db_owner or just db_datareader and db_datawriter?

pls help.

Thnx

View 4 Replies View Related

User Or Role Already Exists In The Current Database

Oct 9, 2000

Hi!
I've restored a backup from server A in server B. In server A I used to have a db_owner, called sitebase. After restoring the backup in server B, I created the user sitebase again and tried to grant him db_owner through Server Manager but I got the error: user or role already exists in the current database.
How can I avoid this situation and grant db_owner to him?
Thanks,
Fábio

View 1 Replies View Related

User Not A Choice When Trying To Add To Db_owner Role For Database

Mar 14, 2008


How do I get a particular user to be a choice under the db_owner role for a particular database?
The user is listed under logins and even shows to be the db_owner for the database under the database access tab of the login properties. This is SQL 2000.
Thanks,
David P.

View 1 Replies View Related

Will User's Permission Be Overriden By Database Role?

Dec 26, 2006

in SQL server 2005, Database User's permission will be overriden by the database Role's permission or ottherwise? For example, a userA is owner of table AA so it has all permisions on table AA but the user is a member of GroupB but group B has no permission to access to Table AA. What happen on User A?. has it permission to access to table BB or not? How can I find document or example about this?
Please help me, thanks so much

View 3 Replies View Related

Server And Database Role Requests For Normal User

Jun 5, 2007

Hi,

I want to know how to assign a normal user to take backup,Restore and attach and detach the database in sqlserver2000.

Please can anyone help me to find out which server role or database role will suites my requirements.

Regards
Md S.Hassan

View 1 Replies View Related

How To Drop A User Defined Database Role In 2005?

Mar 9, 2007

Using Studio, I created a user defined database role but I can not delete it because



"TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for DatabaseRole 'test1'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

I am quite annoyed because the "owned schema" is db_owner, which can not be unselected. Quite an innovation. How do I drop this relationship?

View 3 Replies View Related

How Can One User View Other Users That Belong To A Database Role?

Nov 15, 2006

After upgrading my database from SQL2000 to SQL2005 I have noticed a change in behavior of sp_helprolemember.

In SQL2000 I could connect as 'user1' and use sp_helprolemember to find all users that belong to a certain role.

In SQL2005 sp_helprolemember seems to only show me the roles that connected user belongs to. For example, if I connect as 'user1' I only see the roles that 'user1' belongs to.

Any advice on how to duplicate the behavior from SQL2000?

View 8 Replies View Related

How To Fix &#34;Error 15023: User Or Role &#39;%&#34; Already Exists In The Current Database

Sep 20, 2001

I used a backup copy of our production DB (residing in our prod machine) to do a database RESTORE to our test DB (residing in our test machine). This step was successful.
However when I tried to access the test DB via Peoplesoft application, I am unable to logon. Only then did I notice that all the users, with the exception of "sa", were gone.
When I attempted to add a user via Enterprise Manager's Action - Add Database User, I get the message, "Error 15023: User or role '%' already exists in the current database.
What's the best way to fix this without resorting to copying the source server's master database (If i do this, I risk clobbering some other DB's that are present in the target server but not in source server)?
Any help you can provide will be greatly appreciated!!!

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

Programmatically Adding A User To The System User Role

Dec 27, 2006

We have been working on an application that will be using a forms-authenticated report server (RS2005) as a reporting back-end. Using the reporting services web service I have been able to assign permissions to objects in reporting services no problem. The issue is that each user needs to be added to the System User role to be able to use the report builder properly. I can't seem to find a way to do this programmatically. Any idea?

View 1 Replies View Related

How To: Determine If Current Windows User Has Login Access, Database Access And If They Are A Member Of A Specific DB Role.

Mar 25, 2008


I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.

1 Has the current user been granted login access to the trusted SQL Server?

2 Has the current user been granted access to a specific database?

3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?

Thanks,
Sean

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

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

Determining Database Size

Oct 31, 2001

Is there a system stored procedure that I can execute that will return the actual size of the database you are working with? Any information is appreciated.

View 1 Replies View Related

Determining Database Size

May 9, 2008

Forgive the easy question but I'm afraid it might be also a trick question and I'd like to hear the experts' opinion. I am using SQL Server 2005 Express edition and I know the limitation is 4GB per database. So far none of my users is anywhere near the limit but I have to be prepared for when that day finally comes. As it stands, they use a single database through a program so I have full control over it. There are no fancy backup programs on the system so no fancy recovery models and automatic shrinking can be done - data is only inserted in that database.

My question is simply how can I determine programmatically (I use ADO.Net but it can execute SQL commands just fine) the size of the database as it relates to the limitation? That is, I don't know whether it is the amount of data stored - with or without overhead, or it is simply the size of the *.mdf file (maybe together with the *.ldf file), or whether the 4GB is 4 billion bytes or 2^32 bytes - I just want the same method that the SQL Server is using so that, for example, I can bring up a warning at 90% full and lock out the user at 99% full.

I'd appreciate any insights.

Kamen

View 3 Replies View Related

Determining Is A Database Is A Subscriber

Feb 1, 2006

Hi,



can anyone tell me if there is a way to determine with SMO or RMO if a database is a subscriber when using merge replication. If only have the Server and database at this point too!!

I want to provide a small app that creates a merge publication but only if the database isn't a subscriber.

Thanks for your help

Graham

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

User/Role Check

Jun 5, 2000

Can anyone out there help me write a sp to determine if a user has a certain role?

I'm trying to use "sp_helpuser @UserName".

Can I declare a cursor with "EXEC sp_helpuser @UserName"? I'm not having success with this.

What about SELECT [GroupName] FROM EXEC sp_helpuser @UserName WHERE [GroupName] = @GroupName? Again, syntax error.

Is there already a better way that someone knows of?

View 1 Replies View Related

User With Server Role

Feb 14, 2008

I have a user with DBCREATOR Server role only. That user is able to create database but create table permission denied.
how would I set permission on this user, so that this user can create databases and automatically becomes the DBOWNER of that database and can do any action on that database.

Thanks,

View 6 Replies View Related

Cannot Use The Reserved User Or Role Name 'db_datareader'.

Oct 21, 2005

Hi, I have got a problem. When I try to access my database table Users, I get the following error:

SELECT permission denied on object 'Users', database 'Users', owner 'dbo'.

So
I tried to grand this select command in MS Web Data Administration, but
it doesnt work. When I try to grand db_datareader role to dbo, I get
the following error

[Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot use the reserved user or role name 'db_datareader'.

Does someone have an idea where could be a problem?

View 1 Replies View Related

Is It Possible To &#34;extend&#34; Role/user Rights Using SP...

Jun 8, 2001

Hi All,

I'm rather new to the MS SQL Server development in general and especially to its data security architecture and features - I'd like to know if it is possible for end-user to retrieve/update(!?) the data using a SP which executes on a table for which she/he doesn't have any privileges.

TIA,
Shamil

View 4 Replies View Related

Retrieving User/Role Privileges - How ?

Jul 7, 2004

Hi,

I need to read and subsequently modify the privileges (rights) of a certain SQL Server user / role from within a Visual Basic Program.

Modifying seems to be easy using standard statements like GRANT/REVOKE. But what about reading all the rights a user has ?

I have researched SQL-DMO, but didn't find what I'm looking for.

Any idea ?

Mike

View 1 Replies View Related

Retrieving User Defined Role Name

May 4, 2004

Is there a System stored procedure that gives me the Role in which a user is in. For example I execute this procedure, give the user as parameter an that gives me back the Role the user is in. It has to be said that this is a user defined role, I got three of them, HR, Employee, Approver.

Greetings,
Godofredo

View 2 Replies View Related

Checking The User's Server Role

Jul 23, 2005

I would like to determine if a particular user has sysadmin serverrole. Is there a way to do this via the connection string? Currentlyour code checks if a login is valid using SQLDriverConnect, however weneed to be certain that the user can login and modify the schema.Is it possible to fetch a user's server role to determine if it has asysadmin server role?

View 2 Replies View Related

Backup User - Server Role?

Aug 29, 2005

I wish to create a user that can backup any or all databases in our SQLServer 2000 Instance. I thought there would be a server role for thisfunction, however I can only find that after I grant access of adatabase to the user, then I can choose ds_backupoperator.I want to create a user that will have the ability to backup all thedatabases. I dont wish to have to come back to the server after a newtable is created and add the backup user to that table.I want SA w/o the full privilage...am I crazy?Any Suggestions?TIARobBackgroup: We currently have about 10 SQL servers, and adding more inthe future. I am using SQLBackup from Idera along with HP SurestoreTape library (60 slots,2- DLT8000 drives with 40/80 GB capacity) withArcServe from Computer Associates. I want to have this automated tobackup to file then tape, regardless of what databases get created.

View 1 Replies View Related

Selecting Only Databases In Which User Has A Role

May 31, 2006

I have a user in SQL Server 2000 with public, datareader and datawriter roles on several databases. I need to select all those databases, how can I do that. I have tried sp_databases but I get ALL databases. I also tried sp_MShasdbaccess but I still get all databases.

View 4 Replies View Related

Drop Role, User, Login

Jul 14, 2006

Okay I figured out how to determine if stored procs and funcs exist before dropping them.

How do I do the same for ROLE, LOGIN, USER?

I want get rid of annoying messages in my scripts when trying to drop something that doesn't exist.

Server 2005 and Server Express 2005

Thanks



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







Copyrights 2005-15 www.BigResource.com, All rights reserved